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. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
