We are seeing what seems to me to be very peculiar behaviour. We have a
schema upgrade script that alters the schema of an existing production
database. One of the things we do is create two new indexes. The script
then immediately performs a vacuum analyze.
The problem is (or was) that this analyze didn't seem to work. Queries
performed thereafter would run slowly. Doing another vacuum analyze later
on would fix this, and queries would then perform well.
We have two approaches that fix this. The first was to just sleep for two
seconds between creating the indexes and doing the vacuum analyze. The
second was to perform an explicit checkpoint between index creation and
vacuum analyze. The second approach seems the most sound, the sleep
approach relies too much on coincidence. But both work in our tests so
far.
However, why is this so? Can analyze not work properly unless the data
files have all been fsynced to disk? Does the WAL really stop analyze from
working?
Even stranger, it turns out that doing the checkpoint _after_ the vacuum
analyze also fixes this behaviour, ie queries perform well
immediately. This part is _so_ strange that I'm tempted to just not
believe it ever happened... except that it seems it did.
Any insights? Is this expected behaviour? Can anyone explain why this is
happening? We have a workaround (checkpoint), so we're not too concerned,
but would like to understand what's going on.
Platform is PG7.1.2 on Red Hat Linux 6.2, x86.
Tim
--
-----------------------------------------------
Tim Allen tim@proximity.com.au
Proximity Pty Ltd http://www.proximity.com.au/ http://www4.tpg.com.au/users/rita_tim/