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

čt 25. 2. 2021 v 14:36 odesílatel Alexander Farber <
alexander(dot)farber(at)gmail(dot)com> napsal:

> Hi Pavel,
>
> trying to follow your advice "You should check so all foreign keys have an
> index" I look at the table where I want to delete older records:
>
> # \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
>
> You are probably talking about the section:
>
> 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
>
> The first table words_boards only has 4 records, so I ignore it.
>
> The second table words_users already has an index on the uid, because that
> column is the primary key:
>
> # \d words_users
> Table "public.words_users"
> Column | Type | Collation | Nullable |
> Default
>
> -------------+--------------------------+-----------+----------+------------------------------------------
> uid | integer | | not null |
> nextval('words_users_uid_seq'::regclass)
> created | timestamp with time zone | | not null |
> visited | timestamp with time zone | | not null |
> ip | inet | | not null |
> fcm | text | | |
> apns | text | | |
> adm | text | | |
> motto | text | | |
> vip_until | timestamp with time zone | | |
> grand_until | timestamp with time zone | | |
> elo | integer | | not null |
> medals | integer | | not null |
> coins | integer | | not null |
> avg_score | double precision | | |
> avg_time | interval | | |
> hms | text | | |
> removed | boolean | | not null | false
> muted | boolean | | not null | false
> Indexes:
> "words_users_pkey" PRIMARY KEY, btree (uid)
> Check constraints:
> "words_users_elo_check" CHECK (elo >= 0)
> "words_users_medals_check" CHECK (medals >= 0)
> Referenced by:
> TABLE "words_chat" CONSTRAINT "words_chat_uid_fkey" FOREIGN KEY (uid)
> REFERENCES words_users(uid) ON DELETE CASCADE
> TABLE "words_games" CONSTRAINT "words_games_player1_fkey" FOREIGN KEY
> (player1) REFERENCES words_users(uid) ON DELETE CASCADE
> TABLE "words_games" CONSTRAINT "words_games_player2_fkey" FOREIGN KEY
> (player2) REFERENCES words_users(uid) ON DELETE CASCADE
> TABLE "words_moves" CONSTRAINT "words_moves_uid_fkey" FOREIGN KEY
> (uid) REFERENCES words_users(uid) ON DELETE CASCADE
> TABLE "words_puzzles" CONSTRAINT "words_puzzles_uid_fkey" FOREIGN KEY
> (uid) REFERENCES words_users(uid) ON DELETE CASCADE
> TABLE "words_reviews" CONSTRAINT "words_reviews_author_fkey" FOREIGN
> KEY (author) REFERENCES words_users(uid) ON DELETE CASCADE
> TABLE "words_reviews" CONSTRAINT "words_reviews_uid_fkey" FOREIGN KEY
> (uid) REFERENCES words_users(uid) ON DELETE CASCADE
> TABLE "words_scores" CONSTRAINT "words_scores_uid_fkey" FOREIGN KEY
> (uid) REFERENCES words_users(uid) ON DELETE CASCADE
> TABLE "words_social" CONSTRAINT "words_social_uid_fkey" FOREIGN KEY
> (uid) REFERENCES words_users(uid) ON DELETE CASCADE
> TABLE "words_stats" CONSTRAINT "words_stats_uid_fkey" FOREIGN KEY
> (uid) REFERENCES words_users(uid) ON DELETE CASCADE
>
> Or do I misunderstand something?
>
> If someone would be interested to take a look at the real database, I
> would anonymize it and provide download
>

It is Linux or Windows?
is possible ssh access?

Pavel

> 1 477 210 374 Feb 25 12:04 words_ru-Feb.sql.gz
>
> However I understand that this is a lot to ask and am already thankful for
> any input :-)
>
> Thanks
> Alex
>
>