Re: NOTIFY performance

>> I would like to create some application using triggers and
>> LISTEN/NOTIFY framework. I've tested it, and I noticed that
>> performance of NOTIFY significally decreases with increasing number of
>> distinct NOTIFIES in transaction.
>> I found that function AsyncExistsPendingNotify is responsibe for it. I
>> think that complexivity of searching duplicates there is O(N^2). Would
>> be possible to improve performance of it? Maybe by using list for
>> 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
>> when searching is not performed took 1,5 second. With searching it
>> took 28 seconds.
>
>I've confirmed the n^2 behavior on 9.2:
>postgres=# select pg_notify(v::text, null) from generate_series(1,10000) v;
>Time: 281.000 ms
>postgres=# 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
toa table and issue a much broader NOTIFY which taps listeners on the table to search the action queue.  Could you
describeyour 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
channelswould be really stupid. NOTIFY to channel might only mean that there is sth new in table or sth similar. But
withpayload option it would be possible to make simple system for notify other database clients (or self notify - when
changesare 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
notbig 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