Re: Not sure if I should CREATE INDEX for text columns on which I plan to filter later

[Martin Moore <martin.moore@xxxxxxxxxxx>]

I’m no expert but I’d think it unlikely an index would be considered for a table with only 100 rows in. Also I’m pretty sure only one index per table is used, so you’d want to put state1 and state2 in one index.

 

You may wish to consider normalising too – so any field with a 1 or 2 at the end is moved to a separate table linked by gid. This would also help the indexing.

 

 

Martin.

 

From: Alexander Farber <alexander.farber@xxxxxxxxx>
Date: Wednesday, 21 February 2018 at 12:16
To: pgsql-general <pgsql-general@xxxxxxxxxxxxxx>
Subject: Not sure if I should CREATE INDEX for text columns on which I plan to filter later

 

Hello,

in a 2 player game I store all games in the following PostgreSQL 10.2 table:

CREATE TABLE words_games (
        gid SERIAL PRIMARY KEY,

        created timestamptz NOT NULL,
        finished timestamptz,

        player1 integer REFERENCES words_users(uid) ON DELETE CASCADE NOT NULL CHECK (player1 <> player2),
        player2 integer REFERENCES words_users(uid) ON DELETE CASCADE,

        played1 timestamptz,
        played2 timestamptz,

        state1  text, -- tie, winning, losing, draw, won, lost
        state2  text, -- tie, winning, losing, draw, won, lost
        reason  text, -- regular, resigned, expired, banned

        score1  integer NOT NULL CHECK (score1 >= 0),
        score2  integer NOT NULL CHECK (score2 >= 0),

        hand1   char[7]   NOT NULL,
        hand2   char[7]   NOT NULL,
        pile    char[116] NOT NULL,

        letters char[15][15] NOT NULL,
        values  integer[15][15] NOT NULL,
        bid     integer NOT NULL REFERENCES words_boards ON DELETE CASCADE
);

In the 3 text columns state1, state2 and reason I store all possible game/player states.

For example: player1 has failed to make her move in time, so that would result in:
    state1 = 'lost',
    state2 = 'won',
    reason = 'expired',

On an advice I've got from this mailing list I am explicitly not using enums (in case I need to add unforseen states).

The purpose of these 3 text columns is for me to display player stats later, by quering the columns.

As you can imagine, mostly I perform SELECT on the words_games table - to send update to the game clients (PC and mobile).

And in more seldom cases I update these 3 text columns - when a move is performed or a game gets finished or expires.

My question please:

Should I add the 3 indices as in:

CREATE INDEX words_games_state1_index on words_games(state1);
CREATE INDEX words_games_state2_index on words_games(state2);
CREATE INDEX words_games_reason_index on words_games(reason);

I am asking, because as an unexperienced database user I fail to see any difference when I run EXPLAIN:

words=> select gid, state1, state2 from words_games where state1='won' or state2='won';
 gid | state1 | state2
-----+--------+--------
 146 | lost   | won
 144 | lost   | won
 145 | lost   | won
 150 | won    | lost
..........
 256 | won    | lost
 255 | won    | lost
  35 | lost   | won
(100 rows)

words=> explain select gid, state1, state2 from words_games where state1='won' or state2='won';
                          QUERY PLAN
---------------------------------------------------------------
 Seq Scan on words_games  (cost=0.00..109.85 rows=96 width=12)
   Filter: ((state1 = 'won'::text) OR (state2 = 'won'::text))
(2 rows)

words=> CREATE INDEX words_games_state1_index on words_games(state1);
CREATE INDEX

words=> CREATE INDEX words_games_state2_index on words_games(state2);
CREATE INDEX

words=> explain select gid, state1, state2 from words_games where state1='won' or state2='won';
                          QUERY PLAN
---------------------------------------------------------------
 Seq Scan on words_games  (cost=0.00..109.85 rows=96 width=12)
   Filter: ((state1 = 'won'::text) OR (state2 = 'won'::text))
(2 rows)

Thank you for any insights
Alex