mirror of
https://github.com/LemmyNet/lemmy.git
synced 2025-09-02 19:23:49 +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.
20 lines
924 B
SQL
20 lines
924 B
SQL
-- * inclusive bounds of `smallint` range from https://www.postgresql.org/docs/17/datatype-numeric.html
|
|
-- * built-in `random` function has `VOLATILE` and `PARALLEL RESTRICTED` according to:
|
|
-- * https://www.postgresql.org/docs/current/parallel-safety.html#PARALLEL-LABELING
|
|
-- * https://www.postgresql.org/docs/17/xfunc-volatility.html
|
|
CREATE FUNCTION random_smallint ()
|
|
RETURNS smallint
|
|
LANGUAGE sql
|
|
VOLATILE PARALLEL RESTRICTED RETURN
|
|
-- https://stackoverflow.com/questions/1400505/generate-a-random-number-in-the-range-1-10/1400752#1400752
|
|
-- (65536 = exclusive upper bound - inclusive lower bound)
|
|
trunc ((random() * (65536)) - 32768
|
|
);
|
|
|
|
ALTER TABLE community
|
|
ADD COLUMN random_number smallint NOT NULL DEFAULT random_smallint ();
|
|
|
|
CREATE INDEX idx_community_random_number ON community (random_number) INCLUDE (local, nsfw)
|
|
WHERE
|
|
NOT (deleted OR removed OR visibility = 'Private');
|
|
|