From: Tom Lane <tgl@sss.pgh.pa.us>
To: Ed Tyrrill <tyrrill_ed@emc.com>
Cc: pgsql-performance@postgresql.org
Subject: Re:
Date: Mon, 25 Jun 2007 21:07:14 -0400
Message-ID: <8930.1182820034@sss.pgh.pa.us> (raw)
In-Reply-To: <1182816598.6477.31.camel@nickel.avamar.com>
References: <A23190A408F7094FAF446C1538222F76041E1C28@avaexch01.avamar.com>
<7008.1182809415@sss.pgh.pa.us>
<1182816598.6477.31.camel@nickel.avamar.com>
Ed Tyrrill <tyrrill_ed@emc.com> writes:
> It seems to me that the first plan is the optimal one for this case, but
> when the planner has more information about the table it chooses not to
> use it. Do you think that if work_mem were higher it might choose the
> first plan again?
It's worth fooling around with work_mem just to see what happens. The
other thing that would be interesting is to force the other plan (set
enable_mergejoin = off) just to see what the planner is costing it at.
My suspicion is that the estimated costs are pretty close.
The ANALYZE stats affect this choice only in second-order ways AFAIR.
The planner penalizes hashes if it thinks there will be a lot of
duplicate values in the inner relation, but IIRC there is also a penalty
for inner duplicates in the mergejoin cost estimate. So I'm a bit
surprised that there'd be a change.
Can you show us the pg_stats rows for the join columns after analyzing
at target 10 and target 100?
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, tyrrill_ed@emc.com
Subject: Re:
In-Reply-To: <8930.1182820034@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox