Hi Thomas!
Thomas> Look at the ACTUAL TIME. It dropped from 0.029ms (using the index
Thomas> scan) to 0.009ms (using a sequential scan.)
Thomas> Index scans are not always faster, and the planner/optimizer knows
Thomas> this. VACUUM ANALYZE is best run when a large proportion of data
Thomas> has been updated/loaded or in the off hours to refresh the
Thomas> statistics on large datasets.
While I agree that generally this is true, look how stupid this
behavior looks in this particular case: A developer creates a table
and index, knowing that the table will be large and will be intensively
used. An admin runs 'VACUUM ANALYZE' when table is occasionally empty,
and next, say, 1 day, until another 'VACUUM ANALYZE' starts, the index
is simply not used! Sure you don't suppose to run 'VACUUM ANALYZE' every
5 minutes as a solution, right?
I'm not sure if there's ever such thing like planner hints, such as,
"yes, we were switched from index back to seqscan, but this switch is
only valid until table has less than X records", but it sounds as a
reasonable solution.
Well anyway, here's the scenario that cannot be fought neither by
SQL programming nor by administrative guidelines, at least as I see
it. And yes, I looked on the actual time, but somehow am not moved by
how fast postgresql can seqscan an empty table, really. I believe
there's something wrong if decisions based on a table when it is empty,
are suddenly applied when it is full.
--
Sincerely,
Dmitry Karasik
---
catpipe Systems ApS
*BSD solutions, consulting, development
www.catpipe.net
+45 7021 0050
