Re: Optimize query for listing un-read messages - Mailing list pgsql-performance
På torsdag 01. mai 2014 kl. 23:19:55, skrev David G Johnston <david.g.johnston@gmail.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;
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)
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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
Attachment
pgsql-performance by date: