Skip site navigation (1) Skip section navigation (2)

Re:

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Ed Tyrrill <tyrrill_ed(at)emc(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re:
Date: 2007-06-26 01:07:14
Message-ID: 8930.1182820034@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
Ed Tyrrill <tyrrill_ed(at)emc(dot)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

In response to

  • Re: at 2007-06-26 00:09:58 from Ed Tyrrill

Responses

  • Re: at 2007-06-29 21:01:03 from Ed Tyrrill

pgsql-performance by date

Next:From: Gregory StarkDate: 2007-06-26 08:01:59
Subject: Re: Volunteer to build a configuration tool
Previous:From: Stephen FrostDate: 2007-06-26 00:33:39
Subject: Re:

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group