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
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
