how to enforce index sub-select over filter+seqscan

Поиск
Список
Период
Сортировка
От Dmitry Teslenko
Тема how to enforce index sub-select over filter+seqscan
Дата
Msg-id AANLkTik6+38_miWXw0Ejka8hrhUrDyBxaBYz9Yy8sSG_@mail.gmail.com
обсуждение исходный текст
Ответы Re: how to enforce index sub-select over filter+seqscan  ("Kevin Grittner" <Kevin.Grittner@wicourts.gov>)
Re: how to enforce index sub-select over filter+seqscan  (Merlin Moncure <mmoncure@gmail.com>)
Re: how to enforce index sub-select over filter+seqscan  (Robert Haas <robertmhaas@gmail.com>)
Список 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?

В списке pgsql-performance по дате отправления:

Предыдущее
От:
Дата:
Сообщение: Re: Useless sort by
Следующее
От: "Kevin Grittner"
Дата:
Сообщение: Re: how to enforce index sub-select over filter+seqscan