Re: avoiding seq scans when two columns are very correlated

On Fri, Nov 11, 2011 at 10:01 PM, Ruslan Zakirov <ruz@bestpractical.com> 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@stuartbishop.net>
http://www.stuartbishop.net/