From: | Dmitry Teslenko <dteslenko(at)gmail(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | how to enforce index sub-select over filter+seqscan |
Date: | 2010-09-23 14:26:17 |
Message-ID: | AANLkTik6+38_miWXw0Ejka8hrhUrDyBxaBYz9Yy8sSG_@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Hello!
I have this table:
create table test (
s1 varchar(255),
s2 varchar(255),
i1 integer,
i2 integer,
... over 100 other fields
);
table contains over 8 million records
there's these indexes:
create index is1 on test (s1);
create index is2 on test (s2);
create index ii1 on test (i1);
create index ii2 on test (i2);
create index ii3 on test (i1, i2);
and then i run this query:
select
*
from (
select *
from test
where
is1 = 'aa' or is2 = 'aa'
)
where
is1 = 1
or (is1 = 1
and is2 = 1)
or (is1 = 2
and is2 = 2)
or (is1 = 3
and is2 = 3)
where part of outer query can have different count of
"or (is1 = N
and is2 = M)"
expressions, lets name this number X.
When X is low planner chooses index scan using is1 and is2,
then BitmapAnd that with index scan using ii1, ii2 or ii3.
But when X is big enough (> 15) planner chooses seqscan and filter on
i1, i2, s1, s2.
Seqscan is very slow and I want to avoid it. Subquery is very fast
and i don't know why postgres chooses that plan.
I know I can set enable_seqscan = off.
Is there other ways to enforce index usage?
postgres pg_class have right estimate of rowcount.
--
A: Because it messes up the order in which people normally read text.
Q: Why is top-posting such a bad thing?
A: Top-posting.
Q: What is the most annoying thing in e-mail?
From | Date | Subject | |
---|---|---|---|
Next Message | Kevin Grittner | 2010-09-23 15:43:35 | Re: how to enforce index sub-select over filter+seqscan |
Previous Message | gnuoytr | 2010-09-23 13:51:16 | Re: Useless sort by |