diff options
| author | dumpfmprod <dumpfmprod@ubuntu.(none)> | 2010-08-22 19:49:11 -0400 |
|---|---|---|
| committer | dumpfmprod <dumpfmprod@ubuntu.(none)> | 2010-08-22 19:49:11 -0400 |
| commit | 07c94d40b8051849f362cc8475e6fec12eddd4f0 (patch) | |
| tree | 52f3c55963891c1d4de002eb45acb5240fbc6a5f | |
| parent | 0791d4f2c2152c4e88ba6c4527fe354667cacf45 (diff) | |
| parent | 40683eda906135fb1880ead890a0fcba7c256347 (diff) | |
Merge branch 'master' of /pichat/repo
| -rw-r--r-- | docs/userlog-query.txt | 74 | ||||
| -rw-r--r-- | src/tags.clj | 2 |
2 files changed, 75 insertions, 1 deletions
diff --git a/docs/userlog-query.txt b/docs/userlog-query.txt new file mode 100644 index 0000000..6781772 --- /dev/null +++ b/docs/userlog-query.txt @@ -0,0 +1,74 @@ +Query 1 (USER_ID):
+
+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 2 (NICK):
+
+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.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=7190)
+ Index Cond: (users.user_id = tags.user_id)
+ Total runtime: 532.757 ms
+(19 rows)
diff --git a/src/tags.clj b/src/tags.clj index b6642d3..b95f824 100644 --- a/src/tags.clj +++ b/src/tags.clj @@ -146,7 +146,7 @@ WHERE EXISTS 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 = ? + WHERE m.user_id = u.user_id AND u.user_id = (select user_id from users where lower(nick) = lower(?)) AND r.room_id = m.room_id AND r.admin_only = false " (if image-only "AND m.is_image = true " "") " ORDER BY created_on DESC |