From: | Kerem Kat <keremkat(at)gmail(dot)com> |
---|---|
To: | Thom Brown <thom(at)linux(dot)com> |
Cc: | Erik Rijkers <er(at)xs4all(dot)nl>, Robert Haas <robertmhaas(at)gmail(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: (PATCH) Adding CORRESPONDING to Set Operations |
Date: | 2011-11-14 11:29:05 |
Message-ID: | CAJZSWkXZeCncG-kd4BG12ArzrQze-vvnjRxOt2wqXjicLsqSpg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Shigeru Hanada | 2011-11-14 12:53:55 | Re: FDW system columns |
Previous Message | Thom Brown | 2011-11-14 10:56:44 | Re: (PATCH) Adding CORRESPONDING to Set Operations |