Message-ID: <5362CBFA.6060004@fuzzy.cz>
Date: Fri, 02 May 2014 00:34:34 +0200
From: Tomas Vondra <tv@fuzzy.cz>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:24.0) Gecko/20100101 Thunderbird/24.5.0
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Optimize query for listing un-read messages
References: <OfficeNetEmail.2f.30dd17c640e2229.145b9c57087@prod2>
In-Reply-To: <OfficeNetEmail.2f.30dd17c640e2229.145b9c57087@prod2>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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)?

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)?

Tomas


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance