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

On Wed, Feb 15, 2006 at 05:26:57PM -0500, Daniel Caune wrote:
> 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).

You could do that by turning up the data the planner spits out, to
see why it picks this index. It doesn't do so automatically, I
think: I _think_ it's cost based (Tom will probably chime in here and
remind me how little I know). My bet is that the second column isn't
adding enough selectivity to help.

One thing that might affect this is to fiddle with the SET STATISTICS
settings on the column(s) in question. You might find that as the
samples get better, your index turns out to be usefully selective,
and it gets chosen.

But to answer your question, no, you can't tell it "use index foo".

A

--
Andrew Sullivan | ajs(at)crankycanuck(dot)ca
This work was visionary and imaginative, and goes to show that visionary
and imaginative work need not end up well.
--Dennis Ritchie

"Daniel Caune" <daniel(dot)caune(at)ubisoft(dot)com> writes:
> 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).

Wrong ... should be EVENT_NAME first. Think about the sort order of the
data to see why --- your query represents a contiguous subset of the
index if EVENT_NAME is first, but not if EVENT_DATE_CREATED is first.

regards, tom lane