From: | Andreas Joseph Krogh <andreas(at)visena(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Optimize query for listing un-read messages |
Date: | 2014-05-02 00:10:05 |
Message-ID: | OfficeNetEmail.3a.2970a72aeae261a2.145ba3fc1d3@prod2 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
På fredag 02. mai 2014 kl. 01:58:04, skrev David G Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com <mailto:david(dot)g(dot)johnston(at)gmail(dot)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 andreas(at)visena(dot)com
<mailto:andreas(at)visena(dot)com> www.visena.com <https://www.visena.com>
<https://www.visena.com>
From | Date | Subject | |
---|---|---|---|
Next Message | Craig James | 2014-05-02 00:17:58 | Re: Optimize query for listing un-read messages |
Previous Message | David G Johnston | 2014-05-01 23:58:04 | Re: Optimize query for listing un-read messages |