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

2011/10/4 Nowak Michał <michal.nowak@me.com>:
>
> a9-dev=> select  attname, null_frac, avg_width, n_distinct, correlation from pg_stats where tablename = 'records';
>               attname                | null_frac | avg_width | n_distinct | correlation
> --------------------------------------+-----------+-----------+------------+-------------
>  source_id                            |         0 |        54 |         69 |    0.303059

http://www.postgresql.org/docs/9.0/interactive/view-pg-stats.html

"Statistical correlation between physical row ordering and logical
ordering of the column values. This ranges from -1 to +1. When the
value is near -1 or +1, an index scan on the column will be estimated
to be cheaper than when it is near zero, due to reduction of random
access to the disk. (This column is null if the column data type does
not have a < operator.)"

Kind of like I and Tom said, 0.3 correlation there sounds like the cause.
Seriously, try normalisation as well, before discarding it.


--
GJ