From: =?utf-8?Q?Artur_Zaj=C4=85c?= <azajac@ang.com.pl>
To: "'Merlin Moncure'" <mmoncure@gmail.com>
Cc: <pgsql-performance@postgresql.org>
References: <002401cd8228$ce80e970$6b82bc50$@ang.com.pl>
	<CAHyXU0x5r+3zL6WYpHmuHhONatGYG3f49sdx+2z4Jjc2FhNBxQ@mail.gmail.com>
In-Reply-To: 
 <CAHyXU0x5r+3zL6WYpHmuHhONatGYG3f49sdx+2z4Jjc2FhNBxQ@mail.gmail.com>
Subject: Re: NOTIFY performance
Date: Fri, 24 Aug 2012 23:11:08 +0200
Message-ID: <002601cd823c$fbaba070$f302e150$@ang.com.pl>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Thread-Index: AQFUauSxAaTeqrMVLvYwr9ZuwqP4tAJ9mtt9mEexwmA=
Content-Language: pl

>> I would like to create some application using triggers and=20
>> LISTEN/NOTIFY framework. I've tested it, and I noticed that=20
>> performance of NOTIFY significally decreases with increasing number =
of=20
>> distinct NOTIFIES in transaction.
>> I found that function AsyncExistsPendingNotify is responsibe for it. =
I=20
>> think that complexivity of searching duplicates there is O(N^2). =
Would=20
>> be possible to improve performance of it? Maybe by using list for=20
>> elements precedence and binary search tree for searching duplicates - =

>> with complexivity of O(Nlog2(N)).
>>
>> I'v tested with 50000 of NOTICES. Updating table with 20000 NOTICES=20
>> when searching is not performed took 1,5 second. With searching it=20
>> took 28 seconds.
>
>I've confirmed the n^2 behavior on 9.2:
>postgres=3D# select pg_notify(v::text, null) from =
generate_series(1,10000) v;
>Time: 281.000 ms
>postgres=3D# select pg_notify(v::text, null) from =
generate_series(1,50000) v;
>Time: 7148.000 ms
>
>...but i'm curious if you're going about things the right =
way...typically I'd imagine you'd write out actionable items to a table =
and issue a much broader NOTIFY which taps listeners on the table to =
search the action queue.  Could you describe your problem in >a little =
more detail?

When there was only NOTIFY option with simple channel name there was no =
need to send so many messages - creating 50000 channels would be really =
stupid. NOTIFY to channel might only mean that there is sth new in table =
or sth similar. But with payload option it would be possible to make =
simple system for notify other database clients (or self notify - when =
changes are made by triggers) that some single record has changed and it =
should be invalidated in client cache. I would made (and I already made) =
that system (similar to streaming replication :) but more more simple), =
but unfortunately even not big update on table would kill my system with =
complexivity O(N^2). In general , I know that this system would be not =
efficient, but for my application it would simply solve my many =
problems.

-------------------------------------------
Artur Zajac