From: | Alexander Farber <alexander(dot)farber(at)gmail(dot)com> |
---|---|
To: | |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Deleting takes days, should I add some index? |
Date: | 2021-02-25 21:33:17 |
Message-ID: | CAADeyWjKZ_o4BfjyEuQ-RV=bZi5bvZnUcthiFT-aYXrVFW+=+w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Thank you, Pavel!
I didn't even think about trying to "explain analyze" deletion of just 1
record -
On Thu, Feb 25, 2021 at 10:04 PM Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
wrote:
> čt 25. 2. 2021 v 19:39 odesílatel Alexander Farber <
>> alexander(dot)farber(at)gmail(dot)com> napsal:
>>
>>> The question is why does the command take days (when I tried last time):
>>> delete from words_games where created < now() - interval '12 month';
>>>
>>>
>> postgres=# explain analyze delete from words_games where gid = 44877;
>>
>> create index on words_scores(mid);
>>
>
I have also added:
create index on words_puzzles(mid);
and then the result if finally good enough for my nightly cronjob:
explain analyze delete from words_games where created < now() - interval
'12 month';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Delete on words_games (cost=0.00..49802.33 rows=104022 width=6) (actual
time=2121.475..2121.476 rows=0 loops=1)
-> Seq Scan on words_games (cost=0.00..49802.33 rows=104022 width=6)
(actual time=0.006..85.908 rows=103166 loops=1)
Filter: (created < (now() - '1 year'::interval))
Rows Removed by Filter: 126452
Planning Time: 0.035 ms
Trigger for constraint words_chat_gid_fkey on words_games: time=598.444
calls=103166
Trigger for constraint words_moves_gid_fkey on words_games: time=83745.244
calls=103166
Trigger for constraint words_scores_gid_fkey on words_games:
time=30638.420 calls=103166
Trigger for constraint words_puzzles_mid_fkey on words_moves:
time=15426.679 calls=3544242
Trigger for constraint words_scores_mid_fkey on words_moves:
time=18546.115 calls=3544242
Execution Time: 151427.183 ms
(11 rows)
There is one detail I don't understand in the output of "explain analyze" -
why do the lines
"Trigger for constraint words_scores_mid_fkey on words_moves: time=1885.372
calls=4"
completely disappear after adding the index? Are those the "ON DELETE
CASCADE" triggers?
Aren't they called after the index has been added?
Best regards
Alex
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2021-02-25 21:40:58 | Re: Deleting takes days, should I add some index? |
Previous Message | Pavel Stehule | 2021-02-25 21:04:13 | Re: Deleting takes days, should I add some index? |