Re: Optimize query for listing un-read messages

Hello

2014-05-01 21:17 GMT+02:00 Andreas Joseph Krogh <andreas(at)visena(dot)com>:

> På torsdag 01. mai 2014 kl. 20:35:07, skrev Jochem Berndsen <
> 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?
>

I had a perfect success on similar use case with descent ordered partial
index

http://www.postgresql.org/docs/9.3/interactive/sql-createindex.html

Regards

Pavel

>
> --
> *Andreas Jospeh Krogh*
> CTO / Partner - Visena AS
> Mobile: +47 909 56 963
> andreas(at)visena(dot)com
> www.visena.com
> <https://www.visena.com>
>
>