Re: Optimize query for listing un-read messages

On 01 May 2014, at 13:06, Andreas Joseph Krogh <andreas@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
theopposite 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
entryin 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
;


Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.