Home > mailing lists

bad COPY performance with NOTIFY in a trigger - Mailing list pgsql-performance

From	Filip Rembiałkowski
Subject	bad COPY performance with NOTIFY in a trigger
Date	February 5, 2016 00:13:02
Msg-id	CAP_rwwmpzk9=SbjRZTOd05bDctyC43wNKnu_m37dYGvL4SAeSw@mail.gmail.com Whole thread Raw
Responses	Re: bad COPY performance with NOTIFY in a trigger
List	pgsql-performance

Tree view

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

pgsql-performance by date:

From: Jordi
Date: 04 February 2016, 20:20:51
Subject: Re: Bitmap and-ing between btree and gin?

From: Tom Lane
Date: 05 February 2016, 01:41:17
Subject: Re: bad COPY performance with NOTIFY in a trigger

bad COPY performance with NOTIFY in a trigger - Mailing list pgsql-performance

Previous

Next