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 01:51:14 +0200 (CEST)
Message-ID: <OfficeNetEmail.35.9ac112fa7bf026f1.145ba309894@prod2> (raw)
In-Reply-To: <1398984925338-5802170.post@n5.nabble.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
På fredag 02. mai 2014 kl. 00:55:25, skrev David G Johnston <
david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>>: 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? No, unfortunately... 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... -- 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.35.9ac112fa7bf026f1.145ba309894@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