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

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(dot)farber(at)gmail(dot)com>
Date: Wednesday, 21 February 2018 at 12:16
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

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