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 21:17:36 +0200 (CEST)
Message-ID: <OfficeNetEmail.24.d354cf5bb64f2bec.145b935da71@prod2> (raw)
In-Reply-To: <536293DB.4010505@functor.nl>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
På torsdag 01. mai 2014 kl. 20:35:07, skrev Jochem Berndsen <jochem@functor.nl
<mailto:jochem@functor.nl>>:
Hi Andreas,
[New to this list, forgive my ignorance.]
[snip]
I'm getting better performance with:
SELECT
m.id AS message_id,
1 AS person_id,
FALSE AS is_read,
m.subject
FROM message m
WHERE 1 = 1
AND NOT EXISTS(SELECT
*
FROM message_property pr
WHERE pr.message_id = m.id AND pr.person_id = 1 AND pr.is_read);
You then lose the distinction between message_property with is_read =
FALSE, and nonexistent message_property for the message row.
If that is essential, I'm getting a roughly 2x speedup on my non-tuned
PostgreSQL with:
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 not coalesce(prop.is_read, false); Hi Jochem, Thansk for looking
at it. I'm still seing ~500ms being spent and I was hoping for a way to do this
using index so one could achieve 1-10ms, but maybe that's impossible given the
schema? Is there a way to design an equivalent schema to achieve <10ms
execution-time? -- 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.24.d354cf5bb64f2bec.145b935da71@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