how to enforce index sub-select over filter+seqscan

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?

Responses

Browse pgsql-performance by date

  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