Re: Optimize query for listing un-read messages - Mailing list pgsql-performance

From Andreas Joseph Krogh
Subject Re: Optimize query for listing un-read messages
Date
Msg-id OfficeNetEmail.3a.2970a72aeae261a2.145ba3fc1d3@prod2
Whole thread Raw
In response to Re: Optimize query for listing un-read messages  (David G Johnston <david.g.johnston@gmail.com>)
List pgsql-performance
På fredag 02. mai 2014 kl. 01:58:04, skrev David G Johnston <david.g.johnston@gmail.com>:

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...
 
 
​But you don't have to have all of them on the same table.  Once you've identified the messages in question performing a standard join onto a supplemental detail table should be fairly straight-forward.
 
Do these other properties have values when "is_read" is false or only when "is_read" is true?  Since you already allow for the possibility of a missing record (giving it the meaning of "not read")​ these other properties cannot currently exist in that situation.
 
 
A message might hold a property (ie. is_important) when is_read is FALSE (it might be set back to is_read=FALSE after being read the first time).
 
--
Andreas Jospeh Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
 
Attachment

pgsql-performance by date:

Previous
From: David G Johnston
Date:
Subject: Re: Optimize query for listing un-read messages
Next
From: Craig James
Date:
Subject: Re: Optimize query for listing un-read messages