Re: Unexpected query plan results

Dave Dutcher wrote:

>>From: Anne Rosset
>>Subject: [PERFORM] Unexpected query plan results
>>
>>Hi,
>>We have one query which has a left join. If we run this query without
>>the left join, it runs slower than with the left join.
>>
>>
>[snip]
>
>
>>I am having a hard time to understand why the query runs
>>faster with the
>>left join.
>>
>>
>>
>
>It looks like the query plan for the query without the left join is less
>than optimal.  Adding the left join just seemed to shake things up enough
>that postgres picked a better plan.  The slow step in the query without the
>left join appears to be sorting the result of a hash join so it can be used
>in a merge join.
>
> ->  Sort  (cost=47640.91..47808.10 rows=66876 width=70) (actual
>time=4273.919..4401.387 rows=168715 loops=1)
>       Sort Key: (artifact.id)::text
>            ->  Hash Join  (cost=9271.96..42281.07 rows=66876 width=70)
>(actual time=124.119..794.667 rows=184378 loops=1)
>
>The plan might be sped up by removing the sort or making the sort faster.
>Postgres thinks the Hash Join will only produce 66,876 rows, but it produces
>184,378 rows.  If it made a better estimate of the results of the hash join,
>it might not choose this plan.  I don't really know if there is a way to
>improve the estimate on a join when the estimates of the inputs look pretty
>good.
>
>As a test you might try disabling sorts by setting enable_sort to false,
>then run the explain analyze again to see what you get.
>
>You might be able to make the sort faster by increasing work mem.  What do
>you have work mem set to now and what version of Postgres are you using?
>
>
>Dave
>
>
>
Thank Dave. We are using postgresql-server-8.2.4-1PGDG and have work-mem
set to 20MB.
What value would you advise?
thanks,

Anne