Changed scores and directory update freq, add msgs index to 0-create.psql, change server startup order

2 files changed, 85 insertions, 29 deletions

diff --git a/docs/userlog-query.txt b/docs/userlog-query.txt
index 6781772..a9effb5 100644
--- a/docs/userlog-query.txt
+++ b/docs/userlog-query.txt
@@ -1,4 +1,5 @@
-Query 1 (USER_ID):

+Query 1 (USER_ID)

+------------------------------------------

 

 explain analyze SELECT

 m.content, m.message_id, m.created_on, u.nick, u.avatar,

@@ -12,27 +13,8 @@ where
  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;

-

+LIMIT 40 offset 1000;

 

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

@@ -50,8 +32,23 @@ QUERY 1 (USER_ID)
 (14 rows)

 

 

-QUERY 2 (NICK)

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

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

+

  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

@@ -72,3 +69,64 @@ QUERY 2 (NICK)
                              Index Cond: (users.user_id = tags.user_id)

  Total runtime: 532.757 ms

 (19 rows)

+

+

+QUERY 3 (ROOM JOIN)

+----------------

+

+2010-08-23 02:27:30 EDT LOG:  duration: 10817.570 ms  execute <unnamed>:

+

+explain analyze SELECT

+            m.content, m.message_id, m.created_on,

+            u.nick, u.avatar,

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

+              FROM tags

+              WHERE message_id = m.message_id AND tags.user_id = u.user_id), ' ') as tags

+          FROM users u, messages m

+          WHERE room_id = 2

+            AND m.user_id = u.user_id  ORDER BY created_on DESC

+          LIMIT 200 OFFSET 0;

+

+select 

+

+PLAN

+----

+

+ Limit  (cost=115535.24..115535.74 rows=200 width=157) (actual time=7835.228..7835.592 rows=200 loops=1)

+   ->  Sort  (cost=115535.24..115546.44 rows=4477 width=157) (actual time=7835.224..7835.356 rows=200 loops=1)

+         Sort Key: m.created_on

+         Sort Method:  top-N heapsort  Memory: 58kB

+         ->  Hash Join  (cost=354.59..115341.75 rows=4477 width=157) (actual time=256.324..7807.477 rows=13581 loops=1)

+               Hash Cond: (m.user_id = u.user_id)

+               ->  Index Scan using messages_room_id_idx on messages m  (cost=0.00..2970.23 rows=4477 width=80) (actual time=7.260..5647.945 rows=13581 loops=1)

+                     Index Cond: (room_id = 2)

+               ->  Hash  (cost=262.04..262.04 rows=7404 width=85) (actual time=237.419..237.419 rows=5675 loops=1)

+                     ->  Seq Scan on users u  (cost=0.00..262.04 rows=7404 width=85) (actual time=0.208..227.609 rows=5675 loops=1)

+               SubPlan 1

+                 ->  Nested Loop  (cost=0.00..25.00 rows=2 width=18) (actual time=0.126..0.135 rows=0 loops=13581)

+                       ->  Index Scan using tags_message_id_idx on tags  (cost=0.00..8.43 rows=2 width=13) (actual time=0.122..0.131 rows=0 loops=13581)

+                             Index Cond: (message_id = $0)

+                       ->  Index Scan using users_pkey on users  (cost=0.00..8.27 rows=1 width=13) (actual time=0.010..0.011 rows=1 loops=243)

+                             Index Cond: (users.user_id = tags.user_id)

+ Total runtime: 7837.031 ms

+(17 rows)

+

+

+QUERY 4 (FEED_IMAGES)

+------------------------

+

+explain analyze SELECT * FROM feed_images

+WHERE room = 'boys' AND message_id IS NULL

+ORDER BY queued_on ASC LIMIT 1;

+

+ Limit  (cost=3966.33..3966.33 rows=1 width=231) (actual time=8779.090..8779.093 rows=1 loops=1)

+   ->  Sort  (cost=3966.33..3966.33 rows=1 width=231) (actual time=8779.084..8779.084 rows=1 loops=1)

+         Sort Key: queued_on

+         Sort Method:  top-N heapsort  Memory: 17kB

+         ->  Bitmap Heap Scan on feed_images  (cost=921.32..3966.32 rows=1 width=231) (actual time=8772.479..8772.497 rows=7 loops=1)

+               Recheck Cond: (room = 'boys'::text)

+               Filter: (message_id IS NULL)

+               ->  Bitmap Index Scan on feed_images_pkey  (cost=0.00..921.32 rows=12400 width=0) (actual time=5878.657..5878.657 rows=13408 loops=1)

+                     Index Cond: (room = 'boys'::text)

+ Total runtime: 8779.219 ms

+(10 rows)

diff --git a/src/site.clj b/src/site.clj
index 731e196..92f6eb9 100644
--- a/src/site.clj
+++ b/src/site.clj
@@ -340,9 +340,7 @@ ORDER BY cnt DESC
 (def *user-info-query* "
 SELECT u.nick,
        u.user_id,
-       u.avatar,
-       LAST.content,
-       LAST.message_id
+       u.avatar
 FROM   users u
        LEFT JOIN messages LAST
          ON u.user_id = LAST.user_id
@@ -363,8 +361,8 @@ FROM   users u
     {:list res
      :map  (zipmap (map :nick res) (map :cnt res))}))
 
-(def *scores-refresh-period-sec* (* 30 60))
-(def *user-info-refresh-period-sec* 300)
+(def *scores-refresh-period-sec* (* 29 60))
+(def *user-info-refresh-period-sec* (* 31 60))
 
 (def *user-scores*
      (scheduled-agent build-score-list
@@ -523,7 +521,7 @@ FROM   users u
 	  profile-nick  (:nick user-info) ; Update to get right casing
 	  nick          (session :nick)
 	  logger        (make-time-logger)
-	  has-avatar    (non-empty-string? (user-info :avatar))
+	  has-avatar    (non-empty-string? (:avatar user-info))
 	  offset        (maybe-parse-int offset 0)
 	  dump-offset   (* offset *dumps-per-page*)
 	  raw-dumps     (logger tags/fetch-dumps-by-nick