From: | Andreas Joseph Krogh <andreas(at)visena(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Optimize query for listing un-read messages |
Date: | 2014-05-01 19:17:36 |
Message-ID: | OfficeNetEmail.24.d354cf5bb64f2bec.145b935da71@prod2 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
På torsdag 01. mai 2014 kl. 20:35:07, skrev Jochem Berndsen <jochem(at)functor(dot)nl
<mailto:jochem(at)functor(dot)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(at)visena(dot)com <mailto:andreas(at)visena(dot)com> www.visena.com
<https://www.visena.com> <https://www.visena.com>
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2014-05-01 19:30:39 | Re: Optimize query for listing un-read messages |
Previous Message | Jochem Berndsen | 2014-05-01 18:35:07 | Re: Optimize query for listing un-read messages |