Re: How to force PostgreSQL using an index

> > > > 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?
>

Yes, that is a solution!  I will try that! :-)

> 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.
>

Yes, indeed, such a feature could be badly used.  However it may happen
sometimes that the planner is wrong; I already encountered such
situations with both Oracle 9i and SQL Server 2000, even with statistics
calculated.  That is rare but that happens.  Such options /*+ <HINT> */
or WITH(INDEX(...)) help in such situations, even if that really sucks
for the reason you know.


Daniel