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