pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedavoiding seq scans when two columns are very correlated
5+ messages / 3 participants
[nested] [flat]
* avoiding seq scans when two columns are very correlated
@ 2011-11-11 15:01 Ruslan Zakirov <ruz@bestpractical.com>
2011-11-11 15:36 ` Re: avoiding seq scans when two columns are very correlated Tom Lane <tgl@sss.pgh.pa.us>
2011-11-15 07:08 ` Re: avoiding seq scans when two columns are very correlated Stuart Bishop <stuart@stuartbishop.net>
0 siblings, 2 replies; 5+ messages in thread
From: Ruslan Zakirov @ 2011-11-11 15:01 UTC (permalink / raw)
To: pgsql-performance
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?
Tested with PostgreSQL 9.0.3 on x86_64-apple-darwin10.6.0, compiled by
GCC i686-apple-darwin10-gcc-4.2.1 (GCC) 4.2.1 (Apple Inc. build 5664),
64-bit
--
Best regards, Ruslan.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: avoiding seq scans when two columns are very correlated
2011-11-11 15:01 avoiding seq scans when two columns are very correlated Ruslan Zakirov <ruz@bestpractical.com>
@ 2011-11-11 15:36 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-11-11 16:39 ` Re: avoiding seq scans when two columns are very correlated Ruslan Zakirov <ruz@bestpractical.com>
1 sibling, 1 reply; 5+ messages in thread
From: Tom Lane @ 2011-11-11 15:36 UTC (permalink / raw)
To: Ruslan Zakirov <ruz@bestpractical.com>; +Cc: pgsql-performance
Ruslan Zakirov <ruz@bestpractical.com> writes:
> 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.
I think the only way is to rethink your data representation. PG doesn't
have cross-column statistics at all, and even if it did, you'd be asking
for an estimate of conditions in the "long tail" of the distribution.
That's unlikely to be very accurate.
Consider adding a "merged" boolean, or defining effectiveid differently.
For instance you could set it to null in unmerged records; then you
could get the equivalent of the current meaning with
COALESCE(effectiveid, id). In either case, PG would then have
statistics that bear directly on the question of how many merged vs
unmerged records there are.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: avoiding seq scans when two columns are very correlated
2011-11-11 15:01 avoiding seq scans when two columns are very correlated Ruslan Zakirov <ruz@bestpractical.com>
2011-11-11 15:36 ` Re: avoiding seq scans when two columns are very correlated Tom Lane <tgl@sss.pgh.pa.us>
@ 2011-11-11 16:39 ` Ruslan Zakirov <ruz@bestpractical.com>
0 siblings, 0 replies; 5+ messages in thread
From: Ruslan Zakirov @ 2011-11-11 16:39 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
On Fri, Nov 11, 2011 at 7:36 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Ruslan Zakirov <ruz@bestpractical.com> writes:
>> 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.
>
> I think the only way is to rethink your data representation. PG doesn't
> have cross-column statistics at all, and even if it did, you'd be asking
> for an estimate of conditions in the "long tail" of the distribution.
> That's unlikely to be very accurate.
Rethinking schema is an option that requires more considerations as we
do it this way for years and run product on mysql, Pg and Oracle.
Issue affects Oracle, but it can be worked around by dropping indexes
or may be by building correlation statistics in 11g (didn't try it
yet).
Wonder if "CROSS COLUMN STATISTICS" patch that floats around would
help with such case?
> Consider adding a "merged" boolean, or defining effectiveid differently.
> For instance you could set it to null in unmerged records; then you
> could get the equivalent of the current meaning with
> COALESCE(effectiveid, id). In either case, PG would then have
> statistics that bear directly on the question of how many merged vs
> unmerged records there are.
NULL in EffectiveId is the way to go, however when we actually need
those records (not so often situation) query becomes frightening:
SELECT main.* FROM Tickets main
JOIN Tickets te
ON te.EffectiveId = main.id
OR (te.id = main.id AND te.EffectiveId IS NULL)
JOIN OtherTable ot
ON ot.Ticket = te.id
Past experience reminds that joins with ORs poorly handled by many optimizers.
In the current situation join condition is very straightforward and effective.
> regards, tom lane
--
Best regards, Ruslan.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: avoiding seq scans when two columns are very correlated
2011-11-11 15:01 avoiding seq scans when two columns are very correlated Ruslan Zakirov <ruz@bestpractical.com>
@ 2011-11-15 07:08 ` Stuart Bishop <stuart@stuartbishop.net>
2011-11-15 15:04 ` Re: avoiding seq scans when two columns are very correlated Ruslan Zakirov <ruz@bestpractical.com>
1 sibling, 1 reply; 5+ messages in thread
From: Stuart Bishop @ 2011-11-15 07:08 UTC (permalink / raw)
To: Ruslan Zakirov <ruz@bestpractical.com>; +Cc: pgsql-performance
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/
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: avoiding seq scans when two columns are very correlated
2011-11-11 15:01 avoiding seq scans when two columns are very correlated Ruslan Zakirov <ruz@bestpractical.com>
2011-11-15 07:08 ` Re: avoiding seq scans when two columns are very correlated Stuart Bishop <stuart@stuartbishop.net>
@ 2011-11-15 15:04 ` Ruslan Zakirov <ruz@bestpractical.com>
0 siblings, 0 replies; 5+ messages in thread
From: Ruslan Zakirov @ 2011-11-15 15:04 UTC (permalink / raw)
To: Stuart Bishop <stuart@stuartbishop.net>; +Cc: pgsql-performance
On Tue, Nov 15, 2011 at 11:08 AM, Stuart Bishop <stuart@stuartbishop.net> wrote:
> On Fri, Nov 11, 2011 at 10:01 PM, Ruslan Zakirov <ruz@bestpractical.com> wrote:
>> Hello,
[snip]
>> 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
It doesn't help. Probably reason is the same as for partitions.
>
>
> --
> Stuart Bishop <stuart@stuartbishop.net>
> http://www.stuartbishop.net/
>
--
Best regards, Ruslan.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2011-11-15 15:04 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-11-11 15:01 avoiding seq scans when two columns are very correlated Ruslan Zakirov <ruz@bestpractical.com>
2011-11-11 15:36 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-11-11 16:39 ` Ruslan Zakirov <ruz@bestpractical.com>
2011-11-15 07:08 ` Stuart Bishop <stuart@stuartbishop.net>
2011-11-15 15:04 ` Ruslan Zakirov <ruz@bestpractical.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox