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

Поиск
Список
Период
Сортировка
От Ioana Danes
Тема Query plan issue when upgrading to postgres 8.14 (from postgres 8.12 or 7.4)
Дата
Msg-id 20060706213446.99717.qmail@web55908.mail.re3.yahoo.com
обсуждение исходный текст
Список 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 по дате отправления:

Предыдущее
От: Michael Loftis
Дата:
Сообщение: Re: suggested RAID controller for FreeBSD 6.1 +PostgreSQL
Следующее
От: Gene
Дата:
Сообщение: Update INSERT RULE while running for Partitioning