Skip site navigation (1) Skip section navigation (2)

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

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 (view raw or flat)
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

In response to

pgsql-performance by date

Next:From: Tobias BroxDate: 2010-09-23 20:47:26
Subject: Re: locking issue on simple selects?
Previous:From: Scott MarloweDate: 2010-09-23 17:11:50
Subject: Re: Useless sort by

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group