Re: (PATCH) Adding CORRESPONDING to Set Operations

On 14 November 2011 11:29, Kerem Kat <keremkat(at)gmail(dot)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