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

Can Postgres use an INDEX over an OR?

From: Robert James <srobertjames(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Can Postgres use an INDEX over an OR?
Date: 2009-07-19 23:03:09
Message-ID: e09785e00907191603p25973035kaee55d7bcb7d542c@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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? And how can I shut this off?
select * from dict
where
 word in (select substr('moon', 0, generate_series(3,length('moon')))) --
this is my X above
 OR word like 'moon%' -- this is my Y above

Seq Scan on dict (cost=0.02..2775.66 rows=30422 width=24) (actual
time=16.635..28.580 rows=8 loops=1)
 Filter: ((hashed subplan) OR ((word)::text ~~ 'moon%'::text))
 SubPlan
 -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.014..0.019 rows=2
loops=1)
Total runtime: 28.658 ms
(Using just X or Y alone uses the index, and completes in 0.150 ms)

Is this a bug?

PS Running "PostgreSQL 8.2.1 on i686-pc-mingw32, compiled by GCC gcc.exe
(GCC) 3.4.2 (mingw-special)"

Responses

pgsql-performance by date

Next:From: Alvaro HerreraDate: 2009-07-20 00:26:21
Subject: Re: [PERFORM] Incr/Decr Integer
Previous:From: KradeDate: 2009-07-18 22:07:56
Subject: Full text search with ORDER BY performance issue

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