čt 25. 2. 2021 v 14:06 odesílatel Alexander Farber < alexander.far...@gmail.com> 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 <guilla...@lelarge.info> > wrote: > >> Le ven. 27 nov. 2020 à 16:05, Alvaro Herrera <alvhe...@alvh.no-ip.org> 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: 137 > Planning Time: 0.150 ms > Execution 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_games > Table "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 | false > open2 | boolean | | not null | false > Indexes: > "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 CASCADE > 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 > > # \d words_moves > Table "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 CASCADE > Referenced by: > TABLE "words_puzzles" CONSTRAINT "words_puzzles_mid_fkey" FOREIGN KEY > (mid) REFERENCES words_moves(mid) ON DELETE CASCADE > TABLE "words_scores" CONSTRAINT "words_scores_mid_fkey" FOREIGN KEY > (mid) REFERENCES words_moves(mid) ON DELETE CASCADE > > # \d words_scores > Table "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 > >