Re: Unexpected query plan results

> 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