Re: Suitable Index for my Table - Mailing list pgsql-general
From | Kevin Grittner |
---|---|
Subject | Re: Suitable Index for my Table |
Date | |
Msg-id | 1383603240.1754.YahooMailNeo@web162904.mail.bf1.yahoo.com Whole thread Raw |
In response to | Re: Suitable Index for my Table ("Janek Sendrowski" <janek12@web.de>) |
List | pgsql-general |
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
pgsql-general by date: