Re: Optimize query for listing un-read messages

På torsdag 01. mai 2014 kl. 23:19:55, skrev David G Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com <mailto:david(dot)g(dot)johnston(at)gmail(dot)com>>: How does
something like:

WITH unreads AS (
SELECT messageid FROM message
EXCEPT
SELECT messageid FROM message_property WHERE personid=1 AND has_read
)
SELECT ...
FROM unreads
JOIN messages USING (messageid)
;

perform?   It actually performs worse.   The best query so far is:   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 coalesce(prop.is_read, false) = false;   Giving the plan:
                                                                                     
QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Merge Left Join  (cost=4.20..90300.76 rows=500000 width=40) (actual
time=445.021..445.025 rows=10 loops=1)
   Merge Cond: (m.id = prop.message_id)
   Filter: (NOT COALESCE(prop.is_read, false))
   Rows Removed by Filter: 999990
   ->  Index Scan using message_pkey on message m  (cost=0.42..34317.43
rows=1000000 width=35) (actual time=0.014..113.314 rows=1000000 loops=1)
   ->  Index Scan using message_property_message_id_person_id_key on
message_property prop  (cost=0.42..40983.40 rows=999995 width=9) (actual
time=0.018..115.019 rows=999995 loops=1)
         Index Cond: (person_id = 1)
 Total runtime: 445.076 ms
(8 rows)   -- 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>