From: Tobias Gierke <tobias.gierke@code-sourcery.de>
To: pgsql-performance@lists.postgresql.org
Subject: Re: Linear slow-down while inserting into a table with an ON INSERT trigger ?
Date: Sun, 18 Jul 2021 09:19:00 +0200
Message-ID: <009d281a-9d45-fd66-5c81-967b2b072792@code-sourcery.de> (raw)
In-Reply-To: <CAOFEiBfG7B-ZyPgSzXpn-HO5WwWGqaizUEMkwxv2N18O6thERw@mail.gmail.com>
References: <62070f97-13e7-df73-bed1-f8e48c12bf2d@code-sourcery.de>
<CAOFEiBfG7B-ZyPgSzXpn-HO5WwWGqaizUEMkwxv2N18O6thERw@mail.gmail.com>
> How much does the function-ed UPDATE statement take?
INSERTs into the parent table are taking ~0.1 ms/INSERT at the beginning
of the transaction and this increases to ~0.43 ms after 30k INSERTs.
Without the trigger, INSERTs into the parent table consistently take
around 0.05ms.
Regards,
Tobias
>
>
> Regards,
> Ninad Shah
>
> On Sat, 17 Jul 2021 at 02:57, Tobias Gierke
> <tobias.gierke@code-sourcery.de
> <mailto:tobias.gierke@code-sourcery.de>> wrote:
>
> Hi,
>
> Probably my google-foo is weak today but I couldn't find any
> (convincing) explanation for this.
>
> I'm running PostgreSQL 12.6 on 64-bit Linux (CentOS 7, PostgreSQL
> compiled from sources) and I'm trying to insert 30k rows into a
> simple table that has an "ON INSERT .. FOR EACH STATEMENT" trigger.
>
> Table "public.parent_table"
>
> Column | Type | Collation | Nullable | Default
> -------------+------------------------+-----------+----------+--------------------------------------------
> id | bigint | | not null | nextval('parent_table_id_seq'::regclass)
> name | character varying(64) | | not null |
> enabled | boolean | | not null |
> description | character varying(255) | | |
> deleted | boolean | | not null | false
> is_default | boolean | | not null | false
>
> Indexes:
> "parent_pkey" PRIMARY KEY, btree (id)
> "uniq_name" UNIQUE, btree (name) WHERE deleted = false
>
> Referenced by:
> TABLE "child" CONSTRAINT "child_fkey" FOREIGN KEY (parent_id) REFERENCES parent_table(id) ON DELETE CASCADE
>
> Triggers:
> parent_changed BEFORE INSERT OR DELETE OR UPDATE OR TRUNCATE ON parent_table FOR EACH STATEMENT EXECUTE FUNCTION parent_table_changed();
>
> This is the trigger function
>
> 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 table using the following SQL (note that I came across
> this issue while debugging an application-level performance
> problem and the SQL I'm using here is similar to what the
> application is generating):
>
> BEGIN;
> -- ALTER TABLE public.parent_table DISABLE TRIGGER parent_changed;
> PREPARE my_insert (varchar(64), boolean, varchar(255), boolean, boolean) AS INSERT INTO public.parent_table (name,enabled,description,deleted,is_default) VALUES($1, $2, $3, $4, $5);
> EXECUTE my_insert ($$035001$$, true, $$null$$, false, false);
> EXECUTE my_insert ($$035002$$, true, $$null$$, false, false);
> ....29998 more lines
>
>
> This is the execution time I get when running the script while the
> trigger is enabled:
>
> ~/tmp$ time psql -q -Upostgres -h dbhost -f inserts.sql test_db
>
> real 0m8,381s
> user 0m0,203s
> sys 0m0,287s
>
>
> Running the same SQL script with trigger disabled shows a ~4x
> speed-up:
>
>
> ~/tmp$ time psql -q -Upostgres -h dbhost -f inserts.sql test_db
>
> real 0m2,284s
> user 0m0,171s
> sys 0m0,261s
>
>
> Defining the trigger as "BEFORE INSERT" or "AFTER INSERT" made no
> difference.
>
> I then got curious , put a "/timing" at the start of the SQL
> script, massaged the psql output a bit and plotted a chart of the
> statement execution times.
> To my surprise, I see a linear increase of the per-INSERT
> execution times, roughly 4x as well:
>
> While the execution time per INSERT remains constant when
> disabling the trigger before inserting the data:
>
> What's causing this slow-down ?
>
> Thanks,
> Tobias
>
Attachments:
[image/png] pdojghkhhbimldhf.png (115.5K, ../009d281a-9d45-fd66-5c81-967b2b072792@code-sourcery.de/3-pdojghkhhbimldhf.png)
download | view image
[image/png] nnacmfeefdgmdnbl.png (65.5K, ../009d281a-9d45-fd66-5c81-967b2b072792@code-sourcery.de/4-nnacmfeefdgmdnbl.png)
download | view image
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, pgsql-performance@lists.postgresql.org
Subject: Re: Linear slow-down while inserting into a table with an ON INSERT trigger ?
In-Reply-To: <009d281a-9d45-fd66-5c81-967b2b072792@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