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
--
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
