On 11/30/2004 7:30 AM Dmitry Karasik said::
Look at the ACTUAL TIME. It dropped from 0.029ms (using the index scan) to 0.009ms (using a sequential scan.)Hi all,
On v7.4.5 I noticed downgrade in the planner, namely favoring sequential scan over index scan. The proof:
create table a ( a integer);
create index aidx on a(a);
explain analyze select * from a where a = 0;
-- Index Scan using aidx on a (cost=0.00..17.07 rows=5 width=4) (actual
-- time=0.029..0.029 rows=0 loops=1)
-- Index Cond: (a = 0)
vacuum analyze;
explain analyze select * from a where a = 0;
-- Seq Scan on a (cost=0.00..0.00 rows=1 width=4) (actual time=0.009..0.009 -- rows=0 loops=1)
-- Filter: (a = 0)
I do realize that there might be reasons why this happens over an empty
table, but what is way worse that when the table starts actually to fill,
the seq scan is still there, and the index is simply not used. How
that could be so ...mmm... shortsighted, and what is more important, how to avoid this? I hope the answer is not 'run vacuum analyze each 5 seconds'.
Index scans are not always faster, and the planner/optimizer knows this. VACUUM ANALYZE is best run when a large proportion of data has been updated/loaded or in the off hours to refresh the statistics on large datasets.
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
