From: Ragnar <gnari@hive.is>
To: Brian Herlihy <btherl@yahoo.com.au>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query planner is using wrong index.
Date: Thu, 06 Apr 2006 11:49:56 +0000
Message-ID: <1144324196.32289.91.camel@localhost.localdomain> (raw)
In-Reply-To: <20060406092711.8725.qmail@web52315.mail.yahoo.com>
References: <20060406092711.8725.qmail@web52315.mail.yahoo.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: gnari@hive.is, btherl@yahoo.com.au
Subject: Re: Query planner is using wrong index.
In-Reply-To: <1144324196.32289.91.camel@localhost.localdomain>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox