From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: Mark Williams <mark.williams@jivesoftware.com>
To: pgsql-performance@postgresql.org
Subject: Re: Bad Query Plan with Range Query
Date: Fri, 15 Apr 2011 12:54:18 -0500
Message-ID: <4DA83FFA020000250003C89D@gw.wicourts.gov> (raw)
In-Reply-To: <4DA87DAC.30006@jivesoftware.com>
References: <4DA87DAC.30006@jivesoftware.com>
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: Kevin.Grittner@wicourts.gov, mark.williams@jivesoftware.com
Subject: Re: Bad Query Plan with Range Query
In-Reply-To: <4DA83FFA020000250003C89D@gw.wicourts.gov>
* 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