bad COPY performance with NOTIFY in a trigger

From: Filip Rembiałkowski <filip(dot)rembialkowski(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: bad COPY performance with NOTIFY in a trigger
Date: 2016-02-04 21:12:39
Message-ID: CAP_rwwmpzk9=SbjRZTOd05bDctyC43wNKnu_m37dYGvL4SAeSw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hi.

A table has a trigger.
The trigger sends a NOTIFY.

Test with COPY FROM shows non-linear correlation between number of inserted
rows and COPY duration.

Table "test.tab"
Column | Type | Modifiers
---------+---------+-------------------------------------------------------
id | integer | not null default nextval('test.tab_id_seq'::regclass)
payload | text |
Indexes:
"tab_pkey" PRIMARY KEY, btree (id)
Triggers:
trg AFTER INSERT ON test.tab FOR EACH ROW EXECUTE PROCEDURE test.fun()

Test Series 1. Trigger code:
BEGIN RETURN NULL; END
You can see linear scaling.

rows=40000
191ms COPY test.tab FROM '/tmp/test.dat'
201ms COPY test.tab FROM '/tmp/test.dat'
rows=80000
426ms COPY test.tab FROM '/tmp/test.dat'
415ms COPY test.tab FROM '/tmp/test.dat'
rows=120000
634ms COPY test.tab FROM '/tmp/test.dat'
616ms COPY test.tab FROM '/tmp/test.dat'
rows=160000
843ms COPY test.tab FROM '/tmp/test.dat'
834ms COPY test.tab FROM '/tmp/test.dat'
rows=200000
1101ms COPY test.tab FROM '/tmp/test.dat'
1094ms COPY test.tab FROM '/tmp/test.dat'

Test Series 2. Trigger code:
BEGIN PERFORM pg_notify('test',NEW.id::text); RETURN NULL;
You can see non-linear scaling.

rows=40000
9767ms COPY test.tab FROM '/tmp/test.dat'
8901ms COPY test.tab FROM '/tmp/test.dat'
rows=80000
37409ms COPY test.tab FROM '/tmp/test.dat'
38015ms COPY test.tab FROM '/tmp/test.dat'
rows=120000
90227ms COPY test.tab FROM '/tmp/test.dat'
87838ms COPY test.tab FROM '/tmp/test.dat'
rows=160000
160080ms COPY test.tab FROM '/tmp/test.dat'
159801ms COPY test.tab FROM '/tmp/test.dat'
rows=200000
247330ms COPY test.tab FROM '/tmp/test.dat'
251191ms COPY test.tab FROM '/tmp/test.dat'

O(N^2) ????

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2016-02-04 22:41:13 Re: bad COPY performance with NOTIFY in a trigger
Previous Message Jordi 2016-02-04 17:19:31 Re: Bitmap and-ing between btree and gin?