Re: Not sure if I should CREATE INDEX for text columns on which Iplan to filter later - Mailing list pgsql-general

From Alexander Farber
Subject Re: Not sure if I should CREATE INDEX for text columns on which Iplan to filter later
Date
Msg-id CAADeyWiF1UzTFd38zqH5fmx2t-75v0mrO-K_iwSrYiMk60fefQ@mail.gmail.com
Whole thread Raw
In response to Re: Not sure if I should CREATE INDEX for text columns on which Iplan to filter later  (Alexander Farber <alexander.farber@gmail.com>)
List pgsql-general
Here is the current DESC of the table (I already use few joins....):

words=> \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                     |           |          |
 score1   | integer                  |           | not null |
 score2   | 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 |
 reason   | text                     |           |          |
Indexes:
    "words_games_pkey" PRIMARY KEY, btree (gid)
    "words_games_reason_index" btree (reason)
    "words_games_state1_index" btree (state1)
    "words_games_state2_index" btree (state2)
Check constraints:
    "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


pgsql-general by date:

Previous
From: Alexander Farber
Date:
Subject: Re: Not sure if I should CREATE INDEX for text columns on which Iplan to filter later
Next
From: Ron Johnson
Date:
Subject: Installing 9.6.6 to a RHEL 6.7 server with no Internet access