mirror of
https://github.com/LemmyNet/lemmy.git
synced 2025-09-03 11:43: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.
84 lines
2.4 KiB
SQL
84 lines
2.4 KiB
SQL
-- Creates combined tables for
|
|
-- Search: (post, comment, community, person)
|
|
-- Add published to person_aggregates (it was missing for some reason)
|
|
ALTER TABLE person_aggregates
|
|
ADD COLUMN published timestamptz NOT NULL DEFAULT now();
|
|
|
|
UPDATE
|
|
person_aggregates pa
|
|
SET
|
|
published = p.published
|
|
FROM
|
|
person p
|
|
WHERE
|
|
pa.person_id = p.id;
|
|
|
|
-- score is used for the top sort
|
|
-- For persons: its post score
|
|
-- For comments: score,
|
|
-- For posts: score,
|
|
-- For community: users active monthly
|
|
-- Updating the history
|
|
CREATE TABLE search_combined AS
|
|
SELECT
|
|
published,
|
|
score::int,
|
|
post_id,
|
|
NULL::int AS comment_id,
|
|
NULL::int AS community_id,
|
|
NULL::int AS person_id
|
|
FROM
|
|
post_aggregates
|
|
UNION ALL
|
|
SELECT
|
|
published,
|
|
score::int,
|
|
NULL::int,
|
|
comment_id,
|
|
NULL::int,
|
|
NULL::int
|
|
FROM
|
|
comment_aggregates
|
|
UNION ALL
|
|
SELECT
|
|
published,
|
|
users_active_month::int,
|
|
NULL::int,
|
|
NULL::int,
|
|
community_id,
|
|
NULL::int
|
|
FROM
|
|
community_aggregates
|
|
UNION ALL
|
|
SELECT
|
|
published,
|
|
post_score::int,
|
|
NULL::int,
|
|
NULL::int,
|
|
NULL::int,
|
|
person_id
|
|
FROM
|
|
person_aggregates;
|
|
|
|
-- Add the constraints
|
|
ALTER TABLE search_combined
|
|
ADD COLUMN id int PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
|
|
ALTER COLUMN published SET NOT NULL,
|
|
ALTER COLUMN score SET NOT NULL,
|
|
ALTER COLUMN score SET DEFAULT 0,
|
|
ADD CONSTRAINT search_combined_post_id_fkey FOREIGN KEY (post_id) REFERENCES post ON UPDATE CASCADE ON DELETE CASCADE,
|
|
ADD CONSTRAINT search_combined_comment_id_fkey FOREIGN KEY (comment_id) REFERENCES COMMENT ON UPDATE CASCADE ON DELETE CASCADE,
|
|
ADD CONSTRAINT search_combined_community_id_fkey FOREIGN KEY (community_id) REFERENCES community ON UPDATE CASCADE ON DELETE CASCADE,
|
|
ADD CONSTRAINT search_combined_person_id_fkey FOREIGN KEY (person_id) REFERENCES person ON UPDATE CASCADE ON DELETE CASCADE,
|
|
ADD UNIQUE (post_id),
|
|
ADD UNIQUE (comment_id),
|
|
ADD UNIQUE (community_id),
|
|
ADD UNIQUE (person_id),
|
|
ADD CONSTRAINT search_combined_check CHECK (num_nonnulls (post_id, comment_id, community_id, person_id) = 1);
|
|
|
|
CREATE INDEX idx_search_combined_published ON search_combined (published DESC, id DESC);
|
|
|
|
CREATE INDEX idx_search_combined_published_asc ON search_combined (reverse_timestamp_sort (published) DESC, id DESC);
|
|
|
|
CREATE INDEX idx_search_combined_score ON search_combined (score DESC, id DESC);
|
|
|