Re: Deleting takes days, should I add some index?

čt 25. 2. 2021 v 22:33 odesílatel Alexander Farber <
alexander(dot)farber(at)gmail(dot)com> napsal:

> 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?
>

these triggers are RI triggers

>
> Aren't they called after the index has been added?
>

it should be called every time

Pavel

> Best regards
> Alex
>