Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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 

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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux