From 6ae374d58ff490bc161a39420574f1b99d6ca446 Mon Sep 17 00:00:00 2001
From: Scott Ostler <scottbot9000@gmail.com>
Date: Sat, 21 Aug 2010 22:35:26 -0400
Subject: Add userlog-query file

---
 docs/userlog-query.txt | 44 ++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 44 insertions(+)
 create mode 100644 docs/userlog-query.txt

(limited to 'docs')

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)
-- 
cgit v1.2.3-70-g09d2