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
|
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 0;
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;
QUERY 1 (USER_ID)
----------------------------------------------------------------------------------------------------------------------------------------------------------------
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)
---------------------------------------------------------------------------------------------------------------------------------------------------------
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)
|
