På torsdag 01. mai 2014 kl. 23:19:55, skrev David G Johnston < david.g.johns...@gmail.com <mailto:david.g.johns...@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; 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 andr...@visena.com <mailto:andr...@visena.com> www.visena.com <https://www.visena.com> <https://www.visena.com>
