[postgis-users] GIST index statistics

[Gregory S. Williamson]

vacuum analyze ...

will rebuild statistics for "ordinary tables" (and in more recent versions I think also does the GIST stats).

Greg Williamson
DBA
GlobeXplorer LLC

-----Original Message-----
From: ken [mailto:southerland at samsixedd.com]
Sent: Wednesday, July 28, 2004 1:09 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] GIST index statistics


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