På søndag 04. mai 2014 kl. 19:43:11, skrev Brice André <brice(at)famille-andre(dot)be
<mailto:brice(at)famille-andre(dot)be>>: 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. 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 index 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. -- Andreas Jospeh Krogh
CTO / Partner - Visena AS Mobile: +47 909 56 963 andreas(at)visena(dot)com
<mailto:andreas(at)visena(dot)com> www.visena.com <https://www.visena.com>
<https://www.visena.com>