PostgreSQL - 30000 row updates, setting non-indexed boolean column to 'true' takes hours

Question

I am running PostgreSQL 13, and single UPDATE statement that seems like it would be trivial to complete can take hours (at least in one case more than 24 hours).

Selecting the rows with a SELECT statement takes under a second. Updating the rows is drastically slower, within only 30000 rows affected.

The column in question that I am trying to update is not indexed. The database is also well-provisioned for the scale of the database. While the query is active, I can see 1 of the 4 vCPU at full throttle. Memory settings are tuned to typical defaults recommended for a 32GB RAM system.

After the first execution, I can flip the boolean archived column back & forth much more quickly (5 minutes first UPDATE -> 15 seconds to do inverse in my simulated case).

There are some properties that make this table unique. The schema of the DB is highly normalized. This table holds the names and uuids for many of the data models, so there are many FK relationships. The table has 16 columns, with 10 indexes and 4 constraints. The updated column value is not part of a constraint or an index.

Why is performance for this update query so poor? Is there anything I can do to improve the performance?

Answer 1

The statement could be rewritten to the simpler

UPDATE resource AS r
SET archived = TRUE
FROM sample_sheet AS ss
WHERE r.resource_id = ss.sample_sheet_id
  AND r.created_timestamp < current_timestamp - INTERVAL '120 00:00:00'
  AND ss.state IN ('done', 'failed');

But that won't help your performance. All the time is spent in the actual update. Apart from a really, really slow disk, possible causes could be

• locks that block execution of the update

• excessively many indexes on the table (and perhaps slow ones like GIN indexes)

• a trigger can be ruled out, because that should show up in EXPLAIN (ANALYZE) output

I would set track_io_timing = on and retry the query with EXPLAIN (ANALYZE, BUFFERS, WAL) for more detailed information.

Answer 2

After being able to reproduce the problem with several similar queries, it looks like certain forms of the UPDATE query can result in a very expensive Materialize node that loops to join the updated table's rows & columns as well as the tuple ctid for the other referenced tables.

https://explain.dalibo.com/plan/08729h152df894fd

EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)
UPDATE resource
  SET archived = true
  FROM resource r_ss
  JOIN sample_sheet ss on r_ss.resource_id = ss.sample_sheet_id
  WHERE r_ss.created_timestamp < (now() - INTERVAL '120 DAY') AND ss.state in ('done', 'failed');

A sequential scan is done on the 333 rows in sample_sheet. These are joined 1:1 with an index scan to the resource table aliased as r_ss (the resource entries for those sheets). The joined result also carries the ctid. This result (constrained by filters & index lookups) is then materialized (55,000,000 rows) and joined again to the updated table resource (no alias) in a loop. The resource table itself has 200,000 rows in this example. In this instance, the operation updated 277 rows in 2 minutes.

This form of the query does not result in the expensive materialize operations: https://explain.dalibo.com/plan/bf5377ed6d4dca95

EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)
UPDATE resource AS r_ss
  SET archived = true
  FROM sample_sheet ss
  WHERE 
  r_ss.resource_id = ss.sample_sheet_id
  AND r_ss.created_timestamp < (now() - INTERVAL '120 DAY') 
  AND ss.state in ('done', 'failed');

Verified there were no locks blocking, vacuum had minimal impact, and HOT updates had minimal impact. Worth noting that updating all 200,000 rows without any WHERE clause took ~8 seconds at 100% fill factor (only ~10% of tuple updates were HOT updates). At 50% fill factor for the table the full update took ~2 seconds with all updates being HOT. The materialize node resulting from the unnecessary join of the updated resource table and the product of resource r_ss x sample_sheet ss seemed to be the root cause.