From 4aad093c8434742c4548b24854b920f1a08b9de7 Mon Sep 17 00:00:00 2001 From: Scott Ostler Date: Sun, 22 Aug 2010 01:34:06 -0400 Subject: updated userlog query --- docs/userlog-query.txt | 100 ++++++++++++++++++++++++++++++++----------------- 1 file 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) -- cgit v1.2.3-70-g09d2