Content-Type: text/plain;
  charset="iso-8859-1"
From: Richard Huxton <dev@archonet.com>
Organization: Archonet Ltd
To: Siva Kumar <tech@leatherlink.net>, pgsql-general@postgresql.org
Subject: Re: Query optimization
Date: Fri, 4 Oct 2002 14:29:51 +0100
User-Agent: KMail/1.4.3
References: <200210041347.55055.tech@leatherlink.net>
	<200210041114.17241.dev@archonet.com>
	<200210041756.35489.tech@leatherlink.net>
In-Reply-To: <200210041756.35489.tech@leatherlink.net>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200210041429.51956.dev@archonet.com>

On Friday 04 Oct 2002 1:26 pm, Siva Kumar wrote:
> Giving below the output of EXPLAIN ANALYSE. I could not make much sense o=
ut
> of it, please help!

Scary aren't they ;-)

The important thing is you've got plenty of "Index Scan"s rather than Seq=
=20
Scans. The other point is the final time:

> Total runtime: 5.45 msec

Now, since that's not what you're getting, I'd think Neil (see other reply)=
 is=20
right and you need to look at issuing "SET geqo =3D off;" before the query.

Postgresql has a genetic algorithm that kicks in on what it thinks is a ver=
y=20
complex query, this can take a long time to analyse the options available b=
ut=20
pays dividends on a big query. In your case you're only getting a few rows=
=20
and so it takes longer to analyse than to get the results.

The other thing that might work is rewriting the query with explicit JOINs =
- I=20
think that should make it clear to Postgresql what order to do things in.

- Richard Huxton