Re: bad COPY performance with NOTIFY in a trigger

On Fri, Feb 5, 2016 at 9:33 AM, Filip Rembiałkowski
<filip.rembialkowski@gmail.com> wrote:
> 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'

I'm not so sure that this is a great idea.  Generally, we tend to
discourage GUCs that control behavior at the SQL level.  Are you 100%
certain that there is no path to optimizing this case without changing
behvior?

merlin