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

From: Alexander Farber <alexander(dot)farber(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Not sure if I should CREATE INDEX for text columns on which I plan to filter later
Date: 2018-02-21 12:16:10
Message-ID: CAADeyWi2b_nO+SquOz7sSF2iaAyM2WhJjKX8bQdFYuDhewJqKw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Martin Moore 2018-02-21 12:26:55 Re: Not sure if I should CREATE INDEX for text columns on which I plan to filter later
Previous Message Tom Lane 2018-02-21 03:54:41 Re: Getting a primitive numeric value from "DatumGetNumeric"?