diff options
| author | faried nawaz <faried@gmail.com> | 2021-04-21 03:52:32 +0500 |
|---|---|---|
| committer | faried nawaz <faried@gmail.com> | 2021-05-08 02:00:43 +0500 |
| commit | 5be9d139816fa40ff6227950b58f3c6cea01fc81 (patch) | |
| tree | d8a12733bd1b6d383ce4ea3be05402b8dd637027 | |
| parent | a0c9a2b4cc8c22d6238b0f31239c1e655f47730f (diff) | |
| download | pleroma-5be9d139816fa40ff6227950b58f3c6cea01fc81.tar.gz | |
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)
| -rw-r--r-- | lib/mix/tasks/pleroma/database.ex | 10 |
1 files changed, 4 insertions, 6 deletions
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) |