čt 25. 2. 2021 v 22:33 odesílatel Alexander Farber < alexander.far...@gmail.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.steh...@gmail.com> > wrote: > >> čt 25. 2. 2021 v 19:39 odesílatel Alexander Farber < >>> alexander.far...@gmail.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 >
