From: Ioana Danes <ioanasoftware@yahoo.ca>
To: pgsql-performance@postgresql.org
Subject: Query plan issue when upgrading to postgres 8.14 (from postgres 8.12 or 7.4)
Date: Thu, 6 Jul 2006 16:06:52 -0400 (EDT)
Message-ID: <20060706200652.50294.qmail@web55911.mail.re3.yahoo.com> (raw)
I have a problem with a query that in postgres 7.4 and 8.12 has an acceptable response time but in postgres 8.14 is very slow.
This is the table I use:
create table TEST (
TESTID INT8 not null,
TESTTYPE INT4 null,
constraint PK_TESTID primary key (TESTID));
create index IX_TEST_TESTTYPE on TEST (TESTTYPE);
And this is the query with the problem:
explain select max(TESTID) from TEST where TESTTYPE = 1577;
The query plan in postgres 7.4 and 8.12 is using the index by TESTTYPE field, which is what I want in this case.
QUERY PLAN
Aggregate (cost=25.97..25.97 rows=1 width=8)
-> Index Scan using ix_test_testtype on test (cost=0.00..25.95 rows=9 width=8)
Index Cond: (testtype = 1577)
With postgres 8.14 the query plan uses the primary key PK_TESTID with filter by TESTTYPE, which it takes almost 10 minutes to execute:
QUERY PLAN
Limit (cost=0.00..41.46 rows=1 width=8)
-> Index Scan Backward using pk_testid on test (cost=…)
Filter: ((testid IS NOT NULL) and (testtype = 1577))
When replacing the index
create index IX_TEST_TESTTYPE on TEST (TESTTYPE);
with
create index IX_TEST_TESTTYPE on TEST (TESTTYPE, TESTID);
the query plan uses this index and the execution of this select is extremely fast.
From what I can see, the query plan for 8.14 is using a index scan by the field used with max() function with a filter by the field in where condition.
Should not the query plan use an index scan by the field in where condition (which in my case is a small range) and come up with the max value in that range?
Is this a bug, am I missing a configuration step or this is how it is supposed to work?
Thank you very much,
Ioana
---------------------------------
Make free worldwide PC-to-PC calls. Try the new Yahoo! Canada Messenger with Voice
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: ioanasoftware@yahoo.ca
Subject: Re: Query plan issue when upgrading to postgres 8.14 (from postgres 8.12 or 7.4)
In-Reply-To: <20060706200652.50294.qmail@web55911.mail.re3.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