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: 137
Planning Time: 0.150 ms
Execution Time: 0.143 ms
(6 rows)
Below are the words_games and the "referenced by" tables -
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
