[PERFORM] bad COPY performance with NOTIFY in a trigger

[Filip Rembiałkowski]

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