Re: First steps with 8.3 and autovacuum launcher

On 9/19/07, Decibel! <decibel@decibel.org> wrote:
> You missed my point... what we'd want to happen is for the analyze to
> take place while that table had a good chance of still being in memory.

It seems to be a bit too intrusive for 8.3 at this point.

> It might be worth looking into creating a different lock for ALTERs
> that actually change database page layout vs ALTERs that don't, since
> there's no reason you couldn't run ANALYZE while adding a PK (for
> example).

It seems to be a good idea.

As I restore a lot my test database (a real life 3.1 GB database) to
test the patches on like/ilike, here are some figures to show the
problem a bit more:

** 8.3 (autovacuum on as it's the default) **
Time taken by psql to load my SQL dump (the database is analyzed at
the end of the restore due to autovacuum launcher launching up to 3
processes in parallel):
real    52m55.398s
user    0m37.727s
sys     0m3.114s

** 8.2 (autovacuum off as it's the default) **
Time taken by psql to load the SQL dump:
real    15m5.032s
user    0m37.881s
sys     0m3.040s
Time to analyze the database:
cityvox=# \timing
Timing is on.
cityvox=# ANALYZE;
ANALYZE
Time: 45882.049 ms
So a total of: 16 minutes for 8.2 compared to 53 minutes for 8.3 to
have the database in the same state.

All settings except autovacuum are identical on both clusters.

--
Guillaume