Re: Index question regarding numeric operators

Thanks Tom!

I just tried a query for cooktimes over 1 million to test your theory,
as it would almost instantly be able to tell from the index that there
are zero rows matching that condition. Indeed, it hits the index
which is what I would expect, and the total runtime is 0.163ms.

Thanks again,
Mike

On Oct 1, 2008, at 6:21 AM, Tom Lane wrote:

> 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