From a0c9a2b4cc8c22d6238b0f31239c1e655f47730f Mon Sep 17 00:00:00 2001 From: faried nawaz Date: Wed, 21 Apr 2021 02:38:59 +0500 Subject: mix prune_objects: remove unused hashtags after pruning remote objects --- lib/mix/tasks/pleroma/database.ex | 11 +++++++++++ 1 file changed, 11 insertions(+) (limited to 'lib/mix/tasks') diff --git a/lib/mix/tasks/pleroma/database.ex b/lib/mix/tasks/pleroma/database.ex index e7f4b67a4..53ad58b64 100644 --- a/lib/mix/tasks/pleroma/database.ex +++ b/lib/mix/tasks/pleroma/database.ex @@ -96,6 +96,17 @@ defmodule Mix.Tasks.Pleroma.Database do ) |> Repo.delete_all(timeout: :infinity) + prune_hashtags_query = """ + delete from hashtags + where id in ( + select id from hashtags as ht + left join hashtags_objects as hto + on hto.hashtag_id = ht.id + where hto.hashtag_id is null) + """ + + Repo.query(prune_hashtags_query) + if Keyword.get(options, :vacuum) do Maintenance.vacuum("full") end -- cgit v1.2.3 From 5be9d139816fa40ff6227950b58f3c6cea01fc81 Mon Sep 17 00:00:00 2001 From: faried nawaz Date: Wed, 21 Apr 2021 03:52:32 +0500 Subject: a better query to delete from hashtags old query: Delete on hashtags (cost=5089.81..5521.63 rows=6160 width=18) -> Hash Semi Join (cost=5089.81..5521.63 rows=6160 width=18) Hash Cond: (hashtags.id = ht.id) -> Seq Scan on hashtags (cost=0.00..317.28 rows=17528 width=14) -> Hash (cost=5012.81..5012.81 rows=6160 width=20) -> Merge Anti Join (cost=0.70..5012.81 rows=6160 width=20) Merge Cond: (ht.id = hto.hashtag_id) -> Index Scan using hashtags_pkey on hashtags ht (cost=0.29..610.53 rows=17528 width=14) -> Index Scan using hashtags_objects_pkey on hashtags_objects hto (cost=0.42..3506.48 rows=68158 width=14) new query: Delete on hashtags ht (cost=0.70..5012.81 rows=6160 width=12) -> Merge Anti Join (cost=0.70..5012.81 rows=6160 width=12) Merge Cond: (ht.id = hto.hashtag_id) -> Index Scan using hashtags_pkey on hashtags ht (cost=0.29..610.53 rows=17528 width=14) -> Index Scan using hashtags_objects_pkey on hashtags_objects hto (cost=0.42..3506.48 rows=68158 width=14) --- lib/mix/tasks/pleroma/database.ex | 10 ++++------ 1 file changed, 4 insertions(+), 6 deletions(-) (limited to 'lib/mix/tasks') diff --git a/lib/mix/tasks/pleroma/database.ex b/lib/mix/tasks/pleroma/database.ex index 53ad58b64..bcde07774 100644 --- a/lib/mix/tasks/pleroma/database.ex +++ b/lib/mix/tasks/pleroma/database.ex @@ -97,12 +97,10 @@ defmodule Mix.Tasks.Pleroma.Database do |> Repo.delete_all(timeout: :infinity) prune_hashtags_query = """ - delete from hashtags - where id in ( - select id from hashtags as ht - left join hashtags_objects as hto - on hto.hashtag_id = ht.id - where hto.hashtag_id is null) + delete from hashtags as ht + where not exists ( + select 1 from hashtags_objects hto + where ht.id = hto.hashtag_id) """ Repo.query(prune_hashtags_query) -- cgit v1.2.3 From bc51dea4257d4faaff70f8511dcd3702489ebb74 Mon Sep 17 00:00:00 2001 From: feld Date: Mon, 7 Jun 2021 20:02:28 +0000 Subject: Update lib/mix/tasks/pleroma/database.ex --- lib/mix/tasks/pleroma/database.ex | 8 ++++---- 1 file changed, 4 insertions(+), 4 deletions(-) (limited to 'lib/mix/tasks') diff --git a/lib/mix/tasks/pleroma/database.ex b/lib/mix/tasks/pleroma/database.ex index bcde07774..57f73d12b 100644 --- a/lib/mix/tasks/pleroma/database.ex +++ b/lib/mix/tasks/pleroma/database.ex @@ -97,10 +97,10 @@ defmodule Mix.Tasks.Pleroma.Database do |> Repo.delete_all(timeout: :infinity) prune_hashtags_query = """ - delete from hashtags as ht - where not exists ( - select 1 from hashtags_objects hto - where ht.id = hto.hashtag_id) + DELETE FROM hashtags AS ht + WHERE NOT EXISTS ( + SELECT 1 FROM hashtags_objects hto + WHERE ht.id = hto.hashtag_id) """ Repo.query(prune_hashtags_query) -- cgit v1.2.3