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

Wiadomość napisana przez Tom Lane w dniu 3 paź 2011, o godz. 17:12:

> 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.

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
--------------------------------------+-----------+-----------+------------+-------------
id | 0 | 8 | -1 | 0.932887
last_processing_date | 0.886093 | 8 | 38085 | 0.427959
object_id | 0 | 27 | -0.174273 | 0.227186
processing_path | 0 | 14 | 14 | 0.970166
schema_id | 0 | 17 | 68 | 0.166175
delete_date | 0.999897 | 8 | 29 | 0.63629
data | 0 | 949 | -0.267811 | 0.158279
checksum | 0 | 33 | -0.267495 | 0.0269071
source_id | 0 | 54 | 69 | 0.303059
source_object_last_modification_date | 0 | 8 | 205183 | 0.137143
(10 rows)

> 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.

I'll try that.

Regards,
Michal Nowak