Janek Sendrowski <janek12@xxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
