čt 25. 2. 2021 v 14:36 odesílatel Alexander Farber <alexander.farber@xxxxxxxxx> 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_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 CASCADEYou 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_usersTable "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 | falsemuted | boolean | | not null | falseIndexes:"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 CASCADETABLE "words_games" CONSTRAINT "words_games_player1_fkey" FOREIGN KEY (player1) REFERENCES words_users(uid) ON DELETE CASCADETABLE "words_games" CONSTRAINT "words_games_player2_fkey" FOREIGN KEY (player2) REFERENCES words_users(uid) ON DELETE CASCADETABLE "words_moves" CONSTRAINT "words_moves_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON DELETE CASCADETABLE "words_puzzles" CONSTRAINT "words_puzzles_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON DELETE CASCADETABLE "words_reviews" CONSTRAINT "words_reviews_author_fkey" FOREIGN KEY (author) REFERENCES words_users(uid) ON DELETE CASCADETABLE "words_reviews" CONSTRAINT "words_reviews_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON DELETE CASCADETABLE "words_scores" CONSTRAINT "words_scores_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON DELETE CASCADETABLE "words_social" CONSTRAINT "words_social_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON DELETE CASCADETABLE "words_stats" CONSTRAINT "words_stats_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON DELETE CASCADEOr 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 :-)ThanksAlex
