On Wed, Oct 1, 2008 at 1:57 PM, Mike Christensen <[EMAIL PROTECTED]> 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. -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com EnterpriseDB http://www.enterprisedb.com Mail sent from my BlackLaptop device
