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

2011/10/3 Nowak Michał <michal(dot)nowak(at)me(dot)com>:

> Some info about data distrubution:
>
> a9-dev=> select min(id) from records;
>  min
> --------
>  190830
> (1 row)
>
> a9-dev=> select min(id), max(id) from records where source_id='http://ebuw.uw.edu.pl/dlibra/oai-pmh-repository.xml';
>   min   |   max
> ---------+---------
>  1105217 | 3811326
> (1 row)
> a9-dev=> select min(id), max(id) from records where source_id='http://www.wbc.poznan.pl/dlibra/oai-pmh-repository.xml';
>   min   |   max
> ---------+---------
>  1544991 | 3811413
> (1 row)

PG assumes that the "wbc.poznan.pl" rows are all over the range of
ids, which seems not to be the case. There is no sense of cross-column
correlation in the planner currently.

You are going to have to resort to some more or less cute hacks, like
making an index on (source_id, id - 1) and doing "... order by
source_id, id - 1" .

Greetings
Marcin Mańk