Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-performance-owner+M51646=pgsql-performance-ar@postgresql.org>)
 id 1Wfzt4-0001Om-Tv
 for pgsql-performance@arkaria.postgresql.org; Thu, 01 May 2014 22:55:31 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.80) (envelope-from
 <pgsql-performance-owner+M51646=pgsql-performance-ar@postgresql.org>)
 id 1Wfzt4-0008Jw-8q
 for pgsql-performance@arkaria.postgresql.org; Thu, 01 May 2014 22:55:30 +0000
Received: from makus.postgresql.org ([2001:4800:7903:4::125])
 by malur.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <david.g.johnston@gmail.com>) id 1Wfzt3-0008Jp-33
 for pgsql-performance@postgresql.org; Thu, 01 May 2014 22:55:29 +0000
Received: from sam.nabble.com ([216.139.236.26])
 by makus.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <david.g.johnston@gmail.com>) id 1Wfzt0-0006lw-0A
 for pgsql-performance@postgresql.org; Thu, 01 May 2014 22:55:28 +0000
Received: from [192.168.236.26] (helo=sam.nabble.com)
 by sam.nabble.com with esmtp (Exim 4.72)
 (envelope-from <david.g.johnston@gmail.com>) id 1Wfzsz-0002aV-BZ
 for pgsql-performance@postgresql.org; Thu, 01 May 2014 15:55:25 -0700
Date: Thu, 1 May 2014 15:55:25 -0700 (PDT)
From: David G Johnston <david.g.johnston@gmail.com>
To: pgsql-performance@postgresql.org
Message-ID: <1398984925338-5802170.post@n5.nabble.com>
In-Reply-To: <OfficeNetEmail.31.d5ed6143e3791024.145b9eecf33@prod2>
References: 
 <CAFj8pRCT_DRwSCYfW3i9X_Sy+Z9fQBZ-Hf1d37ke20+L6H-pag@mail.gmail.com>
 <OfficeNetEmail.27.eb2fd548b278ed75.145b94b1c1a@prod2>
 <CAFj8pRAOrokrj9JDG=u6uqxOfb26EsbN6zhuRq9zPQeSQ4k-og@mail.gmail.com>
 <OfficeNetEmail.29.6f26451e1c5ffa11.145b977f133@prod2>
 <CAFj8pRBiNroy_+Pa-7GnLR4vkoQNi6PHu1zva+=i4H-A0H4agQ@mail.gmail.com>
 <OfficeNetEmail.2b.83381fd877c3dffc.145b99dd5ea@prod2>
 <5362C08D.8060008@fuzzy.cz>
 <OfficeNetEmail.2f.30dd17c640e2229.145b9c57087@prod2>
 <5362CBFA.6060004@fuzzy.cz>
 <OfficeNetEmail.31.d5ed6143e3791024.145b9eecf33@prod2>
Subject: Re: Optimize query for listing un-read messages
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Pg-Spam-Score: 3.5 (+++)
List-Archive: <http://archives.postgresql.org/pgsql-performance>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-performance.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@postgresql.org>
List-Post: <mailto:pgsql-performance@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-performance>
List-Unsubscribe: 
 <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
X-Mailing-List: pgsql-performance
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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 dimension does not grow
unbounded?

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 = reader_ids || 1 WHERE
messageid = ..."  I'm not that familiar with how well indexes over arrays
work or which kind is needed (i.e. gin/gist).

HTH

David J.




--
View this message in context: http://postgresql.1045698.n5.nabble.com/Optimize-query-for-listing-un-read-messages-tp5802097p5802170.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance