On Mon, Nov 04, 2013 at 07:21:11PM +0100, Janek Sendrowski wrote: > Hi, > > I've got a table with many Values of the Type REAL. > These are my metric distances or my pivots to my sentences. > The table looks like this: > > ID INTEGER, distance1 REAL, distance2 REAL, distance3 REAL, distance4 REAL, ..., distance24 REAL > > The range of the Value is in between 0 and 1. So it looks like this 0.196 or 0.891 > > That my query > > WHERE value BETWEEN (distance1 - radius) AND (distance1 + radius) > WHERE value BETWEEN (distance2 - radius) AND (distance2 + radius) > WHERE value BETWEEN (distance3 - radius) AND (distance3 + radius) > WHERE value BETWEEN (distance4 - radius) AND (distance4 + radius) > ... > > Now I'm searching for a suitable index. This sounds like a job for a geometric datatype, a la GiST. http://www.postgresql.org/docs/9.3/static/cube.html CREATE INDEX foo ON bar USING GIST ( cube( ARRAY(distance1), ARRAY(distance1) ) ); The you can do lookups with: SELECT * FROM bar WHERE cube( ARRAY(distance1), ARRAY(distance1) ) && cube( ARRAY(value-radius), ARRAY(value+radius) ) If you commonly use sets of columns you can go multiple dimensional for extra benefit. Have a nice day, -- Martijn van Oosterhout <kleptog@xxxxxxxxx> http://svana.org/kleptog/ > He who writes carelessly confesses thereby at the very outset that he does > not attach much importance to his own thoughts. -- Arthur Schopenhauer
Attachment:
signature.asc
Description: Digital signature
