diff options
Diffstat (limited to 'docs/userlog-query.txt')
| -rw-r--r-- | docs/userlog-query.txt | 44 |
1 files changed, 44 insertions, 0 deletions
diff --git a/docs/userlog-query.txt b/docs/userlog-query.txt new file mode 100644 index 0000000..0c747c2 --- /dev/null +++ b/docs/userlog-query.txt @@ -0,0 +1,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)
|