Re: Suitable Index for my Table

Janek Sendrowski <janek12@web.de> wrote:

> Does the Index support a query with this WHERE statement: WHERE
> value BETWEEN (distance1 - radius) AND (distance1 + radius)?

Note that the following are all equivalent:

value BETWEEN (distance1 - radius) AND (distance1 + radius)

value >= (distance1 - radius) and value <= (distance1 + radius)

(value + radius) >= distance1 and (value - radius) <= distance1

distance1 >= (value - radius) and distance1 <= (value + radius)

distance BETWEEN (value - radius) AND (value + radius)

The first two are not suitable for an index scan on distance1, but
the last three are.  If you can rewrite your query to use that
syntax, it will be able to use a btree index on distance1.

> okay, I will use arrays instead of multiple columns.

It's probably worth looking at, but I can't say that is the best
way from information available so far.

> 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
> value BETWEEN (distance4 - radius) AND (distance4 + radius) AND
> value BETWEEN (distance5 - radius) AND (distance5 + radius) AND
> value BETWEEN (distance6 - radius) AND (distance6 + radius) AND
> value BETWEEN (distance7 - radius) AND (distance7 + radius) AND
> value BETWEEN (distance8 - radius) AND (distance8 + radius) AND
> value BETWEEN (distance9 - radius) AND (distance9 + radius) AND
> value BETWEEN (distance10 - radius) AND (distance10 + radius) AND
> value BETWEEN (distance11 - radius) AND (distance11 + radius) AND
> value BETWEEN (distance12 - radius) AND (distance12 + radius) AND
> value BETWEEN (distance13 - radius) AND (distance13 + radius) AND
> value BETWEEN (distance14 - radius) AND (distance14 + radius) AND
> value BETWEEN (distance15 - radius) AND (distance15 + radius) AND
> value BETWEEN (distance16 - radius) AND (distance16 + radius) AND
> value BETWEEN (distance17 - radius) AND (distance17 + radius) AND
> value BETWEEN (distance18 - radius) AND (distance18 + radius) AND
> value BETWEEN (distance19 - radius) AND (distance19 + radius) AND
> value BETWEEN (distance20 - radius) AND (distance20 + radius) AND
> value BETWEEN (distance21 - radius) AND (distance22 + radius) AND
> value BETWEEN (distance22 - radius) AND (distance23 + radius) AND
> value BETWEEN (distance23 - radius) AND (distance24 + radius);

An array column called dist_array might support something along the
lines of (untested):

SELECT id FROM distance
  WHERE (value - radius) <= ALL (dist_array)
    AND (value + radius) >= ALL (dist_array);

I'm not sure whether a GIN index on the dist_array column would be
usable by such a query, but it might be worth testing.

> Until now It just does a Seq Scan, when I'm searching through the
> table 'distances'. I can show your the Query Plan, if you want.

Actual query text, table definitions (with indexes), and EXPLAIN
ANALYZE output are always helpful.

http://wiki.postgresql.org/wiki/SlowQueryQuestions

Which reminds me, this sort of question might be better on the
pgsql-performance list next time.

> The number of rows which are resulting have a range of 0 until
> something like 100 for the begining.

Keep in mind that indexes will rarely be used on small tables.  It
isn't until there are many data pages that access through indexes
begins to be faster.

Also note that (as previously mentioned) the names of variables
here suggest that geometry or PostGIS types may be a cleaner way to
implement this than dealing in raw coordinates.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company