Re: Suitable Index for my Table

On Mon, Nov 04, 2013 at 10:44:29PM +0100, Janek Sendrowski wrote:
> I am always searching for single colums. The  values are different every time, because these are distances from my
newsentence to my pivots. 
> Does the Index support a query with this WHERE statement: WHERE value BETWEEN (distance1 - radius) AND (distance1 +
radius)?

Ok, this is not consistant. You say you're only searching single
columns, but in your example query you're doing 23 columns in one
query.

> A working query looks like this:
> SELECT id FROM distance WHERE
> value BETWEEN (distance1 - radius) AND (distance1 + radius) AND
> value BETWEEN (distance2 - radius) AND (distance2 + radius) AND
> value BETWEEN (distance3 - radius) AND (distance3 + radius) AND

So you're looking for the same value amongst all distances? That's
doesn't seem very useful. Then if distance1=0 and distance2=1 and
radius=0.2 then this query will never match that row, no matter what
value of value.

Anyway, unless you can describe this problem as something geometric
(such that you can consider it a search for overlapping cubes) I'd do
what the other post suggests and just put a btree index on every
column.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.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