From c5555f25da1bd836f4a98cda0905d66e2c6aeb91 Mon Sep 17 00:00:00 2001 From: Profpatsch Date: Thu, 16 May 2024 21:48:18 +0200 Subject: fix(users/Profpatsch/whatcd-resolver): speed up table query This took a while to figure out, but essentially sorting a thing with one million queries takes a long time, as compared to doing it on a small subset of things and then joining against the final ordering. The generated column helps, too. Change-Id: I1bf283e2be060748eebda92576e3d062c51a6777 Reviewed-on: https://cl.tvl.fyi/c/depot/+/11681 Autosubmit: Profpatsch Tested-by: BuildkiteCI Reviewed-by: Profpatsch --- users/Profpatsch/whatcd-resolver/src/Redacted.hs | 50 +++++++++++------------- 1 file changed, 23 insertions(+), 27 deletions(-) diff --git a/users/Profpatsch/whatcd-resolver/src/Redacted.hs b/users/Profpatsch/whatcd-resolver/src/Redacted.hs index 0c16c70c2148..1363d1677231 100644 --- a/users/Profpatsch/whatcd-resolver/src/Redacted.hs +++ b/users/Profpatsch/whatcd-resolver/src/Redacted.hs @@ -400,34 +400,30 @@ getBestTorrents :: getBestTorrents opts = do queryWith [sql| + WITH filtered_torrents AS ( + SELECT DISTINCT ON (torrent_group) + id + FROM + redacted.torrents + WHERE + -- onlyDownloaded + ((NOT ?::bool) OR torrent_file IS NOT NULL) + -- filter by artist id + AND + (?::bool OR (to_jsonb(?::int) <@ (jsonb_path_query_array(full_json_result, '$.artists[*].id')))) + ORDER BY torrent_group, seeding_weight DESC + ) SELECT - group_id, - torrent_id, - seeding_weight, - torrent_json, - torrent_group_json, - has_torrent_file, - transmission_torrent_hash - FROM ( - SELECT DISTINCT ON (tg.group_id) - tg.group_id, - t.torrent_id, - seeding_weight, - t.full_json_result AS torrent_json, - tg.full_json_result AS torrent_group_json, - t.torrent_file IS NOT NULL as has_torrent_file, - t.transmission_torrent_hash, - (jsonb_path_query_array(t.full_json_result, '$.artists[*].id')) as torrent_artists - FROM redacted.torrents t - JOIN redacted.torrent_groups tg ON tg.id = t.torrent_group - ORDER BY tg.group_id, seeding_weight DESC - ) as _ - WHERE - -- onlyDownloaded - ((NOT ?::bool) OR has_torrent_file) - -- filter by artist id - AND - (?::bool OR (to_jsonb(?::int) <@ torrent_artists)) + tg.group_id, + t.torrent_id, + t.seeding_weight, + t.full_json_result AS torrent_json, + tg.full_json_result AS torrent_group_json, + t.torrent_file IS NOT NULL AS has_torrent_file, + t.transmission_torrent_hash + FROM filtered_torrents f + JOIN redacted.torrents t ON t.id = f.id + JOIN redacted.torrent_groups tg ON tg.id = t.torrent_group ORDER BY seeding_weight DESC |] ( do -- cgit 1.4.1