Postgres Pro
Downloads
Home   >   mailing lists

Thread: Why is VACUUM ANALYZE so slow?

Why is VACUUM ANALYZE

so slow?
From
"David Shadovitz"
Date:
I'm running PG 7.2.2 on RH Linux 8.0.

I'd like to know why "VACUUM ANALYZE <table>" is extemely slow (hours) for
certain tables.  Here's what the log file shows when I run this command on
my "employees" table, which has just 5 columns and 55 records:

VACUUM ANALYZE employees

DEBUG: --Relation employees--
DEBUG: index employees_pkey: Pages 2; Tuples 55: Deleted 0.
   CPU 0.00s/0.00u sec elapsed 0.00 sec.
DEBUG: index emp_dept_id_idx: Pages 2; Tuples 55: Deleted 0.
   CPU 0.00s/0.00u sec elapsed 0.00 sec.
DEBUG: index emp_emp_num_idx: Pages 2; Tuples 55: Deleted 0.
   CPU 0.00s/0.00u sec elapsed 0.00 sec.
DEBUG: recycled transaction log file 00000000000000CC
DEBUG: geqo_main: using edge recombination crossover [ERX]

(When I get a chance I will enable timestamping of log file entries.)

Thanks for any insight.  Please reply to me personally (david@shadovitz.com)
as well as to the list.

-David

Re: Why is VACUUM ANALYZE

so slow?
From
Neil Conway
Date:
"David Shadovitz" <david@www.shadovitz.com> writes:
> I'm running PG 7.2.2 on RH Linux 8.0.

Note that this version of PostgreSQL is quite old.

> I'd like to know why "VACUUM ANALYZE <table>" is extemely slow (hours) for
> certain tables.

Is there another concurrent transaction that has modified the table
but has not committed? VACUUM ANALYZE will need to block waiting for
it. You might be able to get some insight into this by examining the
pg_locks system view:

http://www.postgresql.org/docs/current/static/monitoring-locks.html

As well as the pg_stat_activity view.

-Neil

Re: Why is VACUUM ANALYZE

so slow?
From
David Shadovitz
Date:
Neil,

Thanks for the good advice.  I noticed that I had some sessions for which I
could not account, and I think even a 2nd postmaster running.  It looks like
I've cleaned everything up, and now I can VACUUM and I can DROP an index which
wouldn't drop.

And I'm looking into upgrading PostgreSQL.

-David

On Tuesday, December 16, 2003 2:51 PM, Neil Conway [SMTP:neilc@samurai.com]
wrote:
> "David Shadovitz" <david@www.shadovitz.com> writes:
> > I'm running PG 7.2.2 on RH Linux 8.0.
>
> Note that this version of PostgreSQL is quite old.
>
> > I'd like to know why "VACUUM ANALYZE <table>" is extemely slow (hours) for
> > certain tables.
>
> Is there another concurrent transaction that has modified the table
> but has not committed? VACUUM ANALYZE will need to block waiting for
> it. You might be able to get some insight into this by examining the
> pg_locks system view:
>
> http://www.postgresql.org/docs/current/static/monitoring-locks.html
>
> As well as the pg_stat_activity view.
>
> -Neil