From: Andreas Joseph Krogh <andreas@visena.com>
To: pgsql-performance@postgresql.org
Subject: Re: Optimize query for listing un-read messages
Date: Fri, 2 May 2014 02:10:05 +0200 (CEST)
Message-ID: <OfficeNetEmail.3a.2970a72aeae261a2.145ba3fc1d3@prod2> (raw)
In-Reply-To: <CAKFQuwaB0_HQ6A2Gj9BXhButJxdj1g4=RcehdQ4vAroxk6PLWg@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
På fredag 02. mai 2014 kl. 01:58:04, skrev David G Johnston <
david.g.johnston@gmail.com <mailto: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 andreas@visena.com
<mailto:andreas@visena.com> www.visena.com <https://www.visena.com;
<https://www.visena.com;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: andreas@visena.com
Subject: Re: Optimize query for listing un-read messages
In-Reply-To: <OfficeNetEmail.3a.2970a72aeae261a2.145ba3fc1d3@prod2>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox