Hi,
we are seeing cases of EXPLAIN INSERT INTO foo SELECT ... taking over an
hour, with disk I/O utilization (percent of time device is busy) at 100%
the whole time, although I/O bandwidth is not saturated. This is on
PostgreSQL 9.1.13.
What could cause this? Note that there is no ANALYZE. Is it
Hi, thanks for your follow-up questions.
- postgres version is 9.1.13
- the number of rows (in this latest instance) is 28,474,842
- I've clustered and vacuum-full-ed and analyzed this table frequently,
attempting to troubleshoot this. (Running vacuum full on the whole catalog
seems a little exces
On Sat, Mar 7, 2015 at 3:44 PM, Tomas Vondra
wrote:
> Another possibility is that this is part
> of some large batch, and autovacuum simply did not have change to do the
> work.
>
Yes, I think that's it: I've just realized that immediately prior to the
INSERT, in the same transaction, an unfilte
On Mon, Mar 16, 2015 at 7:24 PM, Jim Nasby wrote:
> The other thing you should consider is using TRUNCATE instead of an
> un-filtered DELETE. It will both be much faster to perform and won't leave
> any dead rows behind.
Yep, but it does take an ACCESS EXCLUSIVE lock. We want the old table
cont
