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

[Pavel Stehule <pavel.stehule@xxxxxxxxx>]

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

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