From: "Dave Dutcher" <dave@tridecap.com>
To: "'Luiz K. Matsumura'" <luiz@planit.com.br>,
	<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>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Importance: Normal
In-Reply-To: <44E26939.30903@planit.com.br>


> From: pgsql-performance-owner@postgresql.org=20
> [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of=20
> Luiz K. Matsumura

> Well, in this case the queries with LEFT OUTER join and with=20
> inner join=20
> returns the same result set. I don=B4t have the sufficient knowledge =
to
> affirm , but I suspect that if the query plan used for=20
> fk_clifor =3D 352=20
> and with left outer join is applied for the first query=20
> (fk_clifor =3D 243=20
> 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=3Dfalse".  Then run the explain analyze again to see if =
it is
faster. =20

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