Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Wh0l9-0005Ly-R7 for pgsql-sql@arkaria.postgresql.org; Sun, 04 May 2014 18:03:32 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1Wh0l8-0003j7-O5 for pgsql-sql@arkaria.postgresql.org; Sun, 04 May 2014 18:03:30 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Wh0l7-0003j0-Iu for pgsql-sql@postgresql.org; Sun, 04 May 2014 18:03:29 +0000 Received: from prod2.officenet.no ([195.159.87.246]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Wh0l3-0001nO-Ht for pgsql-sql@postgresql.org; Sun, 04 May 2014 18:03:28 +0000 Received: from localhost ([127.0.0.1] helo=prod2) by prod2.officenet.no with esmtp (Exim 4.76) (envelope-from ) id 1Wh0l2-0002pg-3a; Sun, 04 May 2014 20:03:24 +0200 Date: Sun, 4 May 2014 20:03:23 +0200 (CEST) From: Andreas Joseph Krogh To: =?UTF-8?Q?Brice_Andr=C3=A9?= Cc: pgsql-sql Message-ID: In-Reply-To: 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: -0.5 (/) Content-Type: multipart/related; boundary="----=_Part_16_985502753.1399226603653" List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org ------=_Part_15_274317578.1399226603651 Content-Type: multipart/related; boundary="----=_Part_16_985502753.1399226603653" ------=_Part_16_985502753.1399226603653 Content-Type: multipart/alternative; boundary="----=_Part_17_1120035700.1399226603671" ------=_Part_17_1120035700.1399226603671 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable P=C3=A5 s=C3=B8ndag 04. mai 2014 kl. 19:43:11, skrev Brice Andr=C3=A9 >: Forget my last answer : it was a stupid= =20 one... I tried to answer quickly, but with tiredness, it does not give good= =20 results. =C2=A0 For me, your problem of performance comes from the "WHERE NOT EXIST= S=20 (query)" because your query is executed on each result of the outer join. =C2=A0 I tried to figure out how you can avoid this with your current data= base=20 design, but I did not found any solution. Maybe someone on the forum will h= ave=20 an idea. =C2=A0 If not, what I can propose your is to arrange yourself so that, for= each=20 couple (message, user) of your database, you have a corresponding entry in= =20 message_property, so that the first solution I proposed you (with an inner= =20 join) will work. And with multi-column indexes, it should be fast. =C2=A0 To do so, you can use trigger mechanism on both the insertion of th= e=20 message to create all message_property entries of that message, and on user= =20 insertion to create all message_properties of the user, so that you do not = need=20 to change anything outside your SQL design. =C2=A0 The disadvantages of this solution are that the insertion of a new = message=20 or of a new message will be slower, and that your database size will be=20 greater, but it should solve the problem of fast determining all read or un= read=20 messages of a dedicated user. =C2=A0 Yes, the reason it cannot be fast is b= ecause PG=20 is unable to index the difference between two sets, so my schema, although = a=20 correct one, isn't index friendly so a caching-mechanism must be used for f= ast,=20 indexed access. The solution is to redesign and have an entry in=20 message_property for each combination of user/message. =C2=A0 -- Andreas Jo= speh Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 andreas@visena.com=20 www.visena.com =20 =C2=A0 ------=_Part_17_1120035700.1399226603671 Content-Type: text/html;charset=UTF-8 Content-Transfer-Encoding: quoted-printable
P=C3=A5 s=C3=B8ndag 04. mai 2014 kl. 19:43:11, skrev Brice Andr=C3=A9 = <brice@famil= le-andre.be>:
Forget my last answer : it was a stupid one... I tried to answer quick= ly, but with tiredness, it does not give good results.
=C2=A0
For me, your problem of performance comes from the "WHERE NOT EXISTS (= query)" because your query is executed on each result of the outer joi= n.
=C2=A0
I tried to figure out how you can avoid this with your current database des= ign, but I did not found any solution. Maybe someone on the forum will have= an idea.
=C2=A0
If not, what I can propose your is to arrange yourself so that, for each co= uple (message, user) of your database, you have a corresponding entry in me= ssage_property, so that the first solution I proposed you (with an inner jo= in) will work. And with multi-column indexes, it should be fast.
=C2=A0
To do so, you can use trigger mechanism on both the insertion of the messag= e to create all message_property entries of that message, and on user inser= tion to create all message_properties of the user, so that you do not need = to change anything outside your SQL design.
=C2=A0
The disadvantages of this solution are that the insertion of a new message = or of a new message will be slower, and that your database size will be gre= ater, but it should solve the problem of fast determining all read or unrea= d messages of a dedicated user.
=C2=A0
Yes, the reason it cannot be fast is because PG is unable to index the= difference between two sets, so my schema, although a correct one, isn't i= ndex friendly so a caching-mechanism must be used for fast, indexed access.= The solution is to redesign and have an entry in message_property for each= combination of user/message.
=C2=A0
--
Andrea= s Jospeh Krogh
CTO / Partner<= /span> - Visena AS
Mobile: +47 90= 9 56 963
3D""
=C2=A0
------=_Part_17_1120035700.1399226603671-- ------=_Part_16_985502753.1399226603653 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_16_985502753.1399226603653-- ------=_Part_15_274317578.1399226603651--