From: Tobias Gierke <tobias.gierke@code-sourcery.de>
To: Justin Pryzby <pryzby@telsasoft.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Linear slow-down while inserting into a table with an ON INSERT trigger ?
Date: Sun, 18 Jul 2021 09:36:33 +0200
Message-ID: <b5b020e9-ad28-a309-12e6-5f6d01218b86@code-sourcery.de> (raw)
In-Reply-To: <20210717044026.GA19498@telsasoft.com>
References: <62070f97-13e7-df73-bed1-f8e48c12bf2d@code-sourcery.de>
<20210717044026.GA19498@telsasoft.com>
Thank you for the detailed explanation ! Just one more question: I've
did an experiment and reduced the fillfactor on the table updated by the
trigger to 50%, hoping the HOT feature would kick in and each
subsequent INSERT would clean up the "HOT chain" of the previous INSERT
... but execution times did not change at all compared to 100%
fillfactor, why is this ? Does the HOT feature only work if a different
backend accesses the table concurrently ?
Thanks,
Tobias
> On Fri, Jul 16, 2021 at 11:27:24PM +0200, Tobias Gierke wrote:
>> CREATE OR REPLACE FUNCTION parent_table_changed() RETURNS trigger LANGUAGE plpgsql
>> AS $function$
>> BEGIN
>> UPDATE data_sync SET last_parent_table_change=CURRENT_TIMESTAMP;
>> RETURN NEW;
>> END;
>> $function$
>>
>> I'm trying to insert 30k rows (inside a single transaction) into the parent
> The problem is because you're doing 30k updates of data_sync within a txn.
> Ideally it starts with 1 tuple in 1 page but every row updated requires
> scanning the previous N rows, which haven't been vacuumed (and cannot).
> Update is essentially delete+insert, and the table will grow with each update
> until the txn ends and it's vacuumed.
>
> pages: 176 removed, 1 remain, 0 skipped due to pins, 0 skipped frozen
> tuples: 40000 removed, 1 remain, 0 are dead but not yet removable, oldest xmin: 2027
>
> You could run a single UPDATE rather than 30k triggers.
> Or switch to an INSERT on the table, with an index on it, and call
> max(last_parent_table_change) from whatever needs to ingest it. And prune the
> old entries and vacuum it outside the transaction. Maybe someone else will
> have a better suggestion.
>
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-performance@postgresql.org
Cc: tobias.gierke@code-sourcery.de, pryzby@telsasoft.com
Subject: Re: Linear slow-down while inserting into a table with an ON INSERT trigger ?
In-Reply-To: <b5b020e9-ad28-a309-12e6-5f6d01218b86@code-sourcery.de>
* 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