Re: Index question regarding numeric operators

On Wed, Oct 1, 2008 at 1:57 PM, Mike Christensen <imaudi(at)comcast(dot)net> 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[(dot)singh](at)EnterpriseDB(dot)com
singh(dot)gurjeet(at){ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

Mail sent from my BlackLaptop device