blob: 0c747c271c4d8bbe42888927eaa387f19387165a (
plain)
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
|
;; This buffer is for notes you don't want to save, and for Lisp evaluation.
;; If you want to create a file, visit that file with C-x C-f,
;; then enter the text in that file's own buffer.
explain analyze SELECT
m.content, m.message_id, m.created_on,
u.nick, u.avatar, r.key,
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 messages m, users u, rooms r
WHERE m.user_id = u.user_id AND u.nick = 'jeanette'
AND r.room_id = m.room_id AND r.admin_only = false AND m.is_image = true ORDER BY message_id DESC
LIMIT 40 OFFSET 0;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=6799.90..6800.00 rows=40 width=189) (actual time=574.213..574.318 rows=40 loops=1)
-> Sort (cost=6799.90..6800.10 rows=82 width=189) (actual time=574.207..574.244 rows=40 loops=1)
Sort Key: m.message_id
Sort Method: top-N heapsort Memory: 30kB
-> Hash Join (cost=28.81..6797.31 rows=82 width=189) (actual time=39.615..549.090 rows=14514 loops=1)
Hash Cond: (m.room_id = r.room_id)
-> Nested Loop (cost=27.71..4742.93 rows=680 width=161) (actual time=39.113..209.852 rows=14520 loops=1)
-> Index Scan using users_nick_key on users u (cost=0.00..8.27 rows=1 width=85) (actual time=6.983..6.986 rows=1 loops=1)
Index Cond: (nick = 'jeanette'::text)
-> Bitmap Heap Scan on messages m (cost=27.71..4716.13 rows=1483 width=84) (actual time=31.609..180.079 rows=14520 loops=1)
Recheck Cond: (m.user_id = u.user_id)
Filter: m.is_image
-> Bitmap Index Scan on messages_user_id_idx (cost=0.00..27.54 rows=1483 width=0) (actual time=25.450..25.450 rows=54778 loops=1)
Index Cond: (m.user_id = u.user_id)
-> Hash (cost=1.06..1.06 rows=3 width=36) (actual time=0.144..0.144 rows=32 loops=1)
-> Seq Scan on rooms r (cost=0.00..1.06 rows=3 width=36) (actual time=0.050..0.100 rows=32 loops=1)
Filter: (NOT admin_only)
SubPlan 1
-> Nested Loop (cost=0.00..25.00 rows=2 width=18) (actual time=0.015..0.018 rows=1 loops=14514)
-> Index Scan using tags_message_id_idx on tags (cost=0.00..8.43 rows=2 width=13) (actual
time=0.011..0.012 rows=1 loops=14514)
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=7278)
Index Cond: (users.user_id = tags.user_id)
Total runtime: 576.155 ms
(24 rows)
|
