Say I'm inserting/updating/deleting a large number of rows (minimum 100k, at most 20M) in batches that must be atomic.

Will pausing every 10-100k operations or so to run ANALYZE within the same transaction actually inform the query planner of anything, since the changes made by this transaction haven't been committed yet?

Logically, imagine a client is doing something like this:

BEGIN;

(for i, record in records)
  INSERT ...
  DELETE ...
  UPDATE ...

  (if i % 10000 == 0)
    ANALYZE;
  (end)
(end loop)

COMMIT;

Will ANALYZE be of any benefit to the query planner for the running transaction, or would it only gather statistics for committed rows?

CodePudding user response：

The ANALYZE will see changes made earlier in the same transaction. But only that same transaction will use those newly gathered statistics. Other transactions will continue to use the prior stats (until the large transaction commits, then they will pick up the new stats which include the now-committed rows).