Re: Optimize query for listing un-read messages - Mailing list pgsql-sql
På søndag 04. mai 2014 kl. 14:06:35, skrev Brice André <brice@famille-andre.be>:
I would also ensure that an efficient index is used for the outer join. I would probably try at least a multi-column index on (message_id, person_id) for the property table. I would also maybe give a try to an index on (message_id, person_id, is_read), just to see if it improves performances.As the "LEFT OUTER JOIN" will put fields of non-existing right table to null, I would simply rewrite it :Dear Andreas,For me, putting both "LEFT OUTER JOIN" and "NOT EXISTS" is a bad idea.
SELECT ... FROM message m
LEFT OUTER JOIN message_property prop ON prop.message_id = m.id AND prop.person_id = 1
WHERE prop.is_read = TRUE
The problem is that your suggested query doesn't return the desired results as it effectively is an INNER JOIN because you have "WHERE prop.is_read=TRUE", defeating the whole purpose of a LEFT OUTER JOIN.
-- 
Andreas Jospeh Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963