Re: [PERFORM] Optimize query for listing un-read messages

2014-05-01 Thread Craig James
On Thu, May 1, 2014 at 4:26 AM, Andreas Joseph Krogh wrote: > I have a schema where I have lots of messages and some users who might > have read some of them. When a message is read by a user I create an entry > i a table message_property holding the property (is_read) for that user. > > The schem

Re: [PERFORM] Optimize query for listing un-read messages

2014-05-01 Thread Andreas Joseph Krogh
På fredag 02. mai 2014 kl. 01:58:04, skrev David G Johnston < david.g.johns...@gmail.com >: Per-User caching does seem to be something that is going to be needed... Depending on how many users are being tracked would storing the "reader_id" in an indexed arra

Re: [PERFORM] Optimize query for listing un-read messages

2014-05-01 Thread David G Johnston
> > > Per-User caching does seem to be something that is going to be needed... > > Depending on how many users are being tracked would storing the "reader_id" > in an indexed array improve matters? " SELECT ... FROM message WHERE NOT > (1 > = ANY(reader_ids)) ; UPDATE message SET reader_ids = read

Re: [PERFORM] Optimize query for listing un-read messages

2014-05-01 Thread Andreas Joseph Krogh
På fredag 02. mai 2014 kl. 00:55:25, skrev David G Johnston < david.g.johns...@gmail.com >: Andreas Joseph Krogh-2 wrote > I will end up with that only if > all users read all messages, which is not nearly the case. These observations probably won't help but.

Re: [PERFORM] Optimize query for listing un-read messages

2014-05-01 Thread David G Johnston
Andreas Joseph Krogh-2 wrote > I will end up with that only if > all users read all messages, which is not nearly the case. These observations probably won't help but... You have what amounts to a mathematical "spare matrix" problem on your hands... Is there any way to expire messages so that d

Re: [PERFORM] Optimize query for listing un-read messages

2014-05-01 Thread Andreas Joseph Krogh
På fredag 02. mai 2014 kl. 00:34:34, skrev Tomas Vondra mailto:t...@fuzzy.cz>>: On 1.5.2014 23:58, Andreas Joseph Krogh wrote: > På torsdag 01. mai 2014 kl. 23:45:49, skrev Tomas Vondra >: > >     On 1.5.2014 23:19, Andreas Joseph Krogh wrote: >     > Just curious: >   

Re: [PERFORM] Optimize query for listing un-read messages

2014-05-01 Thread Tomas Vondra
On 1.5.2014 23:58, Andreas Joseph Krogh wrote: > På torsdag 01. mai 2014 kl. 23:45:49, skrev Tomas Vondra >: > > On 1.5.2014 23:19, Andreas Joseph Krogh wrote: > > Just curious: > > Is such a JOIN index possible in other DBs, if so - which? > > Can other DBs

Re: [PERFORM] Optimize query for listing un-read messages

2014-05-01 Thread Andreas Joseph Krogh
På torsdag 01. mai 2014 kl. 23:45:49, skrev Tomas Vondra mailto:t...@fuzzy.cz>>: On 1.5.2014 23:19, Andreas Joseph Krogh wrote: > På torsdag 01. mai 2014 kl. 23:02:13, skrev Pavel Stehule > mailto:pavel.steh...@gmail.com>>: > >      >      >     2014-05-01 22:30 GMT+02:00 Andreas Joseph Krogh

Re: [PERFORM] Optimize query for listing un-read messages

2014-05-01 Thread Tomas Vondra
On 1.5.2014 23:19, Andreas Joseph Krogh wrote: > På torsdag 01. mai 2014 kl. 23:02:13, skrev Pavel Stehule > mailto:pavel.steh...@gmail.com>>: > > > > 2014-05-01 22:30 GMT+02:00 Andreas Joseph Krogh >: > > På torsdag 01. mai 2014 kl. 21:53:3

Re: [PERFORM] Revisiting disk layout on ZFS systems

2014-05-01 Thread Josh Berkus
On 04/28/2014 08:47 AM, Karl Denninger wrote: > The odd thing is that I am getting better performance with a 128k record > size on this application than I get with an 8k one! Not only is the > system faster to respond subjectively and can it sustain a higher TPS > load objectively but the I/O busy

Re: [PERFORM] Optimize query for listing un-read messages

2014-05-01 Thread Andreas Joseph Krogh
På torsdag 01. mai 2014 kl. 23:19:55, skrev David G Johnston < david.g.johns...@gmail.com >: How does something like: WITH unreads AS ( SELECT messageid FROM message EXCEPT SELECT messageid FROM message_property WHERE personid=1 AND has_read ) SELECT ...

Re: [PERFORM] Optimize query for listing un-read messages

2014-05-01 Thread Andreas Joseph Krogh
På torsdag 01. mai 2014 kl. 23:02:13, skrev Pavel Stehule < pavel.steh...@gmail.com >:     2014-05-01 22:30 GMT+02:00 Andreas Joseph Kroghmailto:andr...@visena.com>>: På torsdag 01. mai 2014 kl. 21:53:32, skrev Pavel Stehule < pavel.steh...@gmail.com

Re: [PERFORM] Optimize query for listing un-read messages

2014-05-01 Thread David G Johnston
How does something like: WITH unreads AS ( SELECT messageid FROM message EXCEPT SELECT messageid FROM message_property WHERE personid=1 AND has_read ) SELECT ... FROM unreads JOIN messages USING (messageid) ; perform? David J. -- View this message in context: http://postgresql.1045698.n5.na

Re: [PERFORM] Optimize query for listing un-read messages

2014-05-01 Thread Pavel Stehule
2014-05-01 22:30 GMT+02:00 Andreas Joseph Krogh : > På torsdag 01. mai 2014 kl. 21:53:32, skrev Pavel Stehule < > pavel.steh...@gmail.com>: > > > > 2014-05-01 21:39 GMT+02:00 Andreas Joseph Krogh : >> >> På torsdag 01. mai 2014 kl. 21:30:39, skrev Pavel Stehule < >> pavel.steh...@gmail.com>: >> >>

Re: [PERFORM] Optimize query for listing un-read messages

2014-05-01 Thread Andreas Joseph Krogh
På torsdag 01. mai 2014 kl. 21:53:32, skrev Pavel Stehule < pavel.steh...@gmail.com >:     2014-05-01 21:39 GMT+02:00 Andreas Joseph Kroghmailto:andr...@visena.com>>: På torsdag 01. mai 2014 kl. 21:30:39, skrev Pavel Stehule < pavel.steh...@gmail.com

Re: [PERFORM] Optimize query for listing un-read messages

2014-05-01 Thread Pavel Stehule
2014-05-01 21:39 GMT+02:00 Andreas Joseph Krogh : > På torsdag 01. mai 2014 kl. 21:30:39, skrev Pavel Stehule < > pavel.steh...@gmail.com>: > > Hello > [snip] > > I had a perfect success on similar use case with descent ordered partial > index > > http://www.postgresql.org/docs/9.3/interactive/sql

Re: [PERFORM] Optimize query for listing un-read messages

2014-05-01 Thread Andreas Joseph Krogh
På torsdag 01. mai 2014 kl. 21:30:39, skrev Pavel Stehule < pavel.steh...@gmail.com >: Hello [snip]   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

Re: [PERFORM] Optimize query for listing un-read messages

2014-05-01 Thread Pavel Stehule
Hello 2014-05-01 21:17 GMT+02:00 Andreas Joseph Krogh : > 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 pe

Re: [PERFORM] Optimize query for listing un-read messages

2014-05-01 Thread Andreas Joseph Krogh
På torsdag 01. mai 2014 kl. 20:35:07, skrev Jochem Berndsen mailto: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

Re: [PERFORM] Optimize query for listing un-read messages

2014-05-01 Thread Jochem Berndsen
Hi Andreas, [New to this list, forgive my ignorance.] On 05/01/2014 01:26 PM, Andreas Joseph Krogh wrote: I'm using PostgreSQL 9.3.2 on x86_64-unknown-linux-gnu My machine has PostgreSQL 9.1.13 on x86_64-unknown-linux-gnu. I have a schema where I have lots of messages and some users who might

[PERFORM] Optimize query for listing un-read messages

2014-05-01 Thread Andreas Joseph Krogh
Hi all,   I'm using PostgreSQL 9.3.2 on x86_64-unknown-linux-gnu I have a schema where I have lots of messages and some users who might have read some of them. When a message is read by a user I create an entry i a table message_property holding the property (is_read) for that user.   The schem