Tim Allen <tim@proximity.com.au> writes:
> 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.
This makes no sense to me, either. Can you put together a
self-contained test case that demonstrates the problem?
One thing that would be useful is to compare the planner statistics
produced by the first and second vacuums. To see the stats, do
select relname,relpages,reltuples from pg_class where
relname in ('tablename', 'indexname', ...);
(include each index on the table, as well as the table itself) and also
select attname,attdispersion,s.*
from pg_statistic s, pg_attribute a, pg_class c
where starelid = c.oid and attrelid = c.oid and staattnum = attnum
and relname = 'tablename';
> Even stranger, it turns out that doing the checkpoint _after_ the vacuum
> analyze also fixes this behaviour, ie queries perform well
> immediately.
I don't really believe that checkpoint has anything to do with it.
However, if the queries are being done in a different backend than the
one doing the vacuum, is it possible that the other backend is inside an
open transaction and does not see the catalog updates from the
later-starting vacuum transaction?
regards, tom lane