On 1.5.2014 23:58, Andreas Joseph Krogh wrote: > På torsdag 01. mai 2014 kl. 23:45:49, skrev Tomas Vondra <tv@xxxxxxxx > <mailto:tv@xxxxxxxx>>: > > 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
