X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 274779FA6BB for ; Fri, 7 Jul 2006 02:31:33 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 93464-05 for ; Fri, 7 Jul 2006 02:31:24 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from web55908.mail.re3.yahoo.com (web55908.mail.re3.yahoo.com [216.252.110.69]) by postgresql.org (Postfix) with SMTP id 56BDD9FA6A2 for ; Fri, 7 Jul 2006 02:31:24 -0300 (ADT) Received: (qmail 99719 invoked by uid 60001); 6 Jul 2006 21:34:46 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.ca; h=Message-ID:Received:Date:From:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=XBGT534s5Dx/9TiCPK4rjoEpuzHIhCOIXzgh0FhYjUBoIheaMakQddGvanf4FjPnwQv0tDWKSO5RywdaBfnz3DQMjYDZZsR4rYxsttY0VLsaqKbc/ePj3q+L8iNdChj6PiamWw48lrmN5GoN8dDwI0+tLrzXQs95ADgQylbkmz0= ; Message-ID: <20060706213446.99717.qmail@web55908.mail.re3.yahoo.com> Received: from [70.29.94.218] by web55908.mail.re3.yahoo.com via HTTP; Thu, 06 Jul 2006 17:34:46 EDT Date: Thu, 6 Jul 2006 17:34:46 -0400 (EDT) From: Ioana Danes Subject: Query plan issue when upgrading to postgres 8.14 (from postgres 8.12 or 7.4) To: pgsql-performance@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-878230187-1152221686=:95484" Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200607/59 X-Sequence-Number: 19919 --0-878230187-1152221686=:95484 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit 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. --0-878230187-1152221686=:95484 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit

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. --0-878230187-1152221686=:95484--