[postgis-users] GIST index statistics

[strk]

On Wed, Jul 28, 2004 at 02:13:45PM -0700, Gregory S. Williamson wrote:
> vacuum analyze ...
> 
> will rebuild statistics for "ordinary tables" (and in more recent versions I think also does the GIST stats).

recent postgis releases supports this but postgresql has to be at least
7.5  (not released yet)

--strk;

> 
> 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
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users