Re: Big diference in response time (query plan question)

> From: pgsql-performance-owner(at)postgresql(dot)org
> [mailto:pgsql-performance-owner(at)postgresql(dot)org] On Behalf Of
> Luiz K. Matsumura

> Well, in this case the queries with LEFT OUTER join and with
> inner join
> returns the same result set. I don´t have the sufficient knowledge to
> affirm , but I suspect that if the query plan used for
> fk_clifor = 352
> and with left outer join is applied for the first query
> (fk_clifor = 243
> with left outer join)
> we will have a better total runtime.
> There are some manner to make this test ?

It looks like Postgres used a nested loop join for the fast query and a
merge join for the slow query. I don't think the left join is causing any
problems. On the slower query the cost estimate of the nested loop must
have been higher than the cost estimate of the merge join because of more
rows. You could try disabling merge joins with the command "set
enable_mergejoin=false". Then run the explain analyze again to see if it is
faster.

If it is faster without merge join, then you could try to change your
settings to make the planner prefer the nested loop. I'm not sure what the
best way to do that is. Maybe you could try reducing the random_page_cost,
which should make index scans cheaper.

Dave