1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
|
Query 1 (USER_ID)
------------------------------------------
explain analyze SELECT
m.content, m.message_id, m.created_on, u.nick, u.avatar,
array_to_string(ARRAY(SELECT nick || ' ' || tag
FROM tags, users
WHERE message_id = m.message_id AND tags.user_id = users.user_id), ' ') as tags
from users u
left join messages m on u.user_id = m.user_id
where
--u.nick = 'jeanette'
u.user_id = 6
and m.is_image = true and m.room_id = 1
order by m.created_on desc
LIMIT 40 offset 1000;
Limit (cost=0.00..1526.37 rows=40 width=157) (actual time=0.121..1.696 rows=40 loops=1)
-> Nested Loop (cost=0.00..880298.45 rows=23069 width=157) (actual time=0.118..1.621 rows=40 loops=1)
-> Index Scan using messages_user_created_on_idx on messages m (cost=0.00..112630.78 rows=23069 width=80) (actual time=0.056..0.234 rows=40 loops=1)
Index Cond: (user_id = 6)
Filter: (is_image AND (room_id = 1))
-> Index Scan using users_pkey on users u (cost=0.00..8.27 rows=1 width=85) (actual time=0.004..0.006 rows=1 loops=40)
Index Cond: (u.user_id = 6)
SubPlan 1
-> Nested Loop (cost=0.00..25.00 rows=2 width=18) (actual time=0.012..0.019 rows=1 loops=40)
-> Index Scan using tags_message_id_idx on tags (cost=0.00..8.43 rows=2 width=13) (actual time=0.006..0.007 rows=1 loops=40)
Index Cond: (message_id = $0)
-> Index Scan using users_pkey on users (cost=0.00..8.27 rows=1 width=13) (actual time=0.005..0.006 rows=1 loops=37)
Index Cond: (users.user_id = tags.user_id)
Total runtime: 1.829 ms
(14 rows)
Query 2 (NICK)
----------------------------------------
explain analyze SELECT
m.content, m.message_id, m.created_on, u.nick, u.avatar,
array_to_string(ARRAY(SELECT nick || ' ' || tag
FROM tags, users
WHERE message_id = m.message_id AND tags.user_id = users.user_id), ' ') as tags
from users u
left join messages m on u.user_id = m.user_id
where
u.nick = 'jeanette'
-- u.user_id = 6
and m.is_image = true and m.room_id = 1
order by m.created_on desc
LIMIT 40 offset 0;
Limit (cost=20371.96..20372.06 rows=40 width=157) (actual time=532.538..532.611 rows=40 loops=1)
-> Sort (cost=20371.96..20373.52 rows=624 width=157) (actual time=532.535..532.560 rows=40 loops=1)
Sort Key: m.created_on
Sort Method: top-N heapsort Memory: 29kB
-> Nested Loop (cost=27.71..20352.24 rows=624 width=157) (actual time=83.105..502.069 rows=14354 loops=1)
-> Index Scan using users_nick_key on users u (cost=0.00..8.27 rows=1 width=85) (actual time=0.047..0.048 rows=1 loops=1)
Index Cond: (nick = 'jeanette'::text)
-> Bitmap Heap Scan on messages m (cost=27.71..4726.08 rows=1485 width=80) (actual time=83.025..180.705 rows=14354 loops=1)
Recheck Cond: (m.user_id = u.user_id)
Filter: (m.is_image AND (m.room_id = 1))
-> Bitmap Index Scan on messages_user_id_idx (cost=0.00..27.55 rows=1485 width=0) (actual time=81.113..81.113 rows=54778 loops=1)
Index Cond: (m.user_id = u.user_id)
SubPlan 1
-> Nested Loop (cost=0.00..25.00 rows=2 width=18) (actual time=0.013..0.017 rows=1 loops=14354)
-> Index Scan using tags_message_id_idx on tags (cost=0.00..8.43 rows=2 width=13) (actual time=0.009..0.010 rows=1 loops=14354)
Index Cond: (message_id = $0)
-> Index Scan using users_pkey on users (cost=0.00..8.27 rows=1 width=13) (actual time=0.004..0.006 rows=1 loops=7190)
Index Cond: (users.user_id = tags.user_id)
Total runtime: 532.757 ms
(19 rows)
QUERY 3 (ROOM JOIN)
----------------
2010-08-23 02:27:30 EDT LOG: duration: 10817.570 ms execute <unnamed>:
explain analyze SELECT
m.content, m.message_id, m.created_on,
u.nick, u.avatar,
array_to_string(ARRAY(SELECT nick || ' ' || tag
FROM tags
WHERE message_id = m.message_id AND tags.user_id = u.user_id), ' ') as tags
FROM users u, messages m
WHERE room_id = 2
AND m.user_id = u.user_id ORDER BY created_on DESC
LIMIT 200 OFFSET 0;
select
PLAN
----
Limit (cost=115535.24..115535.74 rows=200 width=157) (actual time=7835.228..7835.592 rows=200 loops=1)
-> Sort (cost=115535.24..115546.44 rows=4477 width=157) (actual time=7835.224..7835.356 rows=200 loops=1)
Sort Key: m.created_on
Sort Method: top-N heapsort Memory: 58kB
-> Hash Join (cost=354.59..115341.75 rows=4477 width=157) (actual time=256.324..7807.477 rows=13581 loops=1)
Hash Cond: (m.user_id = u.user_id)
-> Index Scan using messages_room_id_idx on messages m (cost=0.00..2970.23 rows=4477 width=80) (actual time=7.260..5647.945 rows=13581 loops=1)
Index Cond: (room_id = 2)
-> Hash (cost=262.04..262.04 rows=7404 width=85) (actual time=237.419..237.419 rows=5675 loops=1)
-> Seq Scan on users u (cost=0.00..262.04 rows=7404 width=85) (actual time=0.208..227.609 rows=5675 loops=1)
SubPlan 1
-> Nested Loop (cost=0.00..25.00 rows=2 width=18) (actual time=0.126..0.135 rows=0 loops=13581)
-> Index Scan using tags_message_id_idx on tags (cost=0.00..8.43 rows=2 width=13) (actual time=0.122..0.131 rows=0 loops=13581)
Index Cond: (message_id = $0)
-> Index Scan using users_pkey on users (cost=0.00..8.27 rows=1 width=13) (actual time=0.010..0.011 rows=1 loops=243)
Index Cond: (users.user_id = tags.user_id)
Total runtime: 7837.031 ms
(17 rows)
QUERY 4 (FEED_IMAGES)
------------------------
explain analyze SELECT * FROM feed_images
WHERE room = 'boys' AND message_id IS NULL
ORDER BY queued_on ASC LIMIT 1;
Limit (cost=3966.33..3966.33 rows=1 width=231) (actual time=8779.090..8779.093 rows=1 loops=1)
-> Sort (cost=3966.33..3966.33 rows=1 width=231) (actual time=8779.084..8779.084 rows=1 loops=1)
Sort Key: queued_on
Sort Method: top-N heapsort Memory: 17kB
-> Bitmap Heap Scan on feed_images (cost=921.32..3966.32 rows=1 width=231) (actual time=8772.479..8772.497 rows=7 loops=1)
Recheck Cond: (room = 'boys'::text)
Filter: (message_id IS NULL)
-> Bitmap Index Scan on feed_images_pkey (cost=0.00..921.32 rows=12400 width=0) (actual time=5878.657..5878.657 rows=13408 loops=1)
Index Cond: (room = 'boys'::text)
Total runtime: 8779.219 ms
(10 rows)
|
