-- Fix slow and frequent topic listing query (5 seconds down to 4ms)
CREATE INDEX CONCURRENTLY phpbb_topics_rod2_idx ON phpbb_topics(forum_id, topic_type, topic_last_post_id, topic_first_post_id);


-- Slow User Login? Unlike the Contrib module citext, the phpbb case insensitive index cannot be
-- applied by default due to the exposed type coercions. Implemented by SQL function?
CREATE INDEX CONCURRENTLY phpbb3_search_wordmatch_rod1_idx ON phpbb3_users(lower(username_clean::text));

-- Birthday checks are NOT indexed in PostgreSQL. There is an index on the structure but it is a standard
-- BTree which is not suitable for pattern queries (regular expressions or LIKE).
-- The below makes the birthday queries indexed. Perhaps the other index on this column can be removed?
CREATE INDEX CONCURRENTLY phpbb3_users_user_birthday_rod1_idx ON phpbb3_users(user_birthday varchar_pattern_ops);


-- Slow word searches. Query gets all of word and orders by post_id ascending.
-- Build index in this form (30seconds to 1 minute down to 20ms or less)
-- Clustering the structure takes it from 20ms down to 3ms for most searches.
-- 
-- Last step "CLUSTER" may take a very long time to execute and while it does not
-- block searches it does require maintenance. Probably not something to do by default
-- but it may help people with a few million posts in their forum. PostgreSQL 9.0 reduces
-- overhead of cluster maintenance somewhat.
CREATE INDEX CONCURRENTLY phpbb3_search_wordmatch_rod1_idx ON phpbb_search_wordmatch(word_id, post_id);
DROP INDEX word_id_phpbb_search_wordmatch_;

ALTER TABLE phpbb_search_wordmatch CLUSTER ON phpbb3_search_wordmatch_rod1_idx;
CLUSTER phpbb_search_wordmatch;


-- Finally, PostgreSQL doesn't do so well with a partial sort.
-- The first query causes it to lookup all records then sort then all by 
-- topic_last_post_time, the apply the LIMIT.
--
-- Removing DISTINCT works, or separating into two phases (one to read and limit,
-- and the second to apply DISTINCT works. This second form is shown below.
-- Again, a 2 to 3 second query now runs under 10ms.

-- Change
SELECT DISTINCT t.topic_last_post_time, p.topic_id
          FROM phpbb3_posts p, phpbb3_topics t
         WHERE t.topic_replies = 0
           AND t.topic_moved_id = 0
           AND p.topic_id = t.topic_id
           AND p.post_approved = 1
           AND p.forum_id NOT IN (105, 106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117, 118)
      ORDER BY t.topic_last_post_time DESC
         LIMIT 1001 OFFSET 0
-- to
SELECT DISTINCT topic_last_post_time, topic_id
  FROM (SELECT t.topic_last_post_time, p.topic_id
          FROM phpbb3_posts p, phpbb3_topics t
         WHERE t.topic_replies = 0
           AND t.topic_moved_id = 0
           AND p.topic_id = t.topic_id
           AND p.post_approved = 1
           AND p.forum_id NOT IN (105, 106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117, 118)
      ORDER BY t.topic_last_post_time DESC
         LIMIT 1001 OFFSET 0
       ) AS limited_topics
ORDER BY topic_last_post_time DESC;