From: Andreas Joseph Krogh <andreas@visena.com>
To: pgsql-performance@postgresql.org
Subject: Re: Optimize query for listing un-read messages
Date: Thu, 1 May 2014 23:31:33 +0200 (CEST)
Message-ID: <OfficeNetEmail.2c.b3fcf8302c09ac72.145b9b2636a@prod2> (raw)
In-Reply-To: <1398979195531-5802157.post@n5.nabble.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
På torsdag 01. mai 2014 kl. 23:19:55, skrev David G Johnston <
david.g.johnston@gmail.com <mailto: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 andreas@visena.com <mailto:andreas@visena.com> www.visena.com
<https://www.visena.com; <https://www.visena.com;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: andreas@visena.com
Subject: Re: Optimize query for listing un-read messages
In-Reply-To: <OfficeNetEmail.2c.b3fcf8302c09ac72.145b9b2636a@prod2>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox