Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WfqUZ-0004ch-Ex for pgsql-sql@arkaria.postgresql.org; Thu, 01 May 2014 12:53:35 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1WfqUY-0004FE-4x for pgsql-sql@arkaria.postgresql.org; Thu, 01 May 2014 12:53:34 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WfqUV-0004F5-Pw for pgsql-sql@postgresql.org; Thu, 01 May 2014 12:53:32 +0000 Received: from prod2.officenet.no ([195.159.87.246]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WfqUJ-0004NC-FF for pgsql-sql@postgresql.org; Thu, 01 May 2014 12:53:30 +0000 Received: from localhost ([127.0.0.1] helo=prod2) by prod2.officenet.no with esmtp (Exim 4.76) (envelope-from ) id 1WfqSE-0003A2-Nk for pgsql-sql@postgresql.org; Thu, 01 May 2014 14:51:10 +0200 Date: Thu, 1 May 2014 14:51:10 +0200 (CEST) From: Andreas Joseph Krogh To: pgsql-sql Message-ID: Subject: Optimize query for listing un-read messages MIME-Version: 1.0 X-Mailer: OfficeNet Mail 1.9.0-SNAPSHOT X-Pg-Spam-Score: -1.9 (-) Content-Type: multipart/alternative; boundary="----=_Part_17_1562536747.1398948670655" List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org ------=_Part_15_1275553138.1398948670640 Content-Type: multipart/related; boundary="----=_Part_16_2039826012.1398948670640" ------=_Part_16_2039826012.1398948670640 Content-Type: multipart/alternative; boundary="----=_Part_17_1562536747.1398948670655" ------=_Part_17_1562536747.1398948670655 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Hi all, =C2=A0 I'm using PostgreSQL 9.3.2 on x86_64-unknown-linux-gnu=20 I have a schema where I have lots of messages and some users who might hav= e=20 read some of them. When a message is read by a user I create an entry i a t= able=20 message_property holding the property (is_read) for that user. =C2=A0 The s= chema is=20 as follows: =C2=A0 drop table if exists message_property; drop table if exists message; drop table if exists person; =C2=A0 create table person( =C2=A0=C2=A0=C2=A0 id serial primary key, =C2=A0=C2=A0=C2=A0 username varchar not null unique ); =C2=A0 create table message( =C2=A0=C2=A0=C2=A0 id serial primary key, =C2=A0=C2=A0=C2=A0 subject varchar ); =C2=A0 create table message_property( =C2=A0=C2=A0=C2=A0 message_id integer not null references message(id), =C2=A0=C2=A0=C2=A0 person_id integer not null references person(id), =C2=A0=C2=A0=C2=A0 is_read boolean not null default false, =C2=A0=C2=A0=C2=A0 unique(message_id, person_id) ); =C2=A0 insert into person(username) values('user_' || generate_series(0= , 999)); insert into message(subject) values('Subject ' || random() ||=20 generate_series(0, 999999)); insert into message_property(message_id, person_id, is_read) select id, 1,= =20 true from message order by id limit 999990; insert into message_property(message_id, person_id, is_read) select id, 1,= =20 false from message order by id limit 5 offset 999990; analyze; =C2=A0 So, f= or person=20 1 there are 10 unread messages, out of a total 1mill. 5 of those unread doe= s=20 not have an entry in message_property and 5 have an entry and is_read set t= o=20 FALSE. =C2=A0 I have the following query to list all un-read messages for p= erson=20 with id=3D1: =C2=A0 SELECT =C2=A0=C2=A0=C2=A0 m.id=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0 AS message_id, =C2=A0=C2=A0=C2=A0 prop.person_id, =C2=A0=C2=A0=C2=A0 coalesce(prop.is_read, FALSE) AS is_read, =C2=A0=C2=A0=C2=A0 m.subject FROM message m =C2=A0=C2=A0=C2=A0 LEFT OUTER JOIN message_property prop ON prop.message_i= d =3D m.id AND=20 prop.person_id =3D 1 WHERE 1 =3D 1 =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 AND NOT EXISTS(SELECT =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 * =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 FROM message_property pr =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 WHERE pr.message_id =3D m.= id AND pr.person_id =3D=20 prop.person_id AND prop.is_read =3D TRUE) =C2=A0=C2=A0=C2=A0 ;=20 The problem is that it's not quite efficient and performs badly, explain= =20 analyze shows:=20 =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=20 QUERY PLAN =20 ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= --------------------------------------- =C2=A0Merge Anti Join=C2=A0 (cost=3D1.27..148784.09 rows=3D5 width=3D40) (= actual=20 time=3D918.906..918.913 rows=3D10 loops=3D1) =C2=A0=C2=A0 Merge Cond: (m.id =3D pr.message_id) =C2=A0=C2=A0 Join Filter: (prop.is_read AND (pr.person_id =3D prop.person_= id)) =C2=A0=C2=A0 Rows Removed by Join Filter: 5 =C2=A0=C2=A0 ->=C2=A0 Merge Left Join=C2=A0 (cost=3D0.85..90300.76 rows=3D= 1000000 width=3D40) (actual=20 time=3D0.040..530.748 rows=3D1000000 loops=3D1) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Merge Cond: (m.id =3D pro= p.message_id) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ->=C2=A0 Index Scan using= message_pkey on message m=C2=A0 (cost=3D0.42..34317.43=20 rows=3D1000000 width=3D35) (actual time=3D0.014..115.829 rows=3D1000000 loo= ps=3D1) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ->=C2=A0 Index Scan using= message_property_message_id_person_id_key on=20 message_property prop=C2=A0 (cost=3D0.42..40983.40 rows=3D999995 width=3D9)= (actual=20 time=3D0.020..130.728 rows=3D999995 loops=3D1) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0 Index Cond: (person_id =3D 1) =C2=A0=C2=A0 ->=C2=A0 Index Only Scan using message_property_message_id_pe= rson_id_key on=20 message_property pr=C2=A0 (cost=3D0.42..40983.40 rows=3D999995 width=3D8) (= actual=20 time=3D0.024..140.349 rows=3D999995 loops=3D1) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Index Cond: (person_id = =3D 1) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Heap Fetches: 999995 =C2=A0Total runtime: 918.975 ms (13 rows) =C2=A0=20 Does anyone have suggestions on how to optimize the query or schema? It's= =20 important that any message not having an entry in message_property for a us= er=20 is considered un-read.=20 Thanks! =C2=A0 -- Andreas Jospeh Krogh CTO / Partner - Visena AS Mobile: += 47 909=20 56 963 andreas@visena.com www.visena.com=20 =C2=A0 =C2=A0 ------=_Part_17_1562536747.1398948670655 Content-Type: text/html;charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Hi all,
=C2=A0
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.
=C2=A0
The schema is as follows:
=C2=A0
drop table if exists message_property;
drop table if exists message;
drop table if exists person;
=C2=A0
create table person(
=C2=A0=C2=A0=C2=A0 id serial primary key,
=C2=A0=C2=A0=C2=A0 username varchar not null unique
);
=C2=A0
create table message(
=C2=A0=C2=A0=C2=A0 id serial primary key,
=C2=A0=C2=A0=C2=A0 subject varchar
);
=C2=A0
create table message_property(
=C2=A0=C2=A0=C2=A0 message_id integer not null references message(id),
=C2=A0=C2=A0=C2=A0 person_id integer not null references person(id),
=C2=A0=C2=A0=C2=A0 is_read boolean not null default false,
=C2=A0=C2=A0=C2=A0 unique(message_id, person_id)
);
=C2=A0
insert into person(username) values('user_' || generate_series(0, 999)= );
insert into message(subject) values('Subject ' || random() || generate_seri= es(0, 999999));
insert into message_property(message_id, person_id, is_read) select id, 1, = true from message order by id limit 999990;
insert into message_property(message_id, person_id, is_read) select id, 1, = false from message order by id limit 5 offset 999990;
analyze;
=C2=A0
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 e= ntry and is_read set to FALSE.
=C2=A0
I have the following query to list all un-read messages for person wit= h id=3D1:
=C2=A0
SELECT
=C2=A0=C2=A0=C2=A0 m.id=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0 AS message_id,
=C2=A0=C2=A0=C2=A0 prop.person_id,
=C2=A0=C2=A0=C2=A0 coalesce(prop.is_read, FALSE) AS is_read,
=C2=A0=C2=A0=C2=A0 m.subject
FROM message m
=C2=A0=C2=A0=C2=A0 LEFT OUTER JOIN message_property prop ON prop.message_id= =3D m.id AND prop.person_id =3D 1
WHERE 1 =3D 1
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 AND NOT EXISTS(SELECT
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 *
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 FROM message_property pr
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 WHERE pr.message_id =3D m.id = AND pr.person_id =3D prop.person_id AND prop.is_read =3D TRUE)
=C2=A0=C2=A0=C2=A0 ;

The problem is that it's not quite efficient and performs badly, explain an= alyze shows:
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0 QUERY PLAN
---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------
=C2=A0Merge Anti Join=C2=A0 (cost=3D1.27..148784.09 rows=3D5 width=3D40) (a= ctual time=3D918.906..918.913 rows=3D10 loops=3D1)
=C2=A0=C2=A0 Merge Cond: (m.id =3D pr.message_id)
=C2=A0=C2=A0 Join Filter: (prop.is_read AND (pr.person_id =3D prop.person_i= d))
=C2=A0=C2=A0 Rows Removed by Join Filter: 5
=C2=A0=C2=A0 ->=C2=A0 Merge Left Join=C2=A0 (cost=3D0.85..90300.76 rows= =3D1000000 width=3D40) (actual time=3D0.040..530.748 rows=3D1000000 loops= =3D1)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Merge Cond: (m.id =3D prop= .message_id)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ->=C2=A0 Index Scan usi= ng message_pkey on message m=C2=A0 (cost=3D0.42..34317.43 rows=3D1000000 wi= dth=3D35) (actual time=3D0.014..115.829 rows=3D1000000 loops=3D1)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ->=C2=A0 Index Scan usi= ng message_property_message_id_person_id_key on message_property prop=C2=A0= (cost=3D0.42..40983.40 rows=3D999995 width=3D9) (actual time=3D0.020..130.= 728 rows=3D999995 loops=3D1)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0 Index Cond: (person_id =3D 1)
=C2=A0=C2=A0 ->=C2=A0 Index Only Scan using message_property_message_id_= person_id_key on message_property pr=C2=A0 (cost=3D0.42..40983.40 rows=3D99= 9995 width=3D8) (actual time=3D0.024..140.349 rows=3D999995 loops=3D1)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Index Cond: (person_id =3D= 1)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Heap Fetches: 999995
=C2=A0Total runtime: 918.975 ms
(13 rows)
=C2=A0

Does anyone have suggestions on how to optimize the query or schema? It's i= mportant that any message not having an entry in message_property for a use= r is considered un-read.

Thanks!
=C2=A0
--
Andrea= s Jospeh Krogh
CTO / Partner<= /span> - Visena AS
Mobile: +47 90= 9 56 963
3D""
=C2=A0
=C2=A0
------=_Part_17_1562536747.1398948670655-- ------=_Part_16_2039826012.1398948670640-- ------=_Part_15_1275553138.1398948670640--