mirror of
https://github.com/LemmyNet/lemmy.git
synced 2025-09-02 11:13:51 +00:00
644a448aa9
* [0.19] Fixing active counts slow queries. (#5907) * Fixing active counts slow queries. * Simplify back to str tuple * Batch site and community updates * Using update from temp table * Making aggs temp table use interval name. * Make dev setup use optimized postgres. * Addressing PR comments. * Use ref * Removing system custom info from customPostgresql.conf * Forgot to remove old scheduled tasks. * Making sure migrations aren't missing anything from release/v0.19 Checked using git diff --diff-filter D --stat release/v0.19 migrations * Rename all migrations to come after release/v0.19 migrations * Add liked_at is not null check.
61 lines
2.2 KiB
SQL
61 lines
2.2 KiB
SQL
-- Taglines
|
|
CREATE INDEX idx_tagline_published_id ON tagline (published DESC, id DESC);
|
|
|
|
-- Some for the vote views
|
|
CREATE INDEX idx_comment_actions_like_score ON comment_actions (comment_id, like_score, person_id)
|
|
WHERE
|
|
like_score IS NOT NULL;
|
|
|
|
CREATE INDEX idx_post_actions_like_score ON post_actions (post_id, like_score, person_id)
|
|
WHERE
|
|
like_score IS NOT NULL;
|
|
|
|
-- Fixing the community sorts for an id tie-breaker
|
|
DROP INDEX idx_community_lower_name;
|
|
|
|
DROP INDEX idx_community_hot;
|
|
|
|
DROP INDEX idx_community_published;
|
|
|
|
DROP INDEX idx_community_subscribers;
|
|
|
|
DROP INDEX idx_community_title;
|
|
|
|
DROP INDEX idx_community_users_active_month;
|
|
|
|
CREATE INDEX idx_community_lower_name ON community USING btree (lower((name)::text) DESC, id DESC);
|
|
|
|
CREATE INDEX idx_community_hot ON community USING btree (hot_rank DESC, id DESC);
|
|
|
|
CREATE INDEX idx_community_published ON community USING btree (published DESC, id DESC);
|
|
|
|
CREATE INDEX idx_community_subscribers ON community USING btree (subscribers DESC, id DESC);
|
|
|
|
CREATE INDEX idx_community_title ON community USING btree (title DESC, id DESC);
|
|
|
|
CREATE INDEX idx_community_users_active_month ON community USING btree (users_active_month DESC, id DESC);
|
|
|
|
-- Create a few missing ones
|
|
CREATE INDEX idx_community_users_active_half_year ON community USING btree (users_active_half_year DESC, id DESC);
|
|
|
|
CREATE INDEX idx_community_users_active_week ON community USING btree (users_active_week DESC, id DESC);
|
|
|
|
CREATE INDEX idx_community_users_active_day ON community USING btree (users_active_day DESC, id DESC);
|
|
|
|
CREATE INDEX idx_community_subscribers_local ON community USING btree (subscribers_local DESC, id DESC);
|
|
|
|
CREATE INDEX idx_community_comments ON community USING btree (comments DESC, id DESC);
|
|
|
|
CREATE INDEX idx_community_posts ON community USING btree (posts DESC, id DESC);
|
|
|
|
-- Fix the post reverse_timestamp key sorts.
|
|
DROP INDEX idx_post_featured_community_published_asc;
|
|
|
|
DROP INDEX idx_post_featured_local_published_asc;
|
|
|
|
DROP INDEX idx_post_published_asc;
|
|
|
|
CREATE INDEX idx_post_featured_community_published ON post USING btree (community_id, featured_community DESC, published DESC, id DESC);
|
|
|
|
CREATE INDEX idx_post_community_published ON post USING btree (community_id, published DESC, id DESC);
|
|
|