Re: Optimize query for listing un-read messages - Mailing list pgsql-performance

From Andreas Joseph Krogh
Subject Re: Optimize query for listing un-read messages
Date
Msg-id OfficeNetEmail.2c.b3fcf8302c09ac72.145b9b2636a@prod2
Whole thread Raw
In response to Re: Optimize query for listing un-read messages  (David G Johnston <david.g.johnston@gmail.com>)
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;
 
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
 
Attachment

pgsql-performance by date:

Previous
From: David G Johnston
Date:
Subject: Re: Optimize query for listing un-read messages
Next
From: Josh Berkus
Date:
Subject: Re: Revisiting disk layout on ZFS systems