| From: | David Rowley <dgrowleyml(at)gmail(dot)com> |
|---|---|
| To: | Justin Pryzby <pryzby(at)telsasoft(dot)com> |
| Cc: | Tobias Gierke <tobias(dot)gierke(at)code-sourcery(dot)de>, pgsql-performance <pgsql-performance(at)postgresql(dot)org> |
| Subject: | Re: Linear slow-down while inserting into a table with an ON INSERT trigger ? |
| Date: | 2021-07-17 07:02:31 |
| Message-ID: | CAApHDvr-kFAmOihG1FxVZfgdV_e5TR8cU3n+QPkYwJ8XqmA4YQ@mail.gmail.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-performance |
On Sat, 17 Jul 2021 at 16:40, Justin Pryzby <pryzby(at)telsasoft(dot)com> 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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Tom Lane | 2021-07-17 14:33:09 | Re: Linear slow-down while inserting into a table with an ON INSERT trigger ? |
| Previous Message | Benjamin Scherrey | 2021-07-17 06:48:45 | Re: Linear slow-down while inserting into a table with an ON INSERT trigger ? |