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: CADmi=6MAPjCuGyLccX5qSYHtnar2C1UFg6AGogB+2UsQM0yGeg@mail.gmail.com (view raw or flat)
Thread:
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:
> 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(at)stuartbishop(dot)net>
http://www.stuartbishop.net/

In response to

Responses

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-2014 The PostgreSQL Global Development Group