Horton

Re: (PATCH) Adding CORRESPONDING to Set Operations

Kerem Kat <keremkat@gmail.com>

View thread
From: Kerem Kat <keremkat@gmail.com>
To: Thom Brown <thom@linux.com>
Cc: Erik Rijkers <er@xs4all.nl>, Robert Haas <robertmhaas@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>, pgsql-hackers@postgresql.org
Date: 2011-11-14T11:29:05Z
Lists: pgsql-hackers 
> This explain plan doesn't look right to me:
>
> test=# explain select a,b,c from one intersect corresponding by (a,c)
> select a,b,c from two;
>                                   QUERY PLAN
> ---------------------------------------------------------------------------------
>  HashSetOp Intersect  (cost=0.00..117.00 rows=200 width=8)
>   ->  Append  (cost=0.00..97.60 rows=3880 width=8)
>         ->  Subquery Scan on "*SELECT* 3"  (cost=0.00..48.80 rows=1940 width=8)
>               ->  Seq Scan on one  (cost=0.00..29.40 rows=1940 width=8)
>         ->  Subquery Scan on "*SELECT* 4"  (cost=0.00..48.80 rows=1940 width=8)
>               ->  Seq Scan on two  (cost=0.00..29.40 rows=1940 width=8)
> (6 rows)

In the current implementation,

select a,b,c from one intersect corresponding by (a,c) select a,b,c from two;

is translated to equivalent

select a, c from (select a,b,c from one)
intersect
select a, c from (select a,b,c from two);

Methinks that's the reason for this explain output.

Corresponding is currently implemented in the parse/analyze phase. If
it were to be implemented in the planning phase, explain output would
likely be as you expect it to be.


> If I do the same thing without the "corresponding...":
>
> test=# explain select a,b,c from one intersect select a,b,c from two;
>                                    QUERY PLAN
> ----------------------------------------------------------------------------------
>  HashSetOp Intersect  (cost=0.00..126.70 rows=200 width=12)
>   ->  Append  (cost=0.00..97.60 rows=3880 width=12)
>         ->  Subquery Scan on "*SELECT* 1"  (cost=0.00..48.80
> rows=1940 width=12)
>               ->  Seq Scan on one  (cost=0.00..29.40 rows=1940 width=12)
>         ->  Subquery Scan on "*SELECT* 2"  (cost=0.00..48.80
> rows=1940 width=12)
>               ->  Seq Scan on two  (cost=0.00..29.40 rows=1940 width=12)
> (6 rows)
>
> So it looks like it's now seeing the two tables as the 3rd and 4th
> tables, even though there are only 2 tables in total.
>
> --
> Thom Brown
> Twitter: @darkixion
> IRC (freenode): dark_ixion
> Registered Linux user: #516935
>
> EnterpriseDB UK: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>


Regards,

Kerem KAT