čt 25. 2. 2021 v 22:02 odesílatel Pavel Stehule <pavel.steh...@gmail.com> napsal:
> Hi > > čt 25. 2. 2021 v 19:39 odesílatel Alexander Farber < > alexander.far...@gmail.com> napsal: > >> Pavel, thank you for asking! >> >> I have put the anonymized dump of my database at: >> >> http://wordsbyfarber.com/words_dev.sql.gz (beware, it is a 1.3 GB >> download) >> >> The question is why does the command take days (when I tried last time): >> >> delete from words_games where created < now() - interval '12 month'; >> >> > I tried to remove just only one row > > postgres=# explain analyze delete from words_games where gid = 44877; > > ┌────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ > │ QUERY PLAN > │ > > ╞════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡ > │ Delete on words_games (cost=0.42..8.44 rows=0 width=0) (actual > time=0.268..0.270 rows=0 loops=1) │ > │ -> Index Scan using words_games_pkey on words_games (cost=0.42..8.44 > rows=1 width=6) (actual time=0.205..0.209 rows=1 loops=1) │ > │ Index Cond: (gid = 44877) > │ > │ Planning Time: 0.328 ms > │ > │ Trigger for constraint words_chat_gid_fkey on words_games: time=0.215 > calls=1 │ > │ Trigger for constraint words_moves_gid_fkey on words_games: time=0.240 > calls=1 │ > │ Trigger for constraint words_scores_gid_fkey on words_games: time=0.103 > calls=1 │ > │ Trigger for constraint words_puzzles_mid_fkey on words_moves: > time=56.099 calls=4 │ > │ Trigger for constraint words_scores_mid_fkey on words_moves: > time=22536.280 calls=4 │ > │ Execution Time: 22593.293 ms > │ > > └────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ > (10 rows) > > looks so you miss index > > create index on words_scores(mid); > postgres=# explain analyze delete from words_games where gid = 183154; ┌────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ QUERY PLAN │ ╞════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡ │ Delete on words_games (cost=0.42..8.44 rows=0 width=0) (actual time=0.369..0.371 rows=0 loops=1) │ │ -> Index Scan using words_games_pkey on words_games (cost=0.42..8.44 rows=1 width=6) (actual time=0.283..0.288 rows=1 loops=1) │ │ Index Cond: (gid = 183154) │ │ Planning Time: 0.230 ms │ │ Trigger for constraint words_chat_gid_fkey on words_games: time=0.131 calls=1 │ │ Trigger for constraint words_moves_gid_fkey on words_games: time=1.329 calls=1 │ │ Trigger for constraint words_scores_gid_fkey on words_games: time=1.704 calls=1 │ │ Trigger for constraint words_puzzles_mid_fkey on words_moves: time=4.068 calls=51 │ │ Trigger for constraint words_scores_mid_fkey on words_moves: time=5.304 calls=51 │ │ Execution Time: 13.037 ms │ └────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ (10 rows) Regards Pavel > Regards > > Pavel > >
