From: Andreas Joseph Krogh <andreas@visena.com>
To: pgsql-performance@postgresql.org
Subject: Re: Optimize query for listing un-read messages
Date: Fri, 2 May 2014 09:20:22 +0200 (CEST)
Message-ID: <OfficeNetEmail.75.5efec2c7653ca1ac.145bbcd4e27@prod2> (raw)
In-Reply-To: <CAFwQ8rcuOMRo0Mm1W41OyFCHK3oqAmN-jhvMRZNcuEWDU709Ow@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
På fredag 02. mai 2014 kl. 02:17:58, skrev Craig James <cjames@emolecules.com
<mailto:cjames@emolecules.com>>: On Thu, May 1, 2014 at 4:26 AM, Andreas Joseph
Krogh<andreas@visena.com <mailto:andreas@visena.com>> 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 schema is as follows: [...]
create table person( id serial primary key,
username varchar not null unique
); create table message(
id serial primary key,
subject varchar
); create table message_property(
message_id integer not null references message(id),
person_id integer not null references person(id),
is_read boolean not null default false,
unique(message_id, person_id)
); [...] So, for person 1 there are 10 unread messages, out of a total
1mill. 5 of those unread does not have an entry in message_property and 5 have
an entry and is_read set to FALSE. Here's a possible enhancement: add two
columns, an indexed timestamp to the message table, and a "timestamp of the
oldest message this user has NOT read" on the person table. If most users read
messages in a timely fashion, this would (in most cases) narrow down the
portion of the messages table to a tiny fraction of the total -- just those
messages newer than the oldest message this user has not read.
When you sign up a new user, you can set his timestamp to the time the
account was created, since presumably messages before that time don't apply.
Whether this will help depends a lot on actual use patterns, i.e. do users
typically read all messages or do they leave a bunch of unread messages sitting
around forever? Thanks fort the suggestion. A user must be able to read
arbitrary old messages, and messages don't expire. -- Andreas Jospeh Krogh
CTO / Partner - Visena AS Mobile: +47 909 56 963 andreas@visena.com
<mailto:andreas@visena.com> www.visena.com <https://www.visena.com;
<https://www.visena.com;
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: andreas@visena.com
Subject: Re: Optimize query for listing un-read messages
In-Reply-To: <OfficeNetEmail.75.5efec2c7653ca1ac.145bbcd4e27@prod2>
* 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