Re: Optimize query for listing un-read messages

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?

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).

HTH

David J.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Optimize-query-for-listing-un-read-messages-tp5802097p5802170.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.