I'm just trying to hunt down the reason for some odd behavior I'm noticing
here. We vacuum our database once a week, which is good enough for most
things.
However, there is one query that gets run interactively that slows down
considerably over the course of a week: One memorable week it was taking
over 15 seconds to run. Just after a vacuum it takes around 0.7 seconds.
The odd thing is that while this is a moderately complex query; three
tables, multiple 'union's, etc, *all* the tables involved are fairly
static. The biggest (and the one I've confirmed makes the difference when
I vacuum it) would be having a busy week if there were ten inserts.
Deletes are even rarer. Other tables in this database have several times
that traffic a minute.
So, I'm just wondering if there is a way to figure out why vacuuming that
table in particular makes such a difference. It's not used that often,
but where it is used a person is waiting for the results. (In contrast to
most of our database, which interacts with automated processes for the
most part.)
Of course, the solution is to vacuum it more often. I'm just wondering
*why* it matters. Any ideas on where to look? We stay away from foreign
keys in this database...
Daniel T. Staal
---------------------------------------------------------------
This email copyright the author. Unless otherwise noted, you
are expressly allowed to retransmit, quote, or otherwise use
the contents for non-commercial purposes. This copyright will
expire 5 years after the author's death, or in 30 years,
whichever is longer, unless such a period is in excess of
local copyright law.
---------------------------------------------------------------
