Message-ID: <4DA8893B.8050403@jivesoftware.com>
Date: Fri, 15 Apr 2011 11:06:51 -0700
From: Mark Williams <mark.williams@jivesoftware.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.2.14) Gecko/20110223 Thunderbird/3.1.8
MIME-Version: 1.0
CC: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Bad Query Plan with Range Query
References: <4DA87DAC.30006@jivesoftware.com>
	<4DA83FFA020000250003C89D@gw.wicourts.gov>
In-Reply-To: <4DA83FFA020000250003C89D@gw.wicourts.gov>
Content-Type: text/plain; charset="ISO-8859-1"; format=flowed
Content-Transfer-Encoding: 7bit

Thanks for the response guys. There is something else which confuses me. 
If I re-write the query like this:

explain SELECT messageID FROM Message WHERE modificationDate >= 
1302627793988 ORDER BY modificationDate LIMIT 1;
                                            QUERY PLAN
-------------------------------------------------------------------------------------------------
  Limit  (cost=0.00..2.97 rows=1 width=16)
    ->  Index Scan using jvmssg_mdate_idx on message  
(cost=0.00..3705.59 rows=1249 width=16)
          Index Cond: (modificationdate >= 1302627793988::bigint)
(3 rows)

I also get a better plan. However, this is not always the case. On some 
other instances we still get a sequential scan on the primary key.


On 04/15/2011 10:54 AM, Kevin Grittner wrote:
> Mark Williams<mark.williams@jivesoftware.com>  wrote:
>
>> explain SELECT min(messageID) FROM Message
>>    WHERE modificationDate>= 1302627793988;
>
>> For some reason it is deciding to scan the primary key column of
>> the table. This results in scanning the entire table
>
> No, it scans until it finds the first row where modificationDate>=
> 1302627793988, at which point the scan is done because it's doing an
> ascending scan on what you want the min() of.  You might have a clue
> that the first such row will be ten million rows into the scan, but
> the optimizer doesn't know that.  It's assuming that rows which meet
> that condition are scattered randomly through the primary key range.
> It thinks that it will, on average, need to scan 1249 rows to find a
> match.
>
> The patch Ken referenced causes the alternative to be assigned a
> more accurate (and lower) cost, which tips the scales in favor of
> that plan -- at least for the case you've tried; but this seems to
> me to be another case related to the correlation of values.  It's a
> new and different form of it, but it seems at least somewhat
> related.  It might be a good example for those working on
> multi-column statistics to keep in mind.
>
> -Kevin