updated userlog query

1 files changed, 65 insertions, 35 deletions

diff --git a/docs/userlog-query.txt b/docs/userlog-query.txt
index 0c747c2..6781772 100644
--- a/docs/userlog-query.txt
+++ b/docs/userlog-query.txt
@@ -1,44 +1,74 @@
-;; 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.

+Query 1 (USER_ID):

 

 explain analyze SELECT

-            m.content, m.message_id, m.created_on,

-            u.nick, u.avatar, r.key,

-            array_to_string(ARRAY(SELECT nick || ' ' || tag

+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 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;

+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 PLAN

+Query 2 (NICK):

 

----------------------------------------------------------------------------------------------------------------------------------------------------------------

- 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)

+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.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)

+                 ->  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=7278)

+                       ->  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: 576.155 ms

-(24 rows)

+ Total runtime: 532.757 ms

+(19 rows)