Re: VACUUM ANALYZE downgrades performance

On 30 Nov 2004 14:30:37 +0100, Dmitry Karasik <dmitry(at)karasik(dot)eu(dot)org> 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(at)gmail(dot)com
GPLS -- PINES Development
Database Developer
http://open-ils.org