To: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
cc: "Jori Jovanovich" <jori@dimensiology.com>,
 pgsql-performance@postgresql.org
Subject: Re: SELECT ignoring index even though ORDER BY and LIMIT present 
In-reply-to: <4C0694060200002500031DEA@gw.wicourts.gov> 
References: <AANLkTik6RIYAtAISj1UxmzBC79l-juOuBGYSDzSkV7xG@mail.gmail.com>
	<4C0694060200002500031DEA@gw.wicourts.gov>
Comments: In-reply-to "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
	message dated "Wed, 02 Jun 2010 17:25:26 -0500"
Date: Wed, 02 Jun 2010 18:41:52 -0400
Message-ID: <13218.1275518512@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"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