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 55F499F97C2 for ; Thu, 6 Jul 2006 22:38:08 -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 45075-02 for ; Thu, 6 Jul 2006 22:38:04 -0300 (ADT) X-Greylist: delayed 01:48:18.017311 by SQLgrey- Received: from web55911.mail.re3.yahoo.com (web55911.mail.re3.yahoo.com [216.252.110.72]) by postgresql.org (Postfix) with SMTP id 0F6A59F94F3 for ; Thu, 6 Jul 2006 22:38:03 -0300 (ADT) Received: (qmail 50297 invoked by uid 60001); 6 Jul 2006 20:06:52 -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=KCxhyhzAOisyfpLVXHGsdp8aFPiCG5Kdhz2IE/HZM5viNK8KWCAPfR6J40hvodjDIvsmpK9rXgG3Sn7OjYDD3FciRIKvtL15KvJBU5wn26rtAg/E5zsTTeQM/NAczToCi+0WXtnw6IGVfHDJhPeL5knTflOkU3EuFtvyLNr7oRM= ; Message-ID: <20060706200652.50294.qmail@web55911.mail.re3.yahoo.com> Received: from [207.164.182.8] by web55911.mail.re3.yahoo.com via HTTP; Thu, 06 Jul 2006 16:06:52 EDT Date: Thu, 6 Jul 2006 16:06:52 -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-1703118205-1152216412=:42800" Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200607/55 X-Sequence-Number: 19915 --0-1703118205-1152216412=:42800 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 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 --0-1703118205-1152216412=:42800 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 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 --0-1703118205-1152216412=:42800--