On Fri, Nov 11, 2011 at 10:01 PM, Ruslan Zakirov <ruz(at)bestpractical(dot)com> wrote:
> 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:
> 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(at)stuartbishop(dot)net>
In response to
pgsql-performance by date
|Next:||From: Maciek Sakrejda||Date: 2011-11-15 07:32:34|
|Subject: Re: What's the state of postgresql on ext4 now?|
|Previous:||From: Cody Caughlan||Date: 2011-11-15 01:04:20|
|Subject: Re: Large number of short lived connections - could a
connection pool help?|