Re: How to force PostgreSQL using an index

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