Re: Deleting takes days, should I add some index?

čt 25. 2. 2021 v 14:06 odesílatel Alexander Farber <
alexander(dot)farber(at)gmail(dot)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 <guillaume(at)lelarge(dot)info>
> wrote:
>
>> Le ven. 27 nov. 2020 à 16:05, Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)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
>
>