Re: bad COPY performance with NOTIFY in a trigger

patch submitted on -hackers list.
http://www.postgresql.org/message-id/CAP_rwwn2z0gPOn8GuQ3qDVS5+HgEcG2EzEOyiJtcA=vpDEhoCg@mail.gmail.com

results after the patch:

trigger= BEGIN RETURN NULL; END
rows=40000
228ms COPY test.tab FROM '/tmp/test.dat'
205ms COPY test.tab FROM '/tmp/test.dat'
rows=80000
494ms COPY test.tab FROM '/tmp/test.dat'
395ms COPY test.tab FROM '/tmp/test.dat'
rows=120000
678ms COPY test.tab FROM '/tmp/test.dat'
652ms COPY test.tab FROM '/tmp/test.dat'
rows=160000
956ms COPY test.tab FROM '/tmp/test.dat'
822ms COPY test.tab FROM '/tmp/test.dat'
rows=200000
1184ms COPY test.tab FROM '/tmp/test.dat'
1072ms COPY test.tab FROM '/tmp/test.dat'
trigger= BEGIN PERFORM pg_notify('test',NEW.id::text); RETURN NULL; END
rows=40000
440ms COPY test.tab FROM '/tmp/test.dat'
406ms COPY test.tab FROM '/tmp/test.dat'
rows=80000
887ms COPY test.tab FROM '/tmp/test.dat'
769ms COPY test.tab FROM '/tmp/test.dat'
rows=120000
1346ms COPY test.tab FROM '/tmp/test.dat'
1171ms COPY test.tab FROM '/tmp/test.dat'
rows=160000
1710ms COPY test.tab FROM '/tmp/test.dat'
1709ms COPY test.tab FROM '/tmp/test.dat'
rows=200000
2189ms COPY test.tab FROM '/tmp/test.dat'
2206ms COPY test.tab FROM '/tmp/test.dat'

On Fri, Feb 5, 2016 at 1:45 PM, Filip Rembiałkowski <
filip(dot)rembialkowski(at)gmail(dot)com> wrote:

> On Thu, Feb 4, 2016 at 11:41 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> =?UTF-8?Q?Filip_Rembia=C5=82kowski?= <filip(dot)rembialkowski(at)gmail(dot)com>
>> writes:
>> > 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.
>>
>> No surprise, see AsyncExistsPendingNotify. You would have a lot of other
>> performance issues with sending hundreds of thousands of distinct notify
>> events from one transaction anyway, so I can't get terribly excited about
>> this.
>>
>
>
> What kind of issues? Do you mean, problems in postgres or problems in
> client?
>
> Is there an additional non-linear cost on COMMIT (extra to the cost I
> already showed)?
>
> The 8GB internal queue (referenced in a Note at
> http://www.postgresql.org/docs/current/static/sql-notify.html) should be
> able to keep ~ 1E8 such notifications (assumed one notification will fit in
> 80 bytes).
>
> On client side, this seems legit - the LISTENer deamon will collect these
> notifications and process them in line.
> There might be no LISTENer running at all.
>
> Still, the main problem I get with this approach is quadratic cost on big
> insert transactions.
> I wonder if this behavior is possible to change in future postgres
> versions. And how much programming work does it require.
>
> Is duplicate-elimination a fundamental, non-negotiable requirement?
>
>
>
> Thank you,
> Filip
>
>