Re: Query with order by and limit is very slow - wrong index used

=?iso-8859-2?Q?Nowak_Micha=B3?= <michal(dot)nowak(at)me(dot)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