From: Dave Dutcher <dave@tridecap.com>
To: 'Luiz K. Matsumura' <luiz@planit.com.br>
To: pgsql-performance@postgresql.org
Subject: Re: Big diference in response time (query plan question)
Date: Wed, 16 Aug 2006 08:34:59 -0500
Message-ID: <00a501c6c138$c55725b0$8300a8c0@tridecap.com> (raw)
In-Reply-To: <44E26939.30903@planit.com.br>
> From: pgsql-performance-owner@postgresql.org
> [mailto:pgsql-performance-owner@postgresql.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
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: dave@tridecap.com, luiz@planit.com.br
Subject: Re: Big diference in response time (query plan question)
In-Reply-To: <00a501c6c138$c55725b0$8300a8c0@tridecap.com>
* 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