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); Regards Pavel
