From: Dmitry Karasik <dmitry@karasik.eu.org>
To: pgsql-performance@postgresql.org
Subject: Re: VACUUM ANALYZE downgrades performance
Date: 02 Dec 2004 17:07:17 +0100
Message-ID: <84wtw0d6q2.fsf@plab.ku.dk> (raw)
References: <84fz2ra2gy.fsf_-_@plab.ku.dk>
<41AC94CC.1020602@idigx.com>
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
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: dmitry@karasik.eu.org
Subject: Re: VACUUM ANALYZE downgrades performance
In-Reply-To: <84wtw0d6q2.fsf@plab.ku.dk>
* 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