Skip site navigation (1) Skip section navigation (2)

Re: avoiding seq scans when two columns are very correlated

From: Stuart Bishop <stuart(at)stuartbishop(dot)net>
To: Ruslan Zakirov <ruz(at)bestpractical(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: avoiding seq scans when two columns are very correlated
Date: 2011-11-15 07:08:20
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
On Fri, Nov 11, 2011 at 10:01 PM, Ruslan Zakirov <ruz(at)bestpractical(dot)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:
> 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 SakrejdaDate: 2011-11-15 07:32:34
Subject: Re: What's the state of postgresql on ext4 now?
Previous:From: Cody CaughlanDate: 2011-11-15 01:04:20
Subject: Re: Large number of short lived connections - could a connection pool help?

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group