migrations/2023-07-04-153335_add_optimized_indexes/up.sql

   1 -- Create an admin person index
   2 CREATE INDEX IF NOT EXISTS idx_person_admin ON person (admin);
   3
   4 -- Compound indexes, using featured_, then the other sorts, proved to be much faster
   5 -- Drop the old indexes
   6 DROP INDEX idx_post_aggregates_score;
   7
   8 DROP INDEX idx_post_aggregates_published;
   9
  10 DROP INDEX idx_post_aggregates_newest_comment_time;
  11
  12 DROP INDEX idx_post_aggregates_newest_comment_time_necro;
  13
  14 DROP INDEX idx_post_aggregates_featured_community;
  15
  16 DROP INDEX idx_post_aggregates_featured_local;
  17
  18 DROP INDEX idx_post_aggregates_hot;
  19
  20 DROP INDEX idx_post_aggregates_active;
  21
  22 -- featured_local
  23 CREATE INDEX idx_post_aggregates_featured_local_score ON post_aggregates (featured_local DESC, score DESC);
  24
  25 CREATE INDEX idx_post_aggregates_featured_local_newest_comment_time ON post_aggregates (featured_local DESC, newest_comment_time DESC);
  26
  27 CREATE INDEX idx_post_aggregates_featured_local_newest_comment_time_necro ON post_aggregates (featured_local DESC, newest_comment_time_necro DESC);
  28
  29 CREATE INDEX idx_post_aggregates_featured_local_hot ON post_aggregates (featured_local DESC, hot_rank DESC);
  30
  31 CREATE INDEX idx_post_aggregates_featured_local_active ON post_aggregates (featured_local DESC, hot_rank_active DESC);
  32
  33 CREATE INDEX idx_post_aggregates_featured_local_published ON post_aggregates (featured_local DESC, published DESC);
  34
  35 CREATE INDEX idx_post_aggregates_published ON post_aggregates (published DESC);
  36
  37 -- featured_community
  38 CREATE INDEX idx_post_aggregates_featured_community_score ON post_aggregates (featured_community DESC, score DESC);
  39
  40 CREATE INDEX idx_post_aggregates_featured_community_newest_comment_time ON post_aggregates (featured_community DESC, newest_comment_time DESC);
  41
  42 CREATE INDEX idx_post_aggregates_featured_community_newest_comment_time_necro ON post_aggregates (featured_community DESC, newest_comment_time_necro DESC);
  43
  44 CREATE INDEX idx_post_aggregates_featured_community_hot ON post_aggregates (featured_community DESC, hot_rank DESC);
  45
  46 CREATE INDEX idx_post_aggregates_featured_community_active ON post_aggregates (featured_community DESC, hot_rank_active DESC);
  47
  48 CREATE INDEX idx_post_aggregates_featured_community_published ON post_aggregates (featured_community DESC, published DESC);
  49