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 00:38:49 +0200 (CEST)
Message-ID: <OfficeNetEmail.31.d5ed6143e3791024.145b9eecf33@prod2> (raw)
In-Reply-To: <5362CBFA.6060004@fuzzy.cz>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
På fredag 02. mai 2014 kl. 00:34:34, skrev Tomas Vondra <tv@fuzzy.cz
<mailto:tv@fuzzy.cz>>: On 1.5.2014 23:58, Andreas Joseph Krogh wrote:
> På torsdag 01. mai 2014 kl. 23:45:49, skrev Tomas Vondra <tv@fuzzy.cz
> <mailto:tv@fuzzy.cz>>:
>
> On 1.5.2014 23:19, Andreas Joseph Krogh wrote:
> > Just curious:
> > Is such a JOIN index possible in other DBs, if so - which?
> > Can other DBs do index on difference between two sets?
> > Will PG ever have this, is it even possible?
>
> I'm not aware of such database, but maybe it's possible at least for
> some cases. But I'd expect that to significantly depend on the schema.
> And I'm not aware of any such effort in case of PostgreSQL, do don't
> hold your breath.
>
> IMHO the problem with your schema is that while each 'read' message has
> a matching row in message_property, 'undread' messages may or may not
> have a matching row. Is there a particular reason for that?
>
>
>
> Yes. The point is that maintaining a message_property pair for all
> messages for all users in the system imposes quite a
> maintainance-headache. As the schema is now any new message is per
> definition un-read, and when a user reads it then it gets an entry with
> is_read=true in message_property. This table holds other properties too.
> This way I'm avoiding having to book-keep so much when a new message
> arrives and when a new user is created. A message in my system does not
> necessarily have only one recipient, it might have one, many or none,
> and might be visible to many.
So how do you determine who's the recipient of a message? Or is that the
case that everyone can read everything (which is why you're displaying
them the unread messages, right)? A message might have a recipient and
might be read by others. I understand you're trying to solve this without
storing a row for each
possible message-person combination, but won't this eventually happen
anyway (with is_read=true for all rows)? I will end up with that only if
all users read all messages, which is not nearly the case. -- 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.31.d5ed6143e3791024.145b9eecf33@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