From: Thomas Swan <tswan@idigx.com>
To: Dmitry Karasik <dmitry@karasik.eu.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: VACUUM ANALYZE downgrades performance
Date: Tue, 30 Nov 2004 09:42:04 -0600
Message-ID: <41AC94CC.1020602@idigx.com> (raw)
In-Reply-To: <84fz2ra2gy.fsf_-_@plab.ku.dk>
References: <84fz2ra2gy.fsf_-_@plab.ku.dk>
On 11/30/2004 7:30 AM Dmitry Karasik said::
>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'.
>
>
>
Look at the ACTUAL TIME. It dropped from 0.029ms (using the index
scan) to 0.009ms (using a sequential scan.)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tswan@idigx.com, dmitry@karasik.eu.org
Subject: Re: VACUUM ANALYZE downgrades performance
In-Reply-To: <41AC94CC.1020602@idigx.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox