Віталій Тимчишин wrote:
>
>
> 2009/7/20 Robert James <srobertjames@gmail.com
> <mailto:srobertjames@gmail.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/
