På lørdag 03. mai 2014 kl. 11:51:08, skrev Alban Hertroys <haramrae@xxxxxxxxx>:
On 01 May 2014, at 13:06, Andreas Joseph Krogh <andreas@xxxxxxxxxx> 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
