From: | Ondrej Ivanič <ondrej(dot)ivanic(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Cc: | Sidney Cadot <sidney(at)jigsaw(dot)nl> |
Subject: | Re: Searchable chess positions in a Postgress DB |
Date: | 2012-04-11 10:01:55 |
Message-ID: | CAM6mieLQz38=68WyUYFArsPWa_npQC90XgjpgdsWgxq8mQz+Lw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi,
On 11 April 2012 17:15, Sidney Cadot <sidney(at)jigsaw(dot)nl> wrote:
> I have written code to extract these positions, and now I want to put
> them into a Postgres database. Specifically, I want to do this in a
> way that allows *fast* lookups of positions, e.g. "give me all
> positions that have a White King on c4 and either a Black Bishop or
> White Knight on f7".
I would try to use single table with 16 columns like:
white_pawn char(2)[] -- like {'c1', 'd3', ... }, max 8 elements
white_rook char(2)[] -- max 2 elements
white_bishop char(2)[] -- max 2 elements
white_knight char(2)[] -- max 2 elements
white_queen char(2)
white_king char(2)
black_pawn_1 char(2)[]
...
black_king char(2)
and each column; char(2) and char(2)[] should have btree and GiST
index respectively. The query should looks like this:
select * from positions where white_king = 'c4' and (white_bishop &&
ARRAY['f7'] or white_knight && ARRAY['f7'])
Another alternative might be to use hstore (and GiST index):
http://www.postgresql.org/docs/9.1/static/hstore.html
--
Ondrej Ivanic
(ondrej(dot)ivanic(at)gmail(dot)com)
From | Date | Subject | |
---|---|---|---|
Next Message | nissyre | 2012-04-11 10:28:24 | Query optimization |
Previous Message | Bèrto ëd Sèra | 2012-04-11 09:43:27 | Re: Searchable chess positions in a Postgress DB |