Re: 7.1.3 w/ Perl/DBI application hangs

"Doran L. Barton" <fozz(at)iodynamics(dot)com> writes:
> The problem I'm having with the queries "hanging" is apparently happening
> as soon as a VACUUM ANALYZE is done on the database. Until a VACUUM ANALYZE
> is done, the database is speedy and responsive. As soon as a VACUUM ANALYZE
> is performed, the same queries executed before spin indefinitely.

> A VACUUM by itself doesn't bring on this behavior- so it must be the
> ANALYZE portion.

Interesting. VACUUM ANALYZE essentially does the following:

begin transaction;
vacuum table; -- slow
commit transaction;
[ stop here if no ANALYZE requested ]
begin transaction;
delete rows for table in pg_statistic;
scan table to construct new statistics; -- slow
add rows for table to pg_statistic;
commit transaction;

I am wondering if queries started while the ANALYZE scan is in progress
somehow fail to see any statistics available for the table, and end up
choosing stupid query plans. That shouldn't happen, since the deletion
of the old rows is part of a not-yet-committed transaction --- other
transactions should see the old rows as still valid. But it'd be worth
your while to launch a few EXPLAINs to see if the plans for the problem
queries change while the ANALYZE phase is in progress.

regards, tom lane