Re: how to enforce index sub-select over filter+seqscan

On Thu, Sep 23, 2010 at 10:26 AM, Dmitry Teslenko <dteslenko(at)gmail(dot)com> wrote:
> 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)

hm, I think you meant to say:
s1 = 'aa' or s2 = 'aa', i1 = 1 ... etc. details are important!

Consider taking the combination of 'correct' pair of i1 and i2 and
building a table with 'values' and joining to that:

select * from test
join
(
values (2,2), (3,3), ...
) q(i1, i2) using(i1,i2)
where s1 = 'aa' or s2 = 'aa' or i1=1

merlin