On 14 November 2011 11:29, Kerem Kat
<keremkat@gmail.com> wrote:
> 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.
I'm certainly no expert on what the right way to represent the plan is, but I'm still uncomfortable with its current representation. And having just tested the translated equivalent, I still don't get the same explain plan:
test=# explain select a, c from (select a,b,c from one) a
intersect
select a, c from (select a,b,c from two) b;
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* 1" (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* 2" (cost=0.00..48.80 rows=1940 width=8)
-> Seq Scan on two (cost=0.00..29.40 rows=1940 width=8)
(6 rows)
Also you probably want to update src/backend/catalog/sql_features.txt so that F301 is marked as "YES" for supporting the standard. :)
--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935
EnterpriseDB UK:
http://www.enterprisedb.com The Enterprise PostgreSQL Company