Query plan issue when upgrading to postgres 8.14 (from postgres 8.12 or 7.4) - Mailing list pgsql-performance

From Ioana Danes
Subject Query plan issue when upgrading to postgres 8.14 (from postgres 8.12 or 7.4)
Date
Msg-id 20060706213446.99717.qmail@web55908.mail.re3.yahoo.com
Whole thread Raw
List pgsql-performance
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 the 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


All new Yahoo! Mail -
Get a sneak peak at messages with a handy reading pane.

pgsql-performance by date:

Previous
From: Michael Loftis
Date:
Subject: Re: suggested RAID controller for FreeBSD 6.1 +PostgreSQL
Next
From: Gene
Date:
Subject: Update INSERT RULE while running for Partitioning