From: Brian Herlihy <btherl@yahoo.com.au>
To: pgsql-performance@postgresql.org
Subject: Query planner is using wrong index.
Date: Thu, 6 Apr 2006 12:35:44 +1000 (EST)
Message-ID: <20060406023544.37250.qmail@web52305.mail.yahoo.com> (raw)
Hi,
I have a problem with the choice of index made by the query planner.
My table looks like this:
CREATE TABLE t
(
p1 varchar not null,
p2 varchar not null,
p3 varchar not null,
i1 integer,
i2 integer,
i3 integer,
i4 integer,
i5 integer,
d1 date,
d2 date,
d3 date,
PRIMARY KEY (p1, p2, p3)
);
I have also created an index on (p2, p3), as some of my lookups are on these
only.
All the integers and dates are data values.
The table has around 9 million rows.
I am using postgresl 7.4.7
I have set statistics to 1000 on the p1, p2 and p3 columns, and run vacuum full
analyse. However, I still see
query plans like this:
db=# explain select * from t where p1 = 'something' and p2 = 'fairly_common'
and p3 = 'fairly_common';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Index Scan using p2p3 on t (cost=0.00..6.01 rows=1 width=102)
Index Cond: (((p2)::text = 'fairly_common'::text) AND ((p3)::text =
'fairly_common'::text))
Filter: ((p1)::text = 'something'::text)
(3 rows)
The problem appears to be this:
db=# explain select * from t where p2 = 'fairly_common' and p3 =
'fairly_common';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Index Scan using p2p3 on t (cost=0.00..6.01 rows=1 width=102)
Index Cond: (((p2)::text = 'fairly_common'::text) AND ((p3)::text =
'fairly_common'::text))
(3 rows)
The query planner thinks that this will return only 1 row.
In fact, these index lookups sometimes return up to 500 rows, which then must
be filtered by p1.
This can take 2 or 3 seconds to execute for what should be a simple primary key
lookup.
For VERY common values of p2 and p3, the query planner chooses the primary key,
because these values are stored
explicitly in the analyse results. For rare values there is no problem,
because the query runs quickly.
But for "fairly common" values, there is a problem.
I would like the query planner to use the primary key for all of these lookups.
How can I enforce this?
Thanks,
Brian
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: btherl@yahoo.com.au
Subject: Re: Query planner is using wrong index.
In-Reply-To: <20060406023544.37250.qmail@web52305.mail.yahoo.com>
* 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