To: =?iso-8859-2?Q?Nowak_Micha=B3?= <michal.nowak@me.com>
cc: pgsql-performance@postgresql.org
Subject: Re: Query with order by and limit is very slow - wrong index used 
In-reply-to: <6B8FADC8-9692-439F-8AA4-0CAA287FCEB0@me.com> 
References: <6B8FADC8-9692-439F-8AA4-0CAA287FCEB0@me.com>
Comments: In-reply-to =?iso-8859-2?Q?Nowak_Micha=B3?= <michal.nowak@me.com>
	message dated "Mon, 03 Oct 2011 11:44:21 +0200"
Date: Mon, 03 Oct 2011 11:12:31 -0400
Message-ID: <15615.1317654751@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

=?iso-8859-2?Q?Nowak_Micha=B3?= <michal.nowak@me.com> writes:
> When I perform query such as this:  "select * from records where source_id = 'XXX' order by id limit 200;" I expect DB to use index source_id_id_idx  with XXX as filter. It is true for all but one values of XXX - when I ask for records with most common source_id, records_pkey index is used instead and performance is terrible! Explain analyze results below.

I'm thinking it probably sees the pkey index as cheaper because that's
highly correlated with the physical order of the table.  (It would be
useful to see pg_stats.correlation for these columns.)  With a
sufficiently unselective filter, scanning in pkey order looks cheaper
than scanning in source_id order.

If so, what you probably need to do to get the estimates more in line
with reality is to reduce random_page_cost.  That will reduce the
assumed penalty for non-physical-order scanning.

			regards, tom lane