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.24.d354cf5bb64f2bec.145b935da71@prod2
Whole thread Raw
In response to Re: Optimize query for listing un-read messages  (Jochem Berndsen <jochem@functor.nl>)
Responses Re: Optimize query for listing un-read messages  (Pavel Stehule <pavel.stehule@gmail.com>)
List pgsql-performance
På torsdag 01. mai 2014 kl. 20:35:07, skrev Jochem Berndsen <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
 
Attachment

pgsql-performance by date:

Previous
From: Jochem Berndsen
Date:
Subject: Re: Optimize query for listing un-read messages
Next
From: Pavel Stehule
Date:
Subject: Re: Optimize query for listing un-read messages