Postgres Pro
Downloads
Home   >   mailing lists

Re: Optimize query for listing un-read messages - Mailing list pgsql-sql

From Brice André
Subject Re: Optimize query for listing un-read messages
Date
Msg-id CAOBG12ksaHi990-X3XOKzU6E3ne9kFP9L89n2Dg2+8A9yjxy4g@mail.gmail.com
Whole thread Raw
In response to Re: Optimize query for listing un-read messages  (Andreas Joseph Krogh <andreas@visena.com>)
Responses Re: Optimize query for listing un-read messages  (Andreas Joseph Krogh <andreas@visena.com>)
List pgsql-sql
Tree view
Forget my last answer : it was a stupid one... I tried to answer quickly, but with tiredness, it does not give good results.

For me, your problem of performance comes from the "WHERE NOT EXISTS (query)" because your query is executed on each result of the outer join.

I tried to figure out how you can avoid this with your current database design, but I did not found any solution. Maybe someone on the forum will have an idea.

If not, what I can propose your is to arrange yourself so that, for each couple (message, user) of your database, you have a corresponding entry in message_property, so that the first solution I proposed you (with an inner join) will work. And with multi-column indexes, it should be fast.

To do so, you can use trigger mechanism on both the insertion of the message to create all message_property entries of that message, and on user insertion to create all message_properties of the user, so that you do not need to change anything outside your SQL design.

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 greater, but it should solve the problem of fast determining all read or unread messages of a dedicated user.

Regards,
Brice


2014-05-04 18:53 GMT+02:00 Andreas Joseph Krogh <andreas@visena.com>:
På søndag 04. mai 2014 kl. 18:49:43, skrev Brice André <brice@famille-andre.be>:
Yes, I was a bit too fast. but replace it with
 
WHERE NOT prop.is_read = TRUE
 
and it should be OK.
 
No, that also will be treated as an INNER JOIN, because it kills tuples where prop is null. I need entries where prop IS NULL (hence the LEFT OUTER JOIN) because messages without an entry in message_property must be treated as unread, the same as messages with an entry in message_property where is_read=FALSE.
 
--
Andreas Jospeh Krogh
CTO / Partner - Visena AS
 

Attachment

pgsql-sql by date:

Previous
From: Andreas Joseph Krogh
Date:
Subject: Re: Optimize query for listing un-read messages
Next
From: Andreas Joseph Krogh
Date:
Subject: Re: Optimize query for listing un-read messages