[postgis-users] GIST Index performance

[ken]

On Mon, 2004-11-29 at 20:39, Paul Ramsey wrote:
> Ken,
> What is the contents of "geometry_columns". Do you have some stats  
> columns?

Aaah, no nothing there.
One of my team members changed the table definition on me!  Aaargh. 
Rather than creating the column with the AddGeometryColumn() function
they just created it as type "geometry" and did nothing else.  I didn't
notice this until investigating further after your post.  So I looked at
the function and figured out that I needed to do two other things,
namely ...

INSERT INTO geometry_columns VALUES ( '', current_schema(),
'nrgfeature', 'boundinggeom', 2, -1, 'POLYGON' );
ALTER TABLE nrgfeature ADD CHECK ( SRID( boundinggeom ) = -1 );

.... then reran "select UPDATE_GEOMETRY_STATS()" and everything is
hunky-dory now!

Thanks!

Ken


>  Are they full of histogram goodness? 
> What is your pgsql  
> version, your postgis version?

Just for fullness ...

postgresql-7.4.2-31
postgis 0.8.2


> P
> 
> On 29-Nov-04, at 6:15 PM, ken wrote:
> 
> > Hi all,
> >
> > I've got a simple problem with indexing that I can't seem to overcome.
> >
> > I've got a table that looks like the following (Note: I am not
> > displaying all of the columns, just the ones involved in this query for
> > simplification)...
> >
> > nrgdb=# \d nrgfeature
> >                 Table "public.nrgfeature"
> >      Column     |            Type             | Modifiers
> > ----------------+-----------------------------+-----------
> >  fid1           | bigint                      | not null
> >  fid2           | bigint                      | not null
> >  featuretypeid  | smallint                    | not null
> >  diagonalsize   | double precision            |
> >  boundinggeom   | geometry                    |
> >  layerid        | integer                     |
> > Indexes:
> >     "nrgfeature_fid_index" unique, btree (fid2, fid1)
> >     "nrgfeature_boundinggeom_idx" gist (boundinggeom)
> >     "nrgfeature_diag_idx" btree (diagonalsize)
> >     "nrgfeature_ft_index" btree (featuretypeid)
> >     "nrgfeature_layer" btree (layerid)
> >
> >
> > ... which uses a gist index on the boundinggeom column.  The table has
> > almost 1.5 million rows (1453286 to be exact).
> >
> > If I run a query on the boundinggeom that intersects all of the rows in
> > the table, postgres still chooses the gist index (even though all rows
> > match!) and not another index instead and the query is really, really
> > slow.  If it were instead to use another index the query would be fine.
> > Here is the query I am doing ...
> >
> > nrgdb=# explain
> > nrgdb-# analyze
> > nrgdb-# SELECT *
> > nrgdb-# FROM nrgfeature
> > nrgdb-# WHERE boundinggeom && GeometryFromText('BOX3D(759869.7916666667
> > 646875.0, 200130.2083333333 303125.0)', -1)
> > nrgdb-# AND diagonalSize > 2291.666666666667
> > nrgdb-# ;
> >                                                                         
> >        QUERY PLAN
> > ----------------------------------------------------------------------- 
> > ----------------------------------------------------------------------- 
> > ------------------------
> >  Index Scan using nrgfeature_boundinggeom_idx on nrgfeature
> > (cost=0.00..31.18 rows=1 width=596) (actual time=1329.945..562438.980
> > rows=4422 loops=1)
> >    Index Cond: (boundinggeom && 'SRID=-1;BOX3D(200130.208333333 303125
> > 0,759869.791666667 646875 0)'::geometry)
> >    Filter: ((diagonalsize > 2291.66666666667::double precision) AND
> > (boundinggeom && 'SRID=-1;BOX3D(200130.208333333 303125
> > 0,759869.791666667 646875 0)'::geometry))
> >  Total runtime: 562476.459 ms
> > (4 rows)
> >
> > ... As you can see it thinks only one row would be returned and yet  
> > 4422
> > rows are returned.  All of these rows are satisfied by the diagonalsize
> > condition alone and if we take out the boundinggeom condition we get
> > exactly what we want in this case and we get it immediately.  Of  
> > course,
> > we can't take out the condition in the general case.
> >
> > I have set the statistics to 100 for both the columns diagonalsize and
> > boundinggeom (does that even work for a gist index?), revacuumed full
> > analyze the table, and rerun SELECT UPDATE_GEOMETRY_STATS().  This has
> > not helped.
> >
> > Thanks for any assistance you can provide.
> >
> > Ken
> > -- 
> > ------s----a----m----s----i----x----e----d----d------
> > --
> >
> > Ken Southerland
> > Senior Consultant
> > Sam Six EDD
> > http://www.samsixedd.com
> >
> >
> > _______________________________________________
> > 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
>