I'm working with a Postgres DB full of chess game data right now, where each game is a row in a 'records' table. The players' moves and the (optional) computer evaluations of those moves each have their own column and are stored as arrays.
I've written a query to retrieve all evaluations of a specified opening sequence of moves. (You'd think the computer evaluation would be consistent - but it's not.) The length of the opening sequence is arbitrary - it could be one move, it could be thirty.
Here's an example query, which finds all games starting with the same ten-move opening sequence and then, for each game with an evaluation, returns the computer's evaluation of that point in the game -
SELECT evaluation[10]
FROM records
WHERE moves[1:10]::text[] = ARRAY['b4', 'e5', 'Bb2', 'd6', 'Nf3', 'Nf6', 'g3', 'Bg4', 'Bg2', 'h5']::text[]
AND evaluation IS NOT NULL;
I'm not certain it's relevant, but the move data is always an alphanumeric string from 2-6 characters, and the computer evaluations are largely decimals (both positive and negative) but do include the occasional special character (forced checkmates have an octothorpe for a prefix).
Here's the relevant snippet of the table description -
Column | Type |
-----------------+--------------------------------+-
id | bigint |
moves | character varying(255)[] |
evaluation | character varying(255)[] |
"records_pkey" PRIMARY KEY, btree (id)
Access method: heap
And here's the query plan from EXPLAIN ANALYZE:
Gather (cost=1000.00..736354.70 rows=905 width=516) (actual time=28251.267..28253.139 rows=0 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on records (cost=0.00..735264.20 rows=377 width=516) (actual time=28243.233..28243.234 rows=0 loops=3)
Filter: ((evaluation IS NOT NULL) AND ((moves[1:10])::text[] = '{b4,e5,Bb2,d6,Nf3,Nf6,g3,Bg4,Bg2,h5}'::text[]))
Rows Removed by Filter: 971361
Planning Time: 8.275 ms
Execution Time: 28253.915 ms
This is too slow, but I don't know how to go about optimizing it -- I'm far from an expert when it comes to Postgres and all my attempts to set up an index haven't budged the query plan a bit.
Some additional thoughts
Since my opening sequences always start from the beginning of the game -- I might want to match on moves 1 to 3 or on moves 1 to 30, but never, say, from move 7 to 15 -- I've also thought about also storing the move data as a space-delimited text string, and matching against the start of the string. (I don't know how to optimize that query, either, but perhaps that would be easier.)
While these are currently arrays of strings, I could possibly represent both the moves and the evaluations as arrays of integers. (Not that I want to, but optimizing this query is more important, and if it helps, I'll do it.)
What do you think? Where should I start?