Hello,
A table has two columns id and EffectiveId. First is primary key.
EffectiveId is almost always equal to id (95%) unless records are
merged. Many queries have id = EffectiveId condition. Both columns are
very distinct and Pg reasonably decides that condition has very low
selectivity and picks sequence scan.
Simple perl script that demonstrates estimation error:
https://gist.github.com/1356744
Estimation is ~200 times off (5 vs 950), for real situation it's very
similar. Understandably difference depends on correlation coefficient.
In application such wrong estimation result in seq scan of this table
winning leading position in execution plans over other tables and
index scans.
What can I do to avoid this problem?
Tested with PostgreSQL 9.0.3 on x86_64-apple-darwin10.6.0, compiled by
GCC i686-apple-darwin10-gcc-4.2.1 (GCC) 4.2.1 (Apple Inc. build 5664),
64-bit
--
Best regards, Ruslan.
