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

[Alexander Farber <alexander.farber@xxxxxxxxx>]

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.stehule@xxxxxxxxx> wrote:

čt 25. 2. 2021 v 19:39 odesílatel Alexander Farber <alexander.farber@xxxxxxxxx> 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