Hello
2014-05-01 21:17 GMT+02:00 Andreas Joseph Krogh <andr...@visena.com>: > På torsdag 01. mai 2014 kl. 20:35:07, skrev Jochem Berndsen < > joc...@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? > 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 > andr...@visena.com > www.visena.com > <https://www.visena.com> > >
