Skip site navigation (1) Skip section navigation (2)

Re: 7.1.3 w/ Perl/DBI application hangs

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Doran L(dot) Barton" <fozz(at)iodynamics(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: 7.1.3 w/ Perl/DBI application hangs
Date: 2001-11-13 18:05:29
Message-ID: 2674.1005674729@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
"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

In response to

pgsql-bugs by date

Next:From: Tom LaneDate: 2001-11-13 18:58:56
Subject: Re: Installation problem
Previous:From: KauĂȘ S. EwbankDate: 2001-11-13 16:23:37
Subject: Error.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group