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