Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Wfzf5-0000vN-BG for pgsql-performance@arkaria.postgresql.org; Thu, 01 May 2014 22:41:03 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1Wfzf4-0006Bg-M8 for pgsql-performance@arkaria.postgresql.org; Thu, 01 May 2014 22:41:02 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Wfzf3-0006Ba-Jf for pgsql-performance@postgresql.org; Thu, 01 May 2014 22:41:01 +0000 Received: from prod2.officenet.no ([195.159.87.246]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Wfzez-0006XS-Ka for pgsql-performance@postgresql.org; Thu, 01 May 2014 22:41:01 +0000 Received: from localhost ([127.0.0.1] helo=prod2) by prod2.officenet.no with esmtp (Exim 4.76) (envelope-from ) id 1Wfzcv-0007Yd-Jy for pgsql-performance@postgresql.org; Fri, 02 May 2014 00:38:49 +0200 Date: Fri, 2 May 2014 00:38:49 +0200 (CEST) From: Andreas Joseph Krogh To: pgsql-performance@postgresql.org Message-ID: In-Reply-To: <5362CBFA.6060004@fuzzy.cz> Subject: Re: Optimize query for listing un-read messages MIME-Version: 1.0 X-Mailer: OfficeNet Mail 1.9.0-SNAPSHOT X-Pg-Spam-Score: -1.9 (-) Content-Type: multipart/related; boundary="----=_Part_61_1736923229.1398983929492" List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org ------=_Part_60_171063845.1398983929492 Content-Type: multipart/related; boundary="----=_Part_61_1736923229.1398983929492" ------=_Part_61_1736923229.1398983929492 Content-Type: multipart/alternative; boundary="----=_Part_62_786349998.1398983929514" ------=_Part_62_786349998.1398983929514 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable P=C3=A5 fredag 02. mai 2014 kl. 00:34:34, skrev Tomas Vondra >: On 1.5.2014 23:58, Andreas Joseph Krogh wrote: > P=C3=A5 torsdag 01. mai 2014 kl. 23:45:49, skrev Tomas Vondra >: > >=C2=A0 =C2=A0 =C2=A0On 1.5.2014 23:19, Andreas Joseph Krogh wrote: >=C2=A0 =C2=A0 =C2=A0> Just curious: >=C2=A0 =C2=A0 =C2=A0> Is such a JOIN index possible in other DBs, if so -= which? >=C2=A0 =C2=A0 =C2=A0> Can other DBs do index on difference between two se= ts? >=C2=A0 =C2=A0 =C2=A0> Will PG ever have this, is it even possible? > >=C2=A0 =C2=A0 =C2=A0I'm not aware of such database, but maybe it's possib= le at least for >=C2=A0 =C2=A0 =C2=A0some cases. But I'd expect that to significantly depe= nd on the schema. >=C2=A0 =C2=A0 =C2=A0And I'm not aware of any such effort in case of Postg= reSQL, do don't >=C2=A0 =C2=A0 =C2=A0hold your breath. > >=C2=A0 =C2=A0 =C2=A0IMHO the problem with your schema is that while each = 'read' message has >=C2=A0 =C2=A0 =C2=A0a matching row in message_property, 'undread' message= s may or may not >=C2=A0 =C2=A0 =C2=A0have a matching row. Is there a particular reason for= that? > >=C2=A0 >=C2=A0 > 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=3Dtrue in message_property. This table holds other properties to= o. > 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)? =C2=A0 =C2=A0 A message might have a rec= ipient and=20 might be read by others. =C2=A0 I understand you're trying to solve this wi= thout=20 storing a row for each possible message-person combination, but won't this eventually happen anyway (with is_read=3Dtrue for all rows)? =C2=A0 =C2=A0 I will end up wit= h that only if=20 all users read all messages, which is not nearly the case. =C2=A0 -- Andrea= s Jospeh=20 Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 andreas@visena.com= =20 www.visena.com =20 =C2=A0 ------=_Part_62_786349998.1398983929514 Content-Type: text/html;charset=UTF-8 Content-Transfer-Encoding: quoted-printable
P=C3=A5 fredag 02. mai 2014 kl. 00:34:34, skrev Tomas Vondra <tv@fuzzy.cz>:
On = 1.5.2014 23:58, Andreas Joseph Krogh wrote:
> P=C3=A5 torsdag 01. mai 2014 kl. 23:45:49, skrev Tomas Vondra <tv@f= uzzy.cz
> <mailto:tv@fuzzy.cz>>:
>
>=C2=A0 =C2=A0 =C2=A0On 1.5.2014 23:19, Andreas Joseph Krogh wrote:
>=C2=A0 =C2=A0 =C2=A0> Just curious:
>=C2=A0 =C2=A0 =C2=A0> Is such a JOIN index possible in other DBs, if= so - which?
>=C2=A0 =C2=A0 =C2=A0> Can other DBs do index on difference between t= wo sets?
>=C2=A0 =C2=A0 =C2=A0> Will PG ever have this, is it even possible? >
>=C2=A0 =C2=A0 =C2=A0I'm not aware of such database, but maybe it's poss= ible at least for
>=C2=A0 =C2=A0 =C2=A0some cases. But I'd expect that to significantly de= pend on the schema.
>=C2=A0 =C2=A0 =C2=A0And I'm not aware of any such effort in case of Pos= tgreSQL, do don't
>=C2=A0 =C2=A0 =C2=A0hold your breath.
>
>=C2=A0 =C2=A0 =C2=A0IMHO the problem with your schema is that while eac= h 'read' message has
>=C2=A0 =C2=A0 =C2=A0a matching row in message_property, 'undread' messa= ges may or may not
>=C2=A0 =C2=A0 =C2=A0have a matching row. Is there a particular reason f= or that?
>
>=C2=A0
>=C2=A0
> 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 wit= h
> is_read=3Dtrue 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 no= t
> necessarily have only one recipient, it might have one, many or none,<= br> > 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)?
=C2= =A0
=C2= =A0
A m= essage might have a recipient and might be read by others.
=C2= =A0
I u= nderstand 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=3Dtrue for all rows)?
=C2=A0
=C2=A0
I will end up with that only if all users read all messages, which is = not nearly the case.
=C2=A0
--
Andrea= s Jospeh Krogh
CTO / Partner<= /span> - Visena AS
Mobile: +47 90= 9 56 963
3D""
=C2=A0
------=_Part_62_786349998.1398983929514-- ------=_Part_61_1736923229.1398983929492 Content-Type: image/png Content-Transfer-Encoding: base64 Content-Disposition: inline Content-ID: iVBORw0KGgoAAAANSUhEUgAAAIUAAAAYCAYAAADUIj6hAAAABHNCSVQICAgIfAhkiAAABzBJREFU aEPtmNFxHDcMhmVP3i1VECpvnjzkVIHWFfhcgVcVRKrAUgWRK/C6Al8H3lTgy0PGbzFdQc4VJP/H ADs43q6kROeJNbOYgQACIAgCWJKng4MZ5gxUGXh0U0b+SIuF9G+EWXj2Q15vbrE/NPtk9uub7Gfd t5mBx1NhqSEo8HshjbEUtlO2yIM9tsx5dZP9rPt2MzDZFAqZE4LGcFjdsg3saQaHt7fY70X949On aS+OZidDBkavD33157L4xaw2os+Mp/DAC10l2XhOCeStj0W5ajrJL8WfCi80Xgf9vVlrhg9yRONe /f7xI2vNsIcM7JwU9o6oGyJrLT8JOA1aX9sKP4wl94bAniukEbo/n7YPmuTETzIab4Y9ZWCrKexd 8M58lxPCvvD6aihfvexbEQoPYO8NQRO0JnddGO6FJYaVsBde7cXj7KRk4LsqDxQzCYeGsJNgaXZe +JXkyGgWINq3Gp+bHNILz+wEYs5qH1eJrouNrpDXtk4O6x1IvtD40GWyJYYdkF0jIbYZlN16xygI gl9smbMDsmFdfAKb23xGB8H/mv3tOJfArs1kukk79DEPdQ5u0g1vCvvqKXIW8mZYW+F3Tg4r8HvZ kQCCLydK8EFMQCe5N8RgL9mRG0SqQFuNvdE6beSs0qPDBuCdg0+gvClso8SbTO4ki7mQzQqBrcMH QPwReg2wW8umEe/+L8T/LEzBGF9nXjzZ46s+ITHvhegW8LL39xm6App7KYL/GE+vcYnFbJiP/0aY hdiCnRC7jaj7eiK2ETInC5PRF6LYkSN0+HabF75WuT6syCyI0YkVGGMvUC33AmfZTDUEj8u6IVhu EhRUJ2U2g6UlugyNb03Hl9obHwlxJbcRzcYjO4QPjVfGFTQav4vrmp7cpMp2qbHnBxWJbisbho2Q XI6C1sIHDUFhH4Hij4UbIev66eANeiwbkA+LBsO36zAHzoXU7AhbqLAXEiOYTXcSdO8VS9L4wN8U BIYhBd6oSUgYMijOkecRuTdQa/YiBXhbXFcnCnI2SrfeBG9NydrLYMhGHa5qB9pQIxlzAE6Okjzx JO5afGe6kmhBicWKQNKuTZ5EW+MjQY8v/9rQ0bhJSJyNGUe/JH1t8h1iMbdSPAvxHYin6YmN9YBX QmTYZZNh14vHhhhal5vtcIrJjmvszPRJdExH3MWHvykoBEc9CoCGWJisOLOGoCORs1FvIMbYA8z3 kwM59oemy6LlWrLxFLmWgiQAfEGd8S+NssbK+EhyGLxUkhhiy717waBqHOJYSEacwBejkFNhjJNj v/gANOdQxPfciP/JVBCK2cOIcg3RRJ+CPrLPNVhhN6F38VKMF3XLlIJrjU5C8gMFxvKDvOcPc4rV NjCn7KM0BV+161V8viSCuJZ8SITGJGEh7IUUlxOFMYUH1kJOCN4Wh+I5pqCuK01k40kSNtnKiKIl UfxAgW5sU5JlS05rtt5YFJF1SWoqHv6BRgQcA4/bdb9WRjmMk3jyUMAbIoyJq9e4cVmgzKt9j5gN J/aYDhk+hhjExwaPcz5PObA5Zd9bvz5UzCTZuZDidu5AchpiKSwPR+TV1bCWqBQ9nCjJ5neivC82 Nr4LeS2j1gw5LUqwBuhGQQU5UwHeSslXkwIynz2U2A2yKDgG7OffwLA3TpGRpk0Tzpj3ZEJXi/GR a6GN0e0NtppChePdcAz1FTS+FN8Kpxoiykk+J8fC5tenjbu9kSqpHLu9jBrhUuhNwVGbpyZrzrl0 HPVD8SXj5EOOD/eDC64VjvYBZLuUbIVAfBN1t/C/SU+cAGtdGo+fVnzycUX5wmn6eCKPmWYJG2E/ ppTsuRCbvUD9fwquksG5GqLVKhzDQ3GrEyLKSXhsiK3T5j9EyxffCFOYi2wUlPyFFDQAhehESDjQ GoX0ho0oj8RPou6TxHJdcT0NTcWkO0AnG7+uXsnHqcas/72wvWF+mSf7N/Watp9kTXoluzeS0fB9 9CcZ/hvhSZTfh99pispZOXL9KrGrARkNUMu9ITbScZWs7xOYNt9pwxSZtYDsX/GE3xTkrXgwAsXm fud08FiTeC+m2/o7ppo+PTS/NBK5ARpDG5YHr++DpmVfreYdiX8mnp+DzPEG9WbqJON0JBenZofs sxBA1gj5NXGvfJu/Qh7HwQh/VDUEyUxCit5hH94QfKkEdu+GwK8BX0hvCF+D67xhjmXQCXMwJCZ+ olK0A1EKRCE4snsh42w8Mv/Zhxw9iD7Cjo7CyQC/KyF6oBfShL4PYgG+CHsYKyZxvxZSZBAgjhIz YDz+AbfD37GtbaoSKzgGt+lKfI/GZtay6vG4VXTpPsh+IcQhOk9I7WYeP5AM3HZ9+DaSGIp9HIuu huC4pCGGx+YD2fcc5tfIAA0h/Et4/jX8zz4fWAasIf4UbR9Y6HO4d8jAnd4U0Y8ageuCB+c+H5R3 CHU2mTMwZ+B/y8DfSMBLLOYXVuEAAAAASUVORK5CYII= ------=_Part_61_1736923229.1398983929492-- ------=_Part_60_171063845.1398983929492--