Re: How to force PostgreSQL using an index

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