about summary refs log tree commit diff
path: root/users/Profpatsch
diff options
context:
space:
mode:
authorProfpatsch <mail@profpatsch.de>2024-05-16T18·47+0200
committerclbot <clbot@tvl.fyi>2024-06-03T14·55+0000
commit87d806c20f3a831330c828beba980a1f01f56e99 (patch)
treea8856f500c41244f468247d654598fd067ff14ed /users/Profpatsch
parentb54ad3e5806b60096c40830d08bd6a813703e74e (diff)
fix(users/Profpatsch/whatcd-resolver): store seeding_weight r/8201
Generated columns!!

This reduces the query time for distinct on somewhat!

Change-Id: Ic004d327b719a9f44a224ec87709992ce3d8f160
Reviewed-on: https://cl.tvl.fyi/c/depot/+/11676
Autosubmit: Profpatsch <mail@profpatsch.de>
Tested-by: BuildkiteCI
Reviewed-by: Profpatsch <mail@profpatsch.de>
Diffstat (limited to 'users/Profpatsch')
-rw-r--r--users/Profpatsch/whatcd-resolver/src/WhatcdResolver.hs7
1 files changed, 4 insertions, 3 deletions
diff --git a/users/Profpatsch/whatcd-resolver/src/WhatcdResolver.hs b/users/Profpatsch/whatcd-resolver/src/WhatcdResolver.hs
index fe6a36bacaf9..122ccc11df2d 100644
--- a/users/Profpatsch/whatcd-resolver/src/WhatcdResolver.hs
+++ b/users/Profpatsch/whatcd-resolver/src/WhatcdResolver.hs
@@ -652,6 +652,9 @@ migrate = inSpan "Database Migration" $ do
     END;
     $$ LANGUAGE plpgsql IMMUTABLE;
 
+    ALTER TABLE redacted.torrents_json
+    ADD COLUMN IF NOT EXISTS seeding_weight int GENERATED ALWAYS AS (calc_seeding_weight(full_json_result)) STORED;
+
     -- inflect out values of the full json
     CREATE OR REPLACE VIEW redacted.torrents AS
     SELECT
@@ -659,14 +662,12 @@ migrate = inSpan "Database Migration" $ do
       t.torrent_id,
       t.torrent_group,
       -- the seeding weight is used to find the best torrent in a group.
-      calc_seeding_weight(t.full_json_result) AS seeding_weight,
+      t.seeding_weight,
       t.full_json_result,
       t.torrent_file,
       t.transmission_torrent_hash
     FROM redacted.torrents_json t;
 
-    -- make sure we store the results
-    CREATE INDEX IF NOT EXISTS redacted_torrents_json_seeding_weight ON redacted.torrents_json (calc_seeding_weight(full_json_result));
 
     CREATE INDEX IF NOT EXISTS torrents_json_seeding ON redacted.torrents_json(((full_json_result->'seeding')::integer));
     CREATE INDEX IF NOT EXISTS torrents_json_snatches ON redacted.torrents_json(((full_json_result->'snatches')::integer));