På lørdag 03. mai 2014 kl. 11:51:08, skrev Alban Hertroys <haram...@gmail.com <mailto:haram...@gmail.com>>: On 01 May 2014, at 13:06, Andreas Joseph Krogh <andr...@visena.com> wrote:
> I have the following query to list all un-read messages for person with id=1: > > SELECT > m.id AS message_id, > prop.person_id, > coalesce(prop.is_read, FALSE) AS is_read, > m.subject > FROM message m > LEFT OUTER JOIN message_property prop ON prop.message_id = m.id AND prop.person_id = 1 > WHERE 1 = 1 > AND NOT EXISTS(SELECT > * > FROM message_property pr > WHERE pr.message_id = m.id AND pr.person_id = prop.person_id AND prop.is_read = TRUE) > ; Since most messages will have prop.is_read = TRUE, that part of the query suffers from low selectivity. Querying for the opposite is probably much faster, which you may even be able to speed up more with a partial index on is_read = FALSE. > Does anyone have suggestions on how to optimize the query or schema? It's important that any message not having an entry in message_property for a user is considered un-read. Do you really need to query message_property twice? I would think this would give the same results: SELECT m.id AS message_id, prop.person_id, coalesce(prop.is_read, FALSE) AS is_read, m.subject FROM message m LEFT OUTER JOIN message_property prop ON prop.message_id = m.id AND prop.person_id = 1 AND prop.is_read = FALSE ; That query doesn't produce the same reesult. -- Andreas Jospeh Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 andr...@visena.com <mailto:andr...@visena.com> www.visena.com <https://www.visena.com> <https://www.visena.com>
