From: | Martijn van Oosterhout <kleptog(at)svana(dot)org> |
---|---|
To: | Janek Sendrowski <janek12(at)web(dot)de> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Suitable Index for my Table |
Date: | 2013-11-04 18:42:14 |
Message-ID: | 20131104184214.GB3552@svana.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)svana(dot)org> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Rob Sargent | 2013-11-04 18:51:38 | Re: table lock when where clause uses unique constraing instead of primary key. |
Previous Message | Kevin Grittner | 2013-11-04 18:40:43 | Re: Suitable Index for my Table |