Re: Help tracking down problem with inserts slowing

Steve Wampler <swampler(at)noao(dot)edu> writes:
> PG: 7.2.3 (RedHat 8.0)

You're using PG 7.2.3 with the PG 7.1 JDBC driver; FWIW, upgrading to
newer software is highly recommended.

> The two sites were performing at comparable speeds until a few days
> ago, when we deleted several million records from each database and
> then did a vacuum full; analyze on both. Now inserts at Site B are
> several orders of magnitude slower than at Site A.

Two thoughts:

(1) Can you confirm that the VACUUM FULL on site B actually
removed all the tuples you intended it to remove? Concurrent
transactions can limit the amount of data that VACUUM FULL is
able to reclaim. If you run contrib/pgstattuple (or compare
the database's disk consumption with the number of live rows
in it), you should be able to tell.

(2) Look at the EXPLAIN for the SELECTs generated by the ON INSERT
trigger -- is there any difference between site A and B?

-Neil