Re: Index question regarding numeric operators

Mike Christensen <imaudi(at)comcast(dot)net> writes:
> 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.

It's not using the index because it thinks that'll be slower, given the
large fraction of the table it'll have to retrieve anyway. Seeing the
relatively small runtime I think it made the right choice ---
extrapolating from the first test suggests that doing this with a bitmap
scan would have taken ~ 3 seconds instead of 1.5.

You could experiment with forcing another plan (using enable_seqscan and
related settings) to see if the planner guessed right or not. Bear in
mind though that timings for successive runs of related queries will be
affected by caching: the later tests will look faster because the data
they need is already swapped in.

regards, tom lane