On 30 Nov 2004 14:30:37 +0100, Dmitry Karasik <dmitry@xxxxxxxxxxxxxx> wrote: > > 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) Looks to me like the seq scan is a better plan. The "actual time" went down. > > 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'. > See this thread (http://archives.postgresql.org/pgsql-hackers/2004-11/msg00985.php and http://archives.postgresql.org/pgsql-hackers/2004-11/msg01080.php) for an ongoing discussion of the issue. -- Mike Rylander mrylander@xxxxxxxxx GPLS -- PINES Development Database Developer http://open-ils.org
