Re: Seq scan vs. Index scan with different query conditions

eleven(at)ludojad(dot)itpp(dot)pl wrote:

> -> Index Scan using "DateTimeIndex" on "tablex" (cost=0.00..298272.66 rows=89903 width=8)

> -> Seq Scan on "tablex" (cost=0.00..307137.34 rows=97900 width=8)

> Basically, the difference is in upper "Time" value (as you can see, it's
> 18:01:00 in the first query and 19:01:00 in the other one).
> The question is - why does it use index in first case and
> it tries to do full sequential scan when the upper "Time" value
> is different?

Look at the rows, and more importantly the cost. PG thinks the cost in
the second case (seq scan) is only slightly more than in the first case
(index), so presumably the index scan worked out more expensive.

You can test this by issuing "SET ENABLE_SEQSCAN=OFF;" and re-running
the second explain.

Now, the question is whether PG is right in these cost estimates. You'll
need to run "EXPLAIN ANALYSE" rather than just EXPLAIN to see what it
actually costs.

PS - all the usual questions: make sure you've vacuumed, have you read
the tuning document on varlena.com?

--
Richard Huxton
Archonet Ltd