On Fri, Nov 11, 2011 at 10:01 PM, Ruslan Zakirov <ruz@xxxxxxxxxxxxxxxxx> wrote: > 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? Does a partial index help? CREATE UNIQUE INDEX foo_idx ON mytab(id) WHERE id = EffectiveId -- Stuart Bishop <stuart@xxxxxxxxxxxxxxxx> http://www.stuartbishop.net/ -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
