DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding;
	b=LPp1RmH9/hoE9OPhHPdEbd+9+91UlQaHOah7cIMYQwgIBvjemKKhqG4pon+w1ulpz3FXi/7KhDx7qTdqo4sEvBMAoaENeZHA5zTvCb4P6MTr9THkhe+FIEXlLzmnOiM3IVVLtLnpHnQ+MmhVERiqv96HxRMr2G3KmGGuTapyTAY=
Message-ID: <44ADBE04.9010605@gmail.com>
Date: Fri, 07 Jul 2006 11:51:00 +1000
From: Chris <dmagick@gmail.com>
User-Agent: Thunderbird 1.5.0.4 (Windows/20060516)
MIME-Version: 1.0
To: Ioana Danes <ioanasoftware@yahoo.ca>
CC: pgsql-performance@postgresql.org
Subject: Re: Query plan issue when upgrading to postgres 8.14 (from
References: <20060706200652.50294.qmail@web55911.mail.re3.yahoo.com>
In-Reply-To: <20060706200652.50294.qmail@web55911.mail.re3.yahoo.com>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 8bit

Ioana Danes wrote:
> 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?

You've left out the best details. Post an 'explain analyze' from both 
versions, and don't cut anything out :)

I'm guessing postgres is seeing an index on the table is faster because 
it doesn't think you have many rows in the table. How many are there, 
and have you done an analyze of the table after loading the data in?

-- 
Postgresql & php tutorials
http://www.designmagick.com/