From: Mark Williams <mark.williams@jivesoftware.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Bad Query Plan with Range Query
Date: Fri, 15 Apr 2011 11:06:51 -0700
Message-ID: <4DA8893B.8050403@jivesoftware.com> (raw)
In-Reply-To: <4DA83FFA020000250003C89D@gw.wicourts.gov>
References: <4DA87DAC.30006@jivesoftware.com>
<4DA83FFA020000250003C89D@gw.wicourts.gov>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mark.williams@jivesoftware.com
Subject: Re: Bad Query Plan with Range Query
In-Reply-To: <4DA8893B.8050403@jivesoftware.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox