[no subject]

---------------------------------
 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

<div>I have a problem with a query that in postgres 7.4&nbsp; and 8.12 has an acceptable response time but in postgres 8.14 is very slow.<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <BR>This is the table I use:<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; create table&nbsp; TEST (<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; TESTID&nbsp;&nbsp;&nbsp; INT8 not null,<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; TESTTYPE&nbsp; INT4&nbsp;&nbsp;&nbsp;&nbsp; null,<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; constraint&nbsp; PK_TESTID primary key (TESTID));<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; create index&nbsp; IX_TEST_TESTTYPE on TEST (TESTTYPE);<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <BR>And this is the query with the problem:</div>  <div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; explain select max(TESTID) from TEST where TESTTYPE = 1577;<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <BR>The query plan in postgres 7.4 and 8.12 is using the&nbsp; index by TESTTYPE field, which is what I want in this
 case.<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; QUERY PLAN&nbsp; <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Aggregate&nbsp;&nbsp; (cost=25.97..25.97 rows=1 width=8)&nbsp;&nbsp;&nbsp; <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Index Scan using ix_test_testtype on&nbsp; test&nbsp; (cost=0.00..25.95 rows=9 width=8)&nbsp;&nbsp;&nbsp; <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Index Cond:&nbsp; (testtype = 1577)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </div>  <div>With postgres 8.14 the query plan uses the primary&nbsp; key PK_TESTID with filter by TESTTYPE, which&nbsp; it takes almost 10 minutes to execute:<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; QUERY PLAN&nbsp; <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Limit&nbsp; (cost=0.00..41.46&nbsp; rows=1 width=8)&nbsp;&nbsp;&nbsp; <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Index Scan Backward using pk_testid on&nbsp; test&nbsp; (cost=?)&nbsp;&nbsp;&nbsp;
 <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Filter: ((testid IS&nbsp; NOT NULL) and (testtype = 1577))<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <BR>When replacing the index <BR>&nbsp;&nbsp;&nbsp;&nbsp; create index IX_TEST_TESTTYPE on TEST (TESTTYPE);<BR>with <BR>&nbsp;&nbsp;&nbsp;&nbsp; create index IX_TEST_TESTTYPE on TEST (TESTTYPE, TESTID);<BR>the query plan uses this index and the execution of this select is&nbsp; extremely fast. <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <BR>From what I can see, the query plan for 8.14 is using a index scan&nbsp; by the field used with max() function with a filter by the field in the where&nbsp; condition.<BR>Should not the query plan use an index scan by the field in where&nbsp; condition (which in my case is a small range) and come up with the max value in that range?<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <BR>Is this a bug, am I missing a configuration step or this is how it&nbsp; is supposed
 to work? <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <BR>Thank you very much,<BR>Ioana <BR></div><p>&#32;
		<hr size=1> <a href="http://us.rd.yahoo.com/evt=40705/*http://mrd.mail.yahoo.com/try_beta?.intl=ca";><b>All new Yahoo! Mail - </b></a>
<hr size=1>Get a sneak peak at messages with a handy reading pane.
--0-878230187-1152221686=:95484--