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
