Re: Query planner is using wrong index. - Mailing list pgsql-performance

From Ragnar
Subject Re: Query planner is using wrong index.
Date
Msg-id 1144324196.32289.91.camel@localhost.localdomain
Whole thread Raw
In response to Re: Query planner is using wrong index.  (Brian Herlihy <btherl@yahoo.com.au>)
Responses Re: Query planner is using wrong index.  (Brian Herlihy <btherl@yahoo.com.au>)
List pgsql-performance
On fim, 2006-04-06 at 19:27 +1000, Brian Herlihy wrote:
> --- Ragnar <gnari@hive.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



pgsql-performance by date:

Previous
From: "Juan Casero \(FL FLC\)"
Date:
Subject: Re: Sun Fire T2000 and PostgreSQL 8.1.3
Next
From: Bruce Momjian
Date:
Subject: Re: Intel C/C++ Compiler Tests (fwd)