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've also tried the query on MS SQL Server and both queries go against
the index and are super fast. I've come from a SQL Server background
and am very new to Postgres, so I'm guessing there is a problem with
my index. Perhaps someone can explain how this works, thanks!!
Mike
