bad COPY performance with NOTIFY in a trigger

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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










[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux