Re: Linear slow-down while inserting into a table with an ON INSERT trigger ?
Great idea ! This brought the time per INSERT into the parent table down to a consistent ~0.065ms again (compared to 0.05ms when completely removing the trigger, so penalty for the trigger is roughly ~20%). On Sat, 17 Jul 2021 at 16:40, Justin Pryzby wrote: 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. Maybe just change the UPDATE statement to: UPDATE data_sync SET last_parent_table_change=CURRENT_TIMESTAMP WHERE last_parent_table_change <> CURRENT_TIMESTAMP; That should reduce the number of actual updates to 1 per transaction. David
Re: Linear slow-down while inserting into a table with an ON INSERT trigger ?
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: 4 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.
Re: Linear slow-down while inserting into a table with an ON INSERT trigger ?
On Sun, 2021-07-18 at 09:36 +0200, Tobias Gierke wrote: > 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 ? No, but until the transaction is done, the tuples cannot be removed, no matter if they are HOT or not. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
