Re: Optimize query for listing un-read messages - Mailing list pgsql-performance

On Thu, May 1, 2014 at 4:26 AM, Andreas Joseph Krogh <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?

--

Andreas Jospeh Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com