Re: Optimize query for listing un-read messages - Mailing list pgsql-performance
På fredag 02. mai 2014 kl. 00:55:25, skrev David G Johnston <david.g.johnston@gmail.com>:
Andreas Joseph Krogh-2 wrote
> I will end up with that only if
> all users read all messages, which is not nearly the case.
These observations probably won't help but...
You have what amounts to a mathematical "spare matrix" problem on your
hands...
Is there any way to expire messages so that dimension does not grow
unbounded?
No, unfortunately...
Per-User caching does seem to be something that is going to be needed...
Depending on how many users are being tracked would storing the "reader_id"
in an indexed array improve matters? " SELECT ... FROM message WHERE NOT (1
= ANY(reader_ids)) ; UPDATE message SET reader_ids = reader_ids || 1 WHERE
messageid = ..." I'm not that familiar with how well indexes over arrays
work or which kind is needed (i.e. gin/gist).
"is_read" is one of many properties being tracked for a message...
-- 
Andreas Jospeh Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
Attachment
pgsql-performance by date: