From: Owen Jacobson <ojacobson@osl.com>
To: pgsql-sql@postgresql.org
Subject: Re: How to force PostgreSQL using an index
Date: Wed, 15 Feb 2006 14:32:48 -0800
Message-ID: <144D12D7DD4EC04F99241498BB4EEDCC220865@nelson.osl.com> (raw)
Daniel Caune wrote:
>
> 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?
> >
> > Your best bet is to do
> >
> > set enable_indexscan=false;
> >
> > and then do the EXPLAIN ANALYSE for your select.
>
> 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
> composite index on
> EVENT_DATE_CREATED and EVENT_NAME (in that order as EVENT_DATE_CREATED
> is more dense that EVENT_NAME).
>
> 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
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-sql@postgresql.org
Cc: ojacobson@osl.com
Subject: Re: How to force PostgreSQL using an index
In-Reply-To: <144D12D7DD4EC04F99241498BB4EEDCC220865@nelson.osl.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