From: Tom Lane <tgl@sss.pgh.pa.us>
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: Jori Jovanovich <jori@dimensiology.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: SELECT ignoring index even though ORDER BY and LIMIT present
Date: Wed, 02 Jun 2010 18:41:52 -0400
Message-ID: <13218.1275518512@sss.pgh.pa.us> (raw)
In-Reply-To: <4C0694060200002500031DEA@gw.wicourts.gov>
References: <AANLkTik6RIYAtAISj1UxmzBC79l-juOuBGYSDzSkV7xG@mail.gmail.com>
<4C0694060200002500031DEA@gw.wicourts.gov>
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Jori Jovanovich <jori@dimensiology.com> wrote:
>> what is the recommended way to solve this?
> The recommended way is to adjust your costing configuration to
> better reflect your environment.
Actually, it's probably not the costs so much as the row estimates.
For instance, that first query was estimated to select 20 out of a
possible 24 rows. If 24 is indeed the right number of matches, then
the planner is right and the OP is wrong: the indexscan is going to
have to traverse almost all of the table and therefore it will be a
lot slower than seqscan + sort. Now, if the real number of matches
is a lot more than that, then the indexscan would make sense because it
could be expected to get stopped by the LIMIT before it has to traverse
too much of the table. So the true problem is to get the rowcount
estimate to line up with reality.
Unfortunately the estimates for ~* are typically not very good.
If you could convert that to plain ~ (case sensitive) it'd probably
work better. Also, if this isn't a particularly modern version of
Postgres, a newer version might do a bit better with the estimate.
regards, tom lane
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: tgl@sss.pgh.pa.us, Kevin.Grittner@wicourts.gov, jori@dimensiology.com
Subject: Re: SELECT ignoring index even though ORDER BY and LIMIT present
In-Reply-To: <13218.1275518512@sss.pgh.pa.us>
* 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