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

Re: Can Postgres use an INDEX over an OR?

From: Chris <dmagick(at)gmail(dot)com>
To: Віталій Тимчишин <tivv00(at)gmail(dot)com>
Cc: Robert James <srobertjames(at)gmail(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Can Postgres use an INDEX over an OR?
Date: 2009-07-20 08:02:12
Message-ID: 4A642484.8020107@gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
Віталій Тимчишин wrote:
> 
> 
> 2009/7/20 Robert James <srobertjames(at)gmail(dot)com 
> <mailto:srobertjames(at)gmail(dot)com>>
> 
> 
>     Hi. I notice that when I do a WHERE x, Postgres uses an index, and
>     when I do WHERE y, it does so as well, but when I do WHERE x OR y,
>     it doesn't. Why is this so? 
> 
> 
> It's not clever enough.

Of course it is.

I'm running 8.3.7.

create table t1(id int primary key);
insert into t1(id) select a from generate_series(1, 500000) as s(a);
analyze t1;

explain analyze select * from t1 where id=5000 or id=25937;
                                                       QUERY PLAN 

----------------------------------------------------------------------------------------------------------------------
  Bitmap Heap Scan on t1  (cost=8.60..16.44 rows=2 width=4) (actual 
time=0.077..0.083 rows=2 loops=1)
    Recheck Cond: ((id = 5000) OR (id = 25937))
    ->  BitmapOr  (cost=8.60..8.60 rows=2 width=0) (actual 
time=0.063..0.063 rows=0 loops=1)
          ->  Bitmap Index Scan on t1_pkey  (cost=0.00..4.30 rows=1 
width=0) (actual time=0.034..0.034 rows=1 loops=1)
                Index Cond: (id = 5000)
          ->  Bitmap Index Scan on t1_pkey  (cost=0.00..4.30 rows=1 
width=0) (actual time=0.021..0.021 rows=1 loops=1)
                Index Cond: (id = 25937)
  Total runtime: 0.153 ms
(8 rows)

What Robert didn't post was his query, see

http://archives.postgresql.org/pgsql-general/2009-07/msg00767.php

which makes it a lot harder to 'optimize' since they aren't straight 
forward conditions.

-- 
Postgresql & php tutorials
http://www.designmagick.com/


In response to

Responses

pgsql-performance by date

Next:From: ramasubramanianDate: 2009-07-20 09:05:31
Subject: Trigger on column
Previous:From: Віталій ТимчишинDate: 2009-07-20 06:18:43
Subject: Re: Can Postgres use an INDEX over an OR?

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