-> 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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?


Reply via email to