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

"Owen Jacobson" <ojacobson(at)osl(dot)com> writes:
> On Wed, Feb 15, 2006 at 04:58:54PM -0500, Daniel Caune wrote:
>> 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.

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

BTW, the cute way to do that is

BEGIN;
DROP INDEX unwanted;
EXPLAIN ANALYZE whatever...;
ROLLBACK;

No need to actually rebuild the index when you are done.

This does hold an exclusive lock on the table for the duration of your
experiment, so maybe not such a good idea in a live environment ... but
then again, dropping useful indexes in a live environment isn't a good
idea either, and this at least reduces the duration of the experiment by
a good deal.

regards, tom lane