2012/4/11 Ondrej Ivanič <ondrej.ivanic@gmail.com>:
> Hi,
>
> On 11 April 2012 17:15, Sidney Cadot <sidney@jigsaw.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
yeah -- if you want fast searching of positions (or even games) using
phrases you should immediately be thinking GIST. GIST can optimize
quals such as 'A contains B' or 'A overlaps B'. This is a
non-trival but interesting project and I highly encourage you to give
it a go if you're so inclined. Before banging on the schema, I'd
start thinking about to organize the position into a type such that
you can engineer GIST operations.
merlin
