[postgis-users] GIST index statistics

[Paul Ramsey]

Spatial index selectivity is a special beast, and has not been  
implemented for the built-in rtrees.  Could be done, just hasn't been  
done.

On Wednesday, July 28, 2004, at 01:09 PM, ken wrote:

> On Wed, 2004-07-28 at 12:00, David Blasby wrote:
>> Did you compile with USE_STATS=1 and then do a
>>
>> select UPDATE_GEOMETRY_STATS();
>>
>> This will turn on the statistical analysis that usually makes the
>> query planner more rational.
>
> Wow!  That did it!  Thanks.
>
> You wouldn't happen to know if there is an equivalent little magic  
> trick
> to making rtree indices on box column types in standard postgres work
> properly?
>
> I did post the question to pgsql-performance at postgresql.org and didn't
> get a good answer.  :)
>
> Thanks again,
>
> Ken
>
>
>>
>> dave
>> ps. make sure geometry_columns has the proper entries in it.
>>
>> On Wed, 28 Jul 2004 11:37:09 -0700, ken <southerland at samsixedd.com>  
>> wrote:
>>> I just recently starting looking at postgis due to a failure in the
>>> rtree indices of postgres to handle my situation.  Unfortunately,  
>>> and I
>>> guess not surprisingly since GIST indices are built on postgres's  
>>> rtree
>>> index, I experience the same problem with postgis.
>>>
>>> I have the following table ...
>>>
>>> test=# \d gistest
>>>           Table "public.gistest"
>>>    Column    |       Type       | Modifiers
>>> --------------+------------------+-----------
>>> fid1         | numeric(64,0)    | not null
>>> fid2         | numeric(64,0)    | not null
>>> diagonalsize | double precision |
>>> geometry     | geometry         |
>>> Indexes:
>>>    "gistest_diagonalsize_idx" btree (diagonalsize)
>>>    "gistest_geometry_idx" gist (geometry)
>>> Check constraints:
>>>    "$1" CHECK (srid(geometry) = -1)
>>>    "$2" CHECK (geometrytype(geometry) = 'POLYGON'::text OR geometry  
>>> IS
>>> NULL)
>>>
>>> .... and it has 2898640 rows in it.  If I try the following query ...
>>>
>>> explain analyze SELECT *
>>> FROM gistest
>>> WHERE geometry && GeometryFromText('BOX3D(825160.2564102565
>>> 685833.3333333333, 134839.74358974356 264166.6666666666)', -1 )
>>> AND diagonalSize > 7638.888888888889;
>>>
>>> .... I get the following query plan ...
>>>
>>>                                                                       
>>>                 QUERY PLAN
>>> --------------------------------------------------------------------- 
>>> --------------------------------------------------------------------- 
>>> --------------------------------------------
>>> Index Scan using gistest_geometry_idx on gistest  (cost=0.00..60.20
>>> rows=1 width=278)
>>>   Index Cond: (geometry && 'SRID=-1;BOX3D(134839.743589744
>>> 264166.666666667 0,825160.256410256 685833.333333333 0)'::geometry)
>>>   Filter: ((diagonalsize > 7638.88888888889::double precision) AND
>>> (geometry && 'SRID=-1;BOX3D(134839.743589744 264166.666666667
>>> 0,825160.256410256 685833.333333333 0)'::geometry))
>>> (3 rows)
>>>
>>> .... despite the fact that condition on the geometry alone returns
>>> 2896058 rows or almost the entire table!  The statistics, despite a
>>> vacuum analyze on the table, suggest that only 1 row will be  
>>> returned!
>>>
>>> The diagonalsize condition alone returns only 2490 rows and therefore
>>> the query plan should use the index on this diagonalsize with a  
>>> filter
>>> based on the overlaps condition.  Yet due to the completely incorrect
>>> statistics on the geometry column it doesn't.
>>>
>>> Any ideas?
>>>
>>> Ken
>>>
>>> _______________________________________________
>>> postgis-users mailing list
>>> postgis-users at postgis.refractions.net
>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
      Paul Ramsey
      Refractions Research
      Email: pramsey at refractions.net
      Phone: (250) 885-0632