Re: Index question regarding numeric operators

["Gurjeet Singh" <singh.gurjeet@xxxxxxxxx>]

On Wed, Oct 1, 2008 at 1:57 PM, Mike Christensen <imaudi@xxxxxxxxxxx> wrote:

Hi guys, I'm very new to PostgreSQL so please excuse me if this is an easy question..

I have a table called Recipes which has a column called CookTime.  I have an index on the CookTime column as such:

CREATE INDEX idx_recipes_cooktime
 ON recipes
 USING btree
 (cooktime);

If I run the following query:

select * from recipes where cooktime = 30;

I get the following execution plan:

"Bitmap Heap Scan on recipes  (cost=260.53..22533.22 rows=10870 width=1029) (actual time=6.881..281.442 rows=10915 loops=1)"
"  Recheck Cond: (cooktime = 30)"
"  ->  Bitmap Index Scan on idx_recipes_cooktime  (cost=0.00..257.82 rows=10870 width=0) (actual time=4.490..4.490 rows=12568 loops=1)"
"        Index Cond: (cooktime = 30)"
"Total runtime: 333.061 ms"

As you can see, the index above is being used and the query is very fast.

However, when I change the query to:

select * from recipes where cooktime > 30;

I get the following execution plan:

"Seq Scan on recipes  (cost=0.00..35090.00 rows=187500 width=1029) (actual time=0.943..997.372 rows=184740 loops=1)"
"  Filter: (cooktime > 30)"
"Total runtime: 1507.961 ms"


As you can see the index is not being used and it's doing a seq scan on the table directly.  I would think if Postgres is indeed keeping a btree index on the column, meaning the values would be stored in numerical order, the index would be used to find rows that have a value greater than 30.  I'm curious as to why this is not the case, or if perhaps I have my index setup incorrectly for this sort of query.

I'd suggest you try this query after an ANALYZE on the table.

--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB      http://www.enterprisedb.com

Mail sent from my BlackLaptop device