Skip site navigation (1) Skip section navigation (2)

Query plan issue when upgrading to postgres 8.14 (from postgres 8.12 or 7.4)

From: Ioana Danes <ioanasoftware(at)yahoo(dot)ca>
To: pgsql-performance(at)postgresql(dot)org
Subject: Query plan issue when upgrading to postgres 8.14 (from postgres 8.12 or 7.4)
Date: 2006-07-06 21:34:46
Message-ID: 20060706213446.99717.qmail@web55908.mail.re3.yahoo.com (view raw or flat)
Thread:
Lists: 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

Next:From: Mark KirkwoodDate: 2006-07-06 22:51:54
Subject: Re: suggested RAID controller for FreeBSD 6.1 + PostgreSQL
Previous:From: Ioana DanesDate: 2006-07-06 20:06:52
Subject: Query plan issue when upgrading to postgres 8.14 (from postgres 8.12 or 7.4)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group