pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: David G Johnston <david.g.johnston@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Optimize query for listing un-read messages
Date: Thu, 1 May 2014 15:55:25 -0700 (PDT)
Message-ID: <1398984925338-5802170.post@n5.nabble.com> (raw)
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>
List-Unsubscribe:  <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>

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-tp5802097p580217...
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



view thread (22+ messages)  latest in thread

Message-ID: <1398984925338-5802170.post@n5.nabble.com>
Permalink:  ../1398984925338-5802170.post@n5.nabble.com/
Also on:    postgresql.org/message-id/1398984925338-5802170.post@n5.nabble.com

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: david.g.johnston@gmail.com
  Subject: Re: Optimize query for listing un-read messages
  In-Reply-To: <1398984925338-5802170.post@n5.nabble.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox