čt 25. 2. 2021 v 14:06 odesílatel Alexander Farber <alexander.farber@xxxxxxxxx> napsal:
Hello, revisiting an older mail on the too long deletion times (in PostgreSQL 13.2)...I have followed the advices here, thank you -On Fri, Nov 27, 2020 at 4:15 PM Guillaume Lelarge <guillaume@xxxxxxxxxxxx> wrote:Le ven. 27 nov. 2020 à 16:05, Alvaro Herrera <alvherre@xxxxxxxxxxxxxx> a écrit :On 2020-Nov-27, Alexander Farber wrote:
> Referenced by:
> TABLE "words_chat" CONSTRAINT "words_chat_gid_fkey" FOREIGN KEY (gid)
> REFERENCES words_games(gid) ON DELETE CASCADE
> TABLE "words_moves" CONSTRAINT "words_moves_gid_fkey" FOREIGN KEY (gid)
> REFERENCES words_games(gid) ON DELETE CASCADE
> TABLE "words_scores" CONSTRAINT "words_scores_gid_fkey" FOREIGN KEY
> (gid) REFERENCES words_games(gid) ON DELETE CASCADE
Make sure you have indexes on the gid columns of these tables. Delete
needs to scan them in order to find the rows that are cascaded to.
An index on words_games(finished) and words_moves(played) would help too.and have now the following indices in my database:CREATE INDEX ON words_games(player1, COALESCE(finished, 'INFINITY'));CREATE INDEX ON words_games(player2, COALESCE(finished, 'INFINITY'));CREATE INDEX ON words_games(created),CREATE INDEX ON words_chat(created),CREATE INDEX ON words_moves(uid, action, played);CREATE INDEX ON words_moves(gid, played);CREATE INDEX ON words_moves(played);CREATE INDEX ON words_moves(uid);CREATE INDEX ON words_moves(gid);CREATE INDEX ON words_social(uid, stamp);CREATE INDEX ON words_geoip USING SPGIST (block);CREATE INDEX ON words_scores(LENGTH(word), mid);-- CREATE INDEX ON words_scores(uid, LENGTH(word) desc);CREATE INDEX ON words_scores(gid);CREATE INDEX ON words_scores(uid);CREATE INDEX ON words_chat(gid);
However the deletion still takes forever and I have to ctrl-c it:
# delete from words_games where created < now() - interval '12 month';
Do you please have any further suggestions?
When I try to prepend "explain analyze" to the above query, then in the production database it also lasts forever.In an empty dev database the output does not help much -# explain analyze delete from words_games where created < now() - interval '12 month';QUERY PLAN------------------------------------------------------------------------------------------------------------Delete on words_games (cost=0.00..40.34 rows=1 width=6) (actual time=0.132..0.132 rows=0 loops=1)-> Seq Scan on words_games (cost=0.00..40.34 rows=1 width=6) (actual time=0.131..0.131 rows=0 loops=1)Filter: (created < (now() - '1 year'::interval))Rows Removed by Filter: 137Planning Time: 0.150 msExecution Time: 0.143 ms(6 rows)
Postgres newer use index on small tables
DELETE can be slow due ref integrity check or triggers. You should check so all foreign keys have an index.
Regards
Pavel
Below are the words_games and the "referenced by" tables -# \d words_gamesTable "public.words_games"Column | Type | Collation | Nullable | Default----------+--------------------------+-----------+----------+------------------------------------------gid | integer | | not null | nextval('words_games_gid_seq'::regclass)created | timestamp with time zone | | not null |finished | timestamp with time zone | | |player1 | integer | | not null |player2 | integer | | |played1 | timestamp with time zone | | |played2 | timestamp with time zone | | |state1 | text | | |state2 | text | | |reason | text | | |hint1 | text | | |hint2 | text | | |score1 | integer | | not null |score2 | integer | | not null |chat1 | integer | | not null |chat2 | integer | | not null |hand1 | character(1)[] | | not null |hand2 | character(1)[] | | not null |pile | character(1)[] | | not null |letters | character(1)[] | | not null |values | integer[] | | not null |bid | integer | | not null |diff1 | integer | | |diff2 | integer | | |open1 | boolean | | not null | falseopen2 | boolean | | not null | falseIndexes:"words_games_pkey" PRIMARY KEY, btree (gid)"words_games_created_idx" btree (created)"words_games_player1_coalesce_idx" btree (player1, COALESCE(finished, 'infinity'::timestamp with time zone))"words_games_player2_coalesce_idx" btree (player2, COALESCE(finished, 'infinity'::timestamp with time zone))Check constraints:"words_games_chat1_check" CHECK (chat1 >= 0)"words_games_chat2_check" CHECK (chat2 >= 0)"words_games_check" CHECK (player1 <> player2)"words_games_score1_check" CHECK (score1 >= 0)"words_games_score2_check" CHECK (score2 >= 0)Foreign-key constraints:"words_games_bid_fkey" FOREIGN KEY (bid) REFERENCES words_boards(bid) ON DELETE CASCADE"words_games_player1_fkey" FOREIGN KEY (player1) REFERENCES words_users(uid) ON DELETE CASCADE"words_games_player2_fkey" FOREIGN KEY (player2) REFERENCES words_users(uid) ON DELETE CASCADEReferenced by:TABLE "words_chat" CONSTRAINT "words_chat_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid) ON DELETE CASCADETABLE "words_moves" CONSTRAINT "words_moves_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid) ON DELETE CASCADETABLE "words_scores" CONSTRAINT "words_scores_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid) ON DELETE CASCADE# \d words_movesTable "public.words_moves"Column | Type | Collation | Nullable | Default---------+--------------------------+-----------+----------+------------------------------------------mid | bigint | | not null | nextval('words_moves_mid_seq'::regclass)action | text | | not null |gid | integer | | not null |uid | integer | | not null |played | timestamp with time zone | | not null |tiles | jsonb | | |score | integer | | |str | text | | |hand | text | | |letters | character(1)[] | | |values | integer[] | | |Indexes:"words_moves_pkey" PRIMARY KEY, btree (mid)"words_moves_gid_idx" btree (gid)"words_moves_gid_played_idx" btree (gid, played DESC)"words_moves_played_idx" btree (played)"words_moves_uid_action_played_idx" btree (uid, action, played)"words_moves_uid_idx" btree (uid)Check constraints:"words_moves_score_check" CHECK (score >= 0)Foreign-key constraints:"words_moves_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid) ON DELETE CASCADE"words_moves_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON DELETE CASCADEReferenced by:TABLE "words_puzzles" CONSTRAINT "words_puzzles_mid_fkey" FOREIGN KEY (mid) REFERENCES words_moves(mid) ON DELETE CASCADETABLE "words_scores" CONSTRAINT "words_scores_mid_fkey" FOREIGN KEY (mid) REFERENCES words_moves(mid) ON DELETE CASCADE# \d words_scoresTable "public.words_scores"Column | Type | Collation | Nullable | Default--------+---------+-----------+----------+---------mid | bigint | | not null |gid | integer | | not null |uid | integer | | not null |word | text | | not null |score | integer | | not null |Indexes:"words_scores_gid_idx" btree (gid)"words_scores_length_mid_idx" btree (length(word) DESC, mid DESC)"words_scores_uid_idx" btree (uid)Check constraints:"words_scores_score_check" CHECK (score >= 0)"words_scores_word_check" CHECK (word ~ '^[А-Я]{2,}$'::text)Foreign-key constraints:"words_scores_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid) ON DELETE CASCADE"words_scores_mid_fkey" FOREIGN KEY (mid) REFERENCES words_moves(mid) ON DELETE CASCADE"words_scores_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON DELETE CASCADE
