Re: Query planner is using wrong index.

On fim, 2006-04-06 at 19:27 +1000, Brian Herlihy wrote:
> --- Ragnar <gnari(at)hive(dot)is> wrote:
>
> > On fim, 2006-04-06 at 12:35 +1000, Brian Herlihy wrote:
> >
...
> > > PRIMARY KEY (p1, p2, p3)
...
> > >
> > > I have also created an index on (p2, p3), as some of my lookups are on
> > > these only.
...
> > > db=# explain select * from t where p2 = 'fairly_common' and p3 =
> > > 'fairly_common';

> > please show us an actual EXPLAIN ANALYZE

> > > I would like the query planner to use the primary key for all of these
> > lookups.
> >
> > have you tested to see if it would actualy be better?
> >

> Yes, the primary key is far better. I gave it the ultimate test - I dropped
> the (p2, p3) index. It's blindingly fast when using the PK,

I have problems understanding exactly how an index on
(p1,p2,p3) can be faster than and index on (p2,p3) for
a query not involving p1.
can you demonstrate this with actual EXPLAIN ANALYZES ?
something like:
EXPLAIN ANALYZE select * from t where p2 = ? and p3 = ?;
BEGIN;
DROP INDEX p2p3;
EXPLAIN ANALYZE select * from t where p2 = ? and p3 = ?;
ROLLBACK;

maybe your p2p3 index needs REINDEX ?

> My options seem to be
> - Fudge the analysis results so that the selectivity estimate changes. I
> have tested reducing n_distinct, but this doesn't seem to help.
> - Combine the columns into one column, allowing postgres to calculate the
> combined selectivity.
> - Drop the (p2, p3) index. But I need this for other queries.
>
> None of these are good solutions. So I am hoping that there is a better way to
> go about this!

I think we must detemine exactly what the problem is
before devising complex solutions

gnari