Re: How to force PostgreSQL using an index

> On Wed, Feb 15, 2006 at 04:58:54PM -0500, Daniel Caune wrote:
> > Hi,
> >
> >
> >
> > Is there a way to force PostgreSQL using an index for a SELECT
> > statement?  I just want to confirm that the index PostgreSQL decides
to
> > use is better than the index I supposed PostgreSQL would use (I
already
> > analyze the table).
>
> Your best bet is to do
>
> set enable_indexscan=false;
>
> and then do the EXPLAIN ANALYSE for your select.
>
> You might also find that fiddling with other settings affects the
> planner's idea of what would be a good plan.  The planner is
> sensitive to what it thinks it knows about your environment.
>

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.

For example, I have a table that contains historical data from which I
try to get a subset for a specified period of time:

SELECT <some-columns> FROM GSLOG_EVENT WHERE EVENT_NAME = 'player-status-update'   AND EVENT_DATE_CREATED >=
<start-time>  AND EVENT_DATE_CREATED < <end-time> 

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


Daniel