[postgis-users] question on gist performance

[Obe, Regina]

Just a thought to make sure your geoms do indeed have bboxes.  Have you tried
 
SELECT HasBBOX(the_geom) from mytable
 
If that returns false then you may need to do a 
 
update mytable set the_geom = addbbox(the_geom) from mytable
 
In theory this should have been automatically added, but I think in older versions of postgresql/postgis there may have been conditions where it wasn't.
 
hope that helps,
Regina
 
 

________________________________

From: postgis-users-bounces at postgis.refractions.net on behalf of Stefan Zweig
Sent: Fri 9/7/2007 6:09 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] question on gist performance



> EXPLAIN ANALYZE SELECT gid FROM mytable WHERE the_geom &&
> SetSrid('BOX(13.760675 51.171104,15.231802 51.83737)'::box2d,4326);

"Index Scan using mytable_the_geom_gist on mytable  (cost=0.00..4.95 rows=1 width=4) (actual time=100.198..247.711 rows=4 loops=1)"
"  Index Cond: (the_geom && '0103000020E610000001000000050000000000004077852B40000000C0E69549400000004077852B40000000002FEB4940000000C0AE762E40000000002FEB4940000000C0AE762E40000000C0E69549400000004077852B40000000C0E6954940'::geometry)"
"  Filter: (the_geom && '0103000020E610000001000000050000000000004077852B40000000C0E69549400000004077852B40000000002FEB4940000000C0AE762E40000000002FEB4940000000C0AE762E40000000C0E69549400000004077852B40000000C0E6954940'::geometry)"
"Total runtime: 247.782 ms"

> EXPLAIN ANALYZE SELECT gid FROM mytable WHERE bbox &&
> SetSrid('BOX(13.760675 51.171104,15.231802 51.83737)'::box2d,4326);

"Index Scan using mytable_bbox_gist on mytable  (cost=0.00..4.95 rows=1 width=4) (actual time=0.020..0.078 rows=4 loops=1)"
"  Index Cond: (bbox && '0103000020E610000001000000050000000000004077852B40000000C0E69549400000004077852B40000000002FEB4940000000C0AE762E40000000002FEB4940000000C0AE762E40000000C0E69549400000004077852B40000000C0E6954940'::geometry)"
"  Filter: (bbox && '0103000020E610000001000000050000000000004077852B40000000C0E69549400000004077852B40000000002FEB4940000000C0AE762E40000000002FEB4940000000C0AE762E40000000C0E69549400000004077852B40000000C0E6954940'::geometry)"
"Total runtime: 0.137 ms"

> SELECT postgis_full_version();

"POSTGIS="1.2.1" GEOS="2.2.3-CAPI-1.1.1" PROJ="Rel. 4.4.9, 29 Oct 2004" USE_STATS"

> SELECT version();

"PostgreSQL 8.1.8 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.5 (Debian 1:3.3.5-13)"

As you see, the_geom is *not* selected (which may also increase query time).
Are possibly constrains on srid, dims or geotype are important in queries?




> -----Ursprüngliche Nachricht-----
> Von: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Gesendet: 07.09.07 11:47:30
> An: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Betreff: Re: [postgis-users] question on gist performance


>
> Setfan,
>
> * Stefan Zweig <stefanzweig1881 at web.de> [070907 11:36]:
> > this what analyze says:
>
> the following output looks like from EXPLAIN, not from EXPLAIN ANALYSE,
> the actual timings, loops etc are missing. Could you please repost?
>
> > "Index Scan using mytable_the_geom_gist on mytable  (cost=0.00..4.95 rows=1 width=13)"
> > "  Index Cond: ....
> > "  Filter: ...
> >
> > "Index Scan using mytable_bbox_gist on mytable  (cost=0.00..4.95 rows=1 width=13)"
> > "  Index Cond: (...
> > "  Filter: (...
>
> Also some version/platform information would help. Could you please post
> the output of
>  select postgis_full_version();
> and
>  select version();
>
> Regards,
>
>         Frank
>
> --
> Frank Koormann    |   ++49-541-335 08 30    |   http://www.intevation.net/
> Intevation GmbH, Osnabrück, DE | Commercial Register Osnabrück, HR B 18998
> Managing Directors: Frank Koormann, Bernhard Reiter, Dr. Jan-Oliver Wagner
> PostGIS Support (http://www.intevation.net/geospatial/postgis-support.en.html)
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>


_____________________________________________________________________
Der WEB.DE SmartSurfer hilft bis zu 70% Ihrer Onlinekosten zu sparen!
http://smartsurfer.web.de/?mc=100071&distributionid=000000000066

_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users





-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070907/fd89351c/attachment.html>