Re: Optimize query for listing un-read messages

På lørdag 03. mai 2014 kl. 11:51:08, skrev Alban Hertroys <haramrae(at)gmail(dot)com
<mailto:haramrae(at)gmail(dot)com>>: On 01 May 2014, at 13:06, Andreas Joseph Krogh
<andreas(at)visena(dot)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 andreas(at)visena(dot)com
<mailto:andreas(at)visena(dot)com> www.visena.com <https://www.visena.com>
<https://www.visena.com>