Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-performance-owner+M51647=pgsql-performance-ar@postgresql.org>)
 id 1Wg0l9-0003eT-2j
 for pgsql-performance@arkaria.postgresql.org; Thu, 01 May 2014 23:51:23 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.80) (envelope-from
 <pgsql-performance-owner+M51647=pgsql-performance-ar@postgresql.org>)
 id 1Wg0l8-0004ec-IF
 for pgsql-performance@arkaria.postgresql.org; Thu, 01 May 2014 23:51:22 +0000
Received: from makus.postgresql.org ([2001:4800:7903:4::125])
 by malur.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <andreas@visena.com>) id 1Wg0l7-0004eV-5b
 for pgsql-performance@postgresql.org; Thu, 01 May 2014 23:51:21 +0000
Received: from prod2.officenet.no ([195.159.87.246])
 by makus.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <andreas@visena.com>) id 1Wg0l3-0007lg-Db
 for pgsql-performance@postgresql.org; Thu, 01 May 2014 23:51:20 +0000
Received: from localhost ([127.0.0.1] helo=prod2)
 by prod2.officenet.no with esmtp (Exim 4.76)
 (envelope-from <andreas@visena.com>) id 1Wg0l0-0000dP-VG
 for pgsql-performance@postgresql.org; Fri, 02 May 2014 01:51:15 +0200
Date: Fri, 2 May 2014 01:51:14 +0200 (CEST)
From: Andreas Joseph Krogh <andreas@visena.com>
To: pgsql-performance@postgresql.org
Message-ID: <OfficeNetEmail.35.9ac112fa7bf026f1.145ba309894@prod2>
In-Reply-To: <1398984925338-5802170.post@n5.nabble.com>
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.4 (+)
Content-Type: multipart/related;
 boundary="----=_Part_71_1084665638.1398988274862"
List-Archive: <http://archives.postgresql.org/pgsql-performance>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-performance.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@postgresql.org>
List-Post: <mailto:pgsql-performance@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-performance>
List-Unsubscribe: 
 <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
X-Mailing-List: pgsql-performance
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

------=_Part_70_1888168651.1398988274862
Content-Type: multipart/related; 
	boundary="----=_Part_71_1084665638.1398988274862"

------=_Part_71_1084665638.1398988274862
Content-Type: multipart/alternative;
 boundary="----=_Part_72_2103346646.1398988274879"

------=_Part_72_2103346646.1398988274879
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

P=C3=A5 fredag 02. mai 2014 kl. 00:55:25, skrev David G Johnston <
david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>>: Andreas Jo=
seph=20
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? =C2=A0 =C2=A0 No, unfortunately... =C2=A0 Per-User caching does=
 seem to be=20
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?=C2=A0 " SELECT ... FROM message WHERE=
 NOT (1
 =3D ANY(reader_ids)) ; UPDATE message SET reader_ids =3D reader_ids || 1 W=
HERE
 messageid =3D ..."=C2=A0 I'm not that familiar with how well indexes over =
arrays
 work or which kind is needed (i.e. gin/gist). =C2=A0 =C2=A0 "is_read" is o=
ne of many=20
properties being tracked for a message... =C2=A0 -- Andreas Jospeh Krogh CT=
O /=20
Partner - Visena AS Mobile: +47 909 56 963 andreas@visena.com=20
<mailto:andreas@visena.com> www.visena.com <https://www.visena.com> =20
<https://www.visena.com> =C2=A0
------=_Part_72_2103346646.1398988274879
Content-Type: text/html;charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<div>P=C3=A5 fredag 02. mai 2014 kl. 00:55:25, skrev David G Johnston &lt;<=
a href=3D"mailto:david.g.johnston@gmail.com" target=3D"_blank">david.g.john=
ston@gmail.com</a>&gt;:</div>

<blockquote style=3D"border-left: 1px solid rgb(204, 204, 204); margin: 0pt=
 0pt 0pt 0.8ex; padding-left: 1ex;">
<div style=3D"display:inline; font-family: monospace; font-size: 12px;">And=
reas Joseph Krogh-2 wrote<br>
&gt; I will end up with that only if<br>
&gt; all users read all messages, which is not nearly the case.<br>
<br>
These observations probably won't help but...<br>
<br>
You have what amounts to a mathematical &quot;spare matrix&quot; problem on=
 your<br>
hands...<br>
<br>
Is there any way to expire messages so that dimension does not grow<br>
unbounded?</div>
</blockquote>

<div style=3D"display:inline; font-family: monospace; font-size: 12px;">=C2=
=A0</div>

<div style=3D"display:inline; font-family: monospace; font-size: 12px;">=C2=
=A0</div>

<div style=3D"display:inline; font-family: monospace; font-size: 12px;">No,=
 unfortunately...</div>

<div style=3D"display:inline; font-family: monospace; font-size: 12px;">=C2=
=A0</div>

<blockquote style=3D"border-left: 1px solid rgb(204, 204, 204); margin: 0pt=
 0pt 0pt 0.8ex; padding-left: 1ex;">
<div style=3D"display:inline; font-family: monospace; font-size: 12px;">Per=
-User caching does seem to be something that is going to be needed...<br>
<br>
Depending on how many users are being tracked would storing the &quot;reade=
r_id&quot;<br>
in an indexed array improve matters?=C2=A0 &quot; SELECT ... FROM message W=
HERE NOT (1<br>
=3D ANY(reader_ids)) ; UPDATE message SET reader_ids =3D reader_ids || 1 WH=
ERE<br>
messageid =3D ...&quot;=C2=A0 I'm not that familiar with how well indexes o=
ver arrays<br>
work or which kind is needed (i.e. gin/gist).</div>
</blockquote>

<div>=C2=A0</div>

<div>=C2=A0</div>

<div>&quot;is_read&quot; is one of many properties being tracked for a mess=
age...</div>

<div>=C2=A0</div>

<div class=3D"origo-email-signature">
<div>--
<div><span style=3D"font-family:arial,helvetica,sans-serif;"><strong>Andrea=
s Jospeh Krogh</strong></span></div>

<div><span style=3D"font-family:arial,helvetica,sans-serif;">CTO / Partner<=
/span> - Visena AS</div>

<div><span style=3D"font-family:arial,helvetica,sans-serif;">Mobile: +47 90=
9 56 963</span></div>

<div><a href=3D"mailto:andreas@visena.com" target=3D"_blank">andreas@visena=
.com</a></div>

<div><a href=3D"https://www.visena.com" target=3D"_blank">www.visena.com</a=
></div>

<div style=3D"padding-top: 5px"><a href=3D"https://www.visena.com" target=
=3D"_blank"><img alt=3D"" src=3D"cid:part_F869716306648Q3RB0O"></a></div>
</div>
</div>

<div>=C2=A0</div>
------=_Part_72_2103346646.1398988274879--

------=_Part_71_1084665638.1398988274862
Content-Type: image/png
Content-Transfer-Encoding: base64
Content-Disposition: inline
Content-ID: <part_F869716306648Q3RB0O>

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_71_1084665638.1398988274862--

------=_Part_70_1888168651.1398988274862--