From: | Tom Raney <raneyt(at)cecs(dot)pdx(dot)edu> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Postgres Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Planner question |
Date: | 2008-09-10 18:21:54 |
Message-ID: | 48C81042.3080805@cecs.pdx.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Tom Lane wrote:
> Tom Raney <raneyt(at)cecs(dot)pdx(dot)edu> writes:
>> Why does the planner consider both input variations of each symmetric merge join? The README says "there is not a lot of difference" between the two options. When are there any differences?
>
> The righthand side needs to support mark/restore, the left doesn't;
> so depending on plan types one way might need a helper Materialize
> node that the other way doesn't. Also, duplicated values are a bit
> cheaper to process on the left than the right.
>
> regards, tom lane
>
Thank you for the explanation.
On a somewhat related issue, I am a bit stumped on the way path keys
function.
In the following query and debug data, why would an index scan on a
single relation contain a path key from a different relation?
optimizer/README says, "The PathKeys data structure represents what is
known about the sort order of the tuples generated by a particular
Path. A path's pathkeys field is a list of PathKey nodes, where the
n'th item represents the n'th sort key of the result."
Why does the index scan for tenk1 include a path key from
onek.unique2? Is it implying an equivalence there?
-Tom Raney
bench=# explain select * from tenk1 JOIN onek ON
tenk1.unique2=onek.unique2;
RELOPTINFO (tenk1): rows=10000 width=244
path list:
SeqScan(tenk1) rows=10000 cost=0.00..434.00
IdxScan(tenk1) rows=10000 cost=0.00..583.25
pathkeys: ((tenk1.unique2, onek.unique2)) <---
cheapest startup path:
SeqScan(tenk1) rows=10000 cost=0.00..434.00
cheapest total path:
SeqScan(tenk1) rows=10000 cost=0.00..434.00
RELOPTINFO (onek): rows=1000 width=244
path list:
SeqScan(onek) rows=1000 cost=0.00..44.00
IdxScan(onek) rows=1000 cost=0.00..72.25
pathkeys: ((tenk1.unique2, onek.unique2))
cheapest startup path:
SeqScan(onek) rows=1000 cost=0.00..44.00
cheapest total path:
SeqScan(onek) rows=1000 cost=0.00..44.00
RELOPTINFO (tenk1/onek): rows=1000 width=488
path list:
MergeJoin(tenk1/onek) rows=1000 cost=0.52..144.24
clauses: tenk1.unique2 = onek.unique2
IdxScan(tenk1) rows=10000 cost=0.00..583.25
pathkeys: ((tenk1.unique2, onek.unique2))
IdxScan(onek) rows=1000 cost=0.00..72.25
pathkeys: ((tenk1.unique2, onek.unique2))
NestLoop(tenk1/onek) rows=1000 cost=0.00..1756.96
clauses: tenk1.unique2 = onek.unique2
SeqScan(onek) rows=1000 cost=0.00..44.00
IdxScan(tenk1) rows=10000 cost=0.00..1.70
cheapest startup path:
NestLoop(tenk1/onek) rows=1000 cost=0.00..1756.96
clauses: tenk1.unique2 = onek.unique2
SeqScan(onek) rows=1000 cost=0.00..44.00
IdxScan(tenk1) rows=10000 cost=0.00..1.70
cheapest total path:
MergeJoin(tenk1/onek) rows=1000 cost=0.52..144.24
clauses: tenk1.unique2 = onek.unique2
IdxScan(tenk1) rows=10000 cost=0.00..583.25
pathkeys: ((tenk1.unique2, onek.unique2))
IdxScan(onek) rows=1000 cost=0.00..72.25
pathkeys: ((tenk1.unique2, onek.unique2))
QUERY PLAN
-----------------------------------------------------------------------------------------
Merge Join (cost=0.52..144.24 rows=1000 width=488)
Merge Cond: (tenk1.unique2 = onek.unique2)
-> Index Scan using tenk1_unique2 on tenk1 (cost=0.00..583.25
rows=10000 width=244)
-> Index Scan using onek_unique2 on onek (cost=0.00..72.25 rows=1000
width=244)
(4 rows)
bench=#
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Haas | 2008-09-10 18:27:57 | Re: Common Table Expressions (WITH RECURSIVE) patch |
Previous Message | Simon Riggs | 2008-09-10 18:17:37 | Re: Synchronous Log Shipping Replication |