[postgis-users] looking for some perf data

[Robert Coup]

raphael Jacquot wrote:
> Martin Davis wrote:
>> Well, those are range queries.  Not too surprising, 99.9% of spatial 
>> queries are.
>>
>> The real question is: given that every modern spatial database uses 
>> some sort of spatial index (R-tree, quad-tree, or grid), why even 
>> bother to question whether B-trees might be better?
>
> ask my boss :D
http://en.wikipedia.org/wiki/R-tree

>>> typically I'm looking to compare using
>>>
>>> create table blah1 (
>>>     lon    double precision,
>>>     lat    double precision
>>> )
>>> with one index on lon and another one on lat
>>>
>>> the classic request being
>>> lon>constant1 and lon<constant2 and lat>constant3 and lat<constant4
>>>
>>> and
>>>
>>> create table blah2 (
>>>     position Point
>>> )
>>> with a gist r-tree index
>>>
>>> using the @ operator
Assuming a non-trivial sized dataset, here's a really simplistic 
overview of my understanding ...

B-Tree on x & y:
    1. search lat to find matching Lat slice of dataset == 1 tree 
descent, returning 50K records
    2. search lon to find matching Lon slice of dataset == 1 tree 
descent, returning 50K records
    3. perform AND on 1&2, returning 1K records
    4. seq scan over 3 to check extents within the tree granularities, 
returning 900 records

R-Tree on geom:
    1. search to find matching bounds == 1 tree descent, returning 1K 
records
    2. seq scan over 1 to check extent within the tree granularity, 
returning 900 records

It's pretty easy to use EXPLAIN to try it out (pgAdmin even has a nice 
graphical view).

Rob :)

-- 
One Track Mind Ltd.
PO Box 1604, Shortland St, Auckland, New Zealand
Phone +64-9-966 0433 Mobile +64-21-572 632
Web http://www.onetrackmind.co.nz