Search Postgresql Archives

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

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

 



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


[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 Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux