From: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
---|---|
To: | Dmitry Teslenko <dteslenko(at)gmail(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: how to enforce index sub-select over filter+seqscan |
Date: | 2010-09-23 19:41:43 |
Message-ID: | AANLkTi=c9ZcXKhSJnnefYYX0Me5JJKpcFx5WA9OKzuUX@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tobias Brox | 2010-09-23 20:47:26 | Re: locking issue on simple selects? |
Previous Message | Scott Marlowe | 2010-09-23 17:11:50 | Re: Useless sort by |