From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: postgres@burri.li
Subject: BUG #16967: Extremely slow update statement in trigger
Date: Fri, 16 Apr 2021 16:52:15 +0000
Message-ID: <16967-39db5f50df41173a@postgresql.org> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, postgres@burri.li
Subject: Re: BUG #16967: Extremely slow update statement in trigger
In-Reply-To: <16967-39db5f50df41173a@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox