> 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers