Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Subject: Re: How to force PostgreSQL using an index
Date: Wed, 15 Feb 2006 14:32:48 -0800
Message-ID: <144D12D7DD4EC04F99241498BB4EEDCC220865@nelson.osl.com>
Thread-Topic: [SQL] How to force PostgreSQL using an index
Thread-Index: AcYyfD7btng6QOR9Q/OgL9NhDX6CtQAAQ8PgAAB7XJA=
From: "Owen Jacobson" <ojacobson@osl.com>
To: <pgsql-sql@postgresql.org>

Daniel Caune wrote:=20
>=20
> Andrew Sullivan wrote:
>
> > On Wed, Feb 15, 2006 at 04:58:54PM -0500, Daniel Caune wrote:
> >
> > >
> > > Is there a way to force PostgreSQL using an index for a SELECT
> > > statement?
> >=20
> > Your best bet is to do
> >=20
> > set enable_indexscan=3Dfalse;
> >=20
> > and then do the EXPLAIN ANALYSE for your select.
>=20
> I see, but that doesn't explain whether it is possible to specify the
> index to use.  It seems that those options just force PostgreSQL using
> another plan.

(snip)

> I have an index on EVENT_DATE_CREATED that does it job.  But I though
> that I can help my favourite PostgreSQL if I create a=20
> composite index on
> EVENT_DATE_CREATED and EVENT_NAME (in that order as EVENT_DATE_CREATED
> is more dense that EVENT_NAME).
>=20
> PostgreSQL prefer the simple index rather than the composite index =
(for
> I/O consideration, I suppose).  I wanted to know how bad the composite
> index would be if it was used (the estimate cost).

Drop the simple index and re-create it when you're done?

As I understand it, the problem with letting clients specify which =
indexes to use is that they tend, on the whole, to be wrong about what's =
most efficient, so it's a feature almost specifically designed for =
shooting yourself in the foot with.  I agree that it'd be useful for =
experimenting with indexing schemes, but then, so is DROP INDEX.

-Owen