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) ????
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? |