agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedOptimize query for listing un-read messages
7+ messages / 2 participants
[nested] [flat]
* Optimize query for listing un-read messages
@ 2014-05-01 12:51 Andreas Joseph Krogh <andreas@visena.com>
2014-05-04 12:06 ` Re: Optimize query for listing un-read messages Brice André <brice@famille-andre.be>
0 siblings, 1 reply; 7+ messages in thread
From: Andreas Joseph Krogh @ 2014-05-01 12:51 UTC (permalink / raw)
To: pgsql-sql
Hi all, 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. The schema is
as follows: drop table if exists message_property;
drop table if exists message;
drop table if exists person; 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)
); insert into person(username) values('user_' || generate_series(0, 999));
insert into message(subject) values('Subject ' || random() ||
generate_series(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; 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. I have the following query to list all un-read messages for person
with id=1: SELECT
m.id AS message_id,
prop.person_id,
coalesce(prop.is_read, FALSE) AS is_read,
m.subject
FROM message m
LEFT OUTER JOIN message_property prop ON prop.message_id = m.id AND
prop.person_id = 1
WHERE 1 = 1
AND NOT EXISTS(SELECT
*
FROM message_property pr
WHERE pr.message_id = m.id AND pr.person_id =
prop.person_id AND prop.is_read = TRUE)
;
The problem is that it's not quite efficient and performs badly, explain
analyze shows:
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Merge Anti Join (cost=1.27..148784.09 rows=5 width=40) (actual
time=918.906..918.913 rows=10 loops=1)
Merge Cond: (m.id = pr.message_id)
Join Filter: (prop.is_read AND (pr.person_id = prop.person_id))
Rows Removed by Join Filter: 5
-> Merge Left Join (cost=0.85..90300.76 rows=1000000 width=40) (actual
time=0.040..530.748 rows=1000000 loops=1)
Merge Cond: (m.id = prop.message_id)
-> Index Scan using message_pkey on message m (cost=0.42..34317.43
rows=1000000 width=35) (actual time=0.014..115.829 rows=1000000 loops=1)
-> Index Scan using message_property_message_id_person_id_key on
message_property prop (cost=0.42..40983.40 rows=999995 width=9) (actual
time=0.020..130.728 rows=999995 loops=1)
Index Cond: (person_id = 1)
-> Index Only Scan using message_property_message_id_person_id_key on
message_property pr (cost=0.42..40983.40 rows=999995 width=8) (actual
time=0.024..140.349 rows=999995 loops=1)
Index Cond: (person_id = 1)
Heap Fetches: 999995
Total runtime: 918.975 ms
(13 rows)
Does anyone have suggestions on how to optimize the query or schema? It's
important that any message not having an entry in message_property for a user
is considered un-read.
Thanks! -- 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;
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Optimize query for listing un-read messages
2014-05-01 12:51 Optimize query for listing un-read messages Andreas Joseph Krogh <andreas@visena.com>
@ 2014-05-04 12:06 ` Brice André <brice@famille-andre.be>
2014-05-04 16:40 ` Re: Optimize query for listing un-read messages Andreas Joseph Krogh <andreas@visena.com>
0 siblings, 1 reply; 7+ messages in thread
From: Brice André @ 2014-05-04 12:06 UTC (permalink / raw)
To: Andreas Joseph Krogh <andreas@visena.com>; +Cc: pgsql-sql
Dear Andreas,
For me, putting both "LEFT OUTER JOIN" and "NOT EXISTS" is a bad idea.
As the "LEFT OUTER JOIN" will put fields of non-existing right table to
null, I would simply rewrite it :
SELECT ... FROM message m
LEFT OUTER JOIN message_property prop ON prop.message_id = m.id AND
prop.person_id = 1
WHERE prop.is_read = TRUE
I would also ensure that an efficient index is used for the outer join. I
would probably try at least a multi-column index on (message_id, person_id)
for the property table. I would also maybe give a try to an index on
(message_id, person_id, is_read), just to see if it improves performances.
Regards,
Brice
2014-05-01 14:51 GMT+02:00 Andreas Joseph Krogh <andreas@visena.com>:
> Hi all,
>
> 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.
>
> The schema is as follows:
>
> drop table if exists message_property;
> drop table if exists message;
> drop table if exists person;
>
> 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)
> );
>
> insert into person(username) values('user_' || generate_series(0, 999));
> insert into message(subject) values('Subject ' || random() ||
> generate_series(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;
>
> 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.
>
> I have the following query to list all un-read messages for person with
> id=1:
>
> SELECT
> m.id AS message_id,
> prop.person_id,
> coalesce(prop.is_read, FALSE) AS is_read,
> m.subject
> FROM message m
> LEFT OUTER JOIN message_property prop ON prop.message_id = m.id AND
> prop.person_id = 1
> WHERE 1 = 1
> AND NOT EXISTS(SELECT
> *
> FROM message_property pr
> WHERE pr.message_id = m.id AND pr.person_id =
> prop.person_id AND prop.is_read = TRUE)
> ;
>
> The problem is that it's not quite efficient and performs badly, explain
> analyze shows:
>
> QUERY PLAN
>
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Merge Anti Join (cost=1.27..148784.09 rows=5 width=40) (actual
> time=918.906..918.913 rows=10 loops=1)
> Merge Cond: (m.id = pr.message_id)
> Join Filter: (prop.is_read AND (pr.person_id = prop.person_id))
> Rows Removed by Join Filter: 5
> -> Merge Left Join (cost=0.85..90300.76 rows=1000000 width=40)
> (actual time=0.040..530.748 rows=1000000 loops=1)
> Merge Cond: (m.id = prop.message_id)
> -> Index Scan using message_pkey on message m
> (cost=0.42..34317.43 rows=1000000 width=35) (actual time=0.014..115.829
> rows=1000000 loops=1)
> -> Index Scan using message_property_message_id_person_id_key on
> message_property prop (cost=0.42..40983.40 rows=999995 width=9) (actual
> time=0.020..130.728 rows=999995 loops=1)
> Index Cond: (person_id = 1)
> -> Index Only Scan using message_property_message_id_person_id_key on
> message_property pr (cost=0.42..40983.40 rows=999995 width=8) (actual
> time=0.024..140.349 rows=999995 loops=1)
> Index Cond: (person_id = 1)
> Heap Fetches: 999995
> Total runtime: 918.975 ms
> (13 rows)
>
>
> Does anyone have suggestions on how to optimize the query or schema? It's
> important that any message not having an entry in message_property for a
> user is considered un-read.
>
> Thanks!
>
> --
> *Andreas Jospeh Krogh*
> CTO / Partner - Visena AS
> Mobile: +47 909 56 963
> andreas@visena.com
> www.visena.com
> <https://www.visena.com;
>
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Optimize query for listing un-read messages
2014-05-01 12:51 Optimize query for listing un-read messages Andreas Joseph Krogh <andreas@visena.com>
2014-05-04 12:06 ` Re: Optimize query for listing un-read messages Brice André <brice@famille-andre.be>
@ 2014-05-04 16:40 ` Andreas Joseph Krogh <andreas@visena.com>
2014-05-04 16:49 ` Re: Optimize query for listing un-read messages Brice André <brice@famille-andre.be>
0 siblings, 1 reply; 7+ messages in thread
From: Andreas Joseph Krogh @ 2014-05-04 16:40 UTC (permalink / raw)
To: Brice André <brice@famille-andre.be>; +Cc: pgsql-sql
På søndag 04. mai 2014 kl. 14:06:35, skrev Brice André <brice@famille-andre.be
<mailto:brice@famille-andre.be>>: Dear Andreas,
For me, putting both "LEFT OUTER JOIN" and "NOT EXISTS" is a bad idea.
As the "LEFT OUTER JOIN" will put fields of non-existing right table to
null, I would simply rewrite it :
SELECT ... FROM message m
LEFT OUTER JOIN message_property prop ON prop.message_id = m.id
<http://m.id; AND prop.person_id = 1
WHERE prop.is_read = TRUE
I would also ensure that an efficient index is used for the outer join. I
would probably try at least a multi-column index on (message_id, person_id) for
the property table. I would also maybe give a try to an index on (message_id,
person_id, is_read), just to see if it improves performances. The problem is
that your suggested query doesn't return the desired results as it effectively
is an INNER JOIN because you have "WHERE prop.is_read=TRUE", defeating the
whole purpose of a LEFT OUTER JOIN. -- 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;
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Optimize query for listing un-read messages
2014-05-01 12:51 Optimize query for listing un-read messages Andreas Joseph Krogh <andreas@visena.com>
2014-05-04 12:06 ` Re: Optimize query for listing un-read messages Brice André <brice@famille-andre.be>
2014-05-04 16:40 ` Re: Optimize query for listing un-read messages Andreas Joseph Krogh <andreas@visena.com>
@ 2014-05-04 16:49 ` Brice André <brice@famille-andre.be>
2014-05-04 16:53 ` Re: Optimize query for listing un-read messages Andreas Joseph Krogh <andreas@visena.com>
0 siblings, 1 reply; 7+ messages in thread
From: Brice André @ 2014-05-04 16:49 UTC (permalink / raw)
To: Andreas Joseph Krogh <andreas@visena.com>; +Cc: pgsql-sql
Yes, I was a bit too fast. but replace it with
WHERE NOT prop.is_read = TRUE
and it should be OK.
2014-05-04 18:40 GMT+02:00 Andreas Joseph Krogh <andreas@visena.com>:
> På søndag 04. mai 2014 kl. 14:06:35, skrev Brice André <
> brice@famille-andre.be>:
>
> Dear Andreas,
>
> For me, putting both "LEFT OUTER JOIN" and "NOT EXISTS" is a bad idea.
>
> As the "LEFT OUTER JOIN" will put fields of non-existing right table to
> null, I would simply rewrite it :
> SELECT ... FROM message m
> LEFT OUTER JOIN message_property prop ON prop.message_id = m.id AND
> prop.person_id = 1
> WHERE prop.is_read = TRUE
>
> I would also ensure that an efficient index is used for the outer join. I
> would probably try at least a multi-column index on (message_id, person_id)
> for the property table. I would also maybe give a try to an index on
> (message_id, person_id, is_read), just to see if it improves performances.
>
>
> The problem is that your suggested query doesn't return the desired
> results as it effectively is an INNER JOIN because you have "WHERE
> prop.is_read=TRUE", defeating the whole purpose of a LEFT OUTER JOIN.
>
> --
> *Andreas Jospeh Krogh*
> CTO / Partner - Visena AS
> Mobile: +47 909 56 963
> andreas@visena.com
> www.visena.com
> <https://www.visena.com;
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Optimize query for listing un-read messages
2014-05-01 12:51 Optimize query for listing un-read messages Andreas Joseph Krogh <andreas@visena.com>
2014-05-04 12:06 ` Re: Optimize query for listing un-read messages Brice André <brice@famille-andre.be>
2014-05-04 16:40 ` Re: Optimize query for listing un-read messages Andreas Joseph Krogh <andreas@visena.com>
2014-05-04 16:49 ` Re: Optimize query for listing un-read messages Brice André <brice@famille-andre.be>
@ 2014-05-04 16:53 ` Andreas Joseph Krogh <andreas@visena.com>
2014-05-04 17:43 ` Re: Optimize query for listing un-read messages Brice André <brice@famille-andre.be>
0 siblings, 1 reply; 7+ messages in thread
From: Andreas Joseph Krogh @ 2014-05-04 16:53 UTC (permalink / raw)
To: Brice André <brice@famille-andre.be>; +Cc: pgsql-sql
På søndag 04. mai 2014 kl. 18:49:43, skrev Brice André <brice@famille-andre.be
<mailto:brice@famille-andre.be>>: Yes, I was a bit too fast. but replace it with
WHERE NOT prop.is_read = TRUE
and it should be OK. No, that also will be treated as an INNER JOIN,
because it kills tuples where prop is null. I need entries where prop IS NULL
(hence the LEFT OUTER JOIN) because messages without an entry in
message_property must be treated as unread, the same as messages with an entry
in message_property where is_read=FALSE. -- 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;
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Optimize query for listing un-read messages
2014-05-01 12:51 Optimize query for listing un-read messages Andreas Joseph Krogh <andreas@visena.com>
2014-05-04 12:06 ` Re: Optimize query for listing un-read messages Brice André <brice@famille-andre.be>
2014-05-04 16:40 ` Re: Optimize query for listing un-read messages Andreas Joseph Krogh <andreas@visena.com>
2014-05-04 16:49 ` Re: Optimize query for listing un-read messages Brice André <brice@famille-andre.be>
2014-05-04 16:53 ` Re: Optimize query for listing un-read messages Andreas Joseph Krogh <andreas@visena.com>
@ 2014-05-04 17:43 ` Brice André <brice@famille-andre.be>
2014-05-04 18:03 ` Re: Optimize query for listing un-read messages Andreas Joseph Krogh <andreas@visena.com>
0 siblings, 1 reply; 7+ messages in thread
From: Brice André @ 2014-05-04 17:43 UTC (permalink / raw)
To: Andreas Joseph Krogh <andreas@visena.com>; +Cc: pgsql-sql
Forget my last answer : it was a stupid one... I tried to answer quickly,
but with tiredness, it does not give good results.
For me, your problem of performance comes from the "WHERE NOT EXISTS
(query)" because your query is executed on each result of the outer join.
I tried to figure out how you can avoid this with your current database
design, but I did not found any solution. Maybe someone on the forum will
have an idea.
If not, what I can propose your is to arrange yourself so that, for each
couple (message, user) of your database, you have a corresponding entry in
message_property, so that the first solution I proposed you (with an inner
join) will work. And with multi-column indexes, it should be fast.
To do so, you can use trigger mechanism on both the insertion of the
message to create all message_property entries of that message, and on user
insertion to create all message_properties of the user, so that you do not
need to change anything outside your SQL design.
The disadvantages of this solution are that the insertion of a new message
or of a new message will be slower, and that your database size will be
greater, but it should solve the problem of fast determining all read or
unread messages of a dedicated user.
Regards,
Brice
2014-05-04 18:53 GMT+02:00 Andreas Joseph Krogh <andreas@visena.com>:
> På søndag 04. mai 2014 kl. 18:49:43, skrev Brice André <
> brice@famille-andre.be>:
>
> Yes, I was a bit too fast. but replace it with
>
> WHERE NOT prop.is_read = TRUE
>
> and it should be OK.
>
>
> No, that also will be treated as an INNER JOIN, because it kills tuples
> where prop is null. I need entries where prop IS NULL (hence the LEFT OUTER
> JOIN) because messages without an entry in message_property must be treated
> as unread, the same as messages with an entry in message_property where
> is_read=FALSE.
>
> --
> *Andreas Jospeh Krogh*
> CTO / Partner - Visena AS
> Mobile: +47 909 56 963
> andreas@visena.com
> www.visena.com
> <https://www.visena.com;
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Optimize query for listing un-read messages
2014-05-01 12:51 Optimize query for listing un-read messages Andreas Joseph Krogh <andreas@visena.com>
2014-05-04 12:06 ` Re: Optimize query for listing un-read messages Brice André <brice@famille-andre.be>
2014-05-04 16:40 ` Re: Optimize query for listing un-read messages Andreas Joseph Krogh <andreas@visena.com>
2014-05-04 16:49 ` Re: Optimize query for listing un-read messages Brice André <brice@famille-andre.be>
2014-05-04 16:53 ` Re: Optimize query for listing un-read messages Andreas Joseph Krogh <andreas@visena.com>
2014-05-04 17:43 ` Re: Optimize query for listing un-read messages Brice André <brice@famille-andre.be>
@ 2014-05-04 18:03 ` Andreas Joseph Krogh <andreas@visena.com>
0 siblings, 0 replies; 7+ messages in thread
From: Andreas Joseph Krogh @ 2014-05-04 18:03 UTC (permalink / raw)
To: Brice André <brice@famille-andre.be>; +Cc: pgsql-sql
På søndag 04. mai 2014 kl. 19:43:11, skrev Brice André <brice@famille-andre.be
<mailto:brice@famille-andre.be>>: Forget my last answer : it was a stupid
one... I tried to answer quickly, but with tiredness, it does not give good
results.
For me, your problem of performance comes from the "WHERE NOT EXISTS
(query)" because your query is executed on each result of the outer join.
I tried to figure out how you can avoid this with your current database
design, but I did not found any solution. Maybe someone on the forum will have
an idea.
If not, what I can propose your is to arrange yourself so that, for each
couple (message, user) of your database, you have a corresponding entry in
message_property, so that the first solution I proposed you (with an inner
join) will work. And with multi-column indexes, it should be fast.
To do so, you can use trigger mechanism on both the insertion of the
message to create all message_property entries of that message, and on user
insertion to create all message_properties of the user, so that you do not need
to change anything outside your SQL design.
The disadvantages of this solution are that the insertion of a new message
or of a new message will be slower, and that your database size will be
greater, but it should solve the problem of fast determining all read or unread
messages of a dedicated user. Yes, the reason it cannot be fast is because PG
is unable to index the difference between two sets, so my schema, although a
correct one, isn't index friendly so a caching-mechanism must be used for fast,
indexed access. The solution is to redesign and have an entry in
message_property for each combination of user/message. -- 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;
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2014-05-04 18:03 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-05-01 12:51 Optimize query for listing un-read messages Andreas Joseph Krogh <andreas@visena.com>
2014-05-04 12:06 ` Brice André <brice@famille-andre.be>
2014-05-04 16:40 ` Andreas Joseph Krogh <andreas@visena.com>
2014-05-04 16:49 ` Brice André <brice@famille-andre.be>
2014-05-04 16:53 ` Andreas Joseph Krogh <andreas@visena.com>
2014-05-04 17:43 ` Brice André <brice@famille-andre.be>
2014-05-04 18:03 ` Andreas Joseph Krogh <andreas@visena.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox