> 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