The following bug has been logged on the website:
Bug reference: 16967
Logged by: Nicolas Burri
Email address: postgres@burri.li
PostgreSQL version: 13.2
Operating system: Linux & Windows
Description:
Hi,
During a data migration our production system spent several hours in a
simple update statement of a table with very few columns and about 200k
records. We managed to reproduce the problem and a minimal setup for
reproduction can be found here:
https://www.dropbox.com/s/mxjavpl43s48hdg/bug_report.sql. The bug seems to
be related to query plan caching and can be reproduced on postrgres 11.11
and 13.2 (I have not run tests with other versions)
Description:
We use a statement level trigger to log changes to records in a certain
table. On every update, the trigger calls a function comparing “old table”
and “new table”. For every record changed by the statement, the function
inserts a new record in a “log” table. In the following scenario, execution
times explode:
First, execute an update statement that affects no rows. This query is fast
and completes within milliseconds. Then, execute a second update statement
that affects a lot of records. At 200k records, this query runs for more
than 4h on my workstation. If we call “discard plans” before executing the
second update statement, or if we do not execute the first statement at all,
the update of all rows completes within about 1 second.
Thanks and best Regards
Nicolas