<HTML dir=ltr><HEAD><TITLE>Re: [postgis-users] question on gist performance</TITLE>
<META http-equiv=Content-Type content="text/html; charset=unicode">
<META content="MSHTML 6.00.6000.16525" name=GENERATOR></HEAD>
<BODY>
<DIV id=idOWAReplyText95208 dir=ltr>
<DIV dir=ltr><FONT face=Arial color=#000000 size=2>Just a thought to make sure your geoms do indeed have bboxes. Have you tried</FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2></FONT> </DIV>
<DIV dir=ltr><FONT face=Arial size=2>SELECT HasBBOX(the_geom) from mytable</FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2></FONT> </DIV>
<DIV dir=ltr><FONT face=Arial size=2>If that returns false then you may need to do a </FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2></FONT> </DIV>
<DIV dir=ltr><FONT face=Arial size=2>update mytable set the_geom = addbbox(the_geom) from mytable</FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2></FONT> </DIV>
<DIV dir=ltr><FONT face=Arial size=2>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.</FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2></FONT> </DIV>
<DIV dir=ltr><FONT face=Arial size=2>hope that helps,</FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2>Regina</FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2></FONT> </DIV>
<DIV dir=ltr><FONT face=Arial size=2></FONT> </DIV></DIV>
<DIV dir=ltr><BR>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B> postgis-users-bounces@postgis.refractions.net on behalf of Stefan Zweig<BR><B>Sent:</B> Fri 9/7/2007 6:09 AM<BR><B>To:</B> PostGIS Users Discussion<BR><B>Subject:</B> Re: [postgis-users] question on gist performance<BR></FONT><BR></DIV>
<DIV>
<P><FONT size=2>> EXPLAIN ANALYZE SELECT gid FROM mytable WHERE the_geom &&<BR>> SetSrid('BOX(13.760675 51.171104,15.231802 51.83737)'::box2d,4326);<BR><BR>"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)"<BR>" Index Cond: (the_geom && '0103000020E610000001000000050000000000004077852B40000000C0E69549400000004077852B40000000002FEB4940000000C0AE762E40000000002FEB4940000000C0AE762E40000000C0E69549400000004077852B40000000C0E6954940'::geometry)"<BR>" Filter: (the_geom && '0103000020E610000001000000050000000000004077852B40000000C0E69549400000004077852B40000000002FEB4940000000C0AE762E40000000002FEB4940000000C0AE762E40000000C0E69549400000004077852B40000000C0E6954940'::geometry)"<BR>"Total runtime: 247.782 ms"<BR><BR>> EXPLAIN ANALYZE SELECT gid FROM mytable WHERE bbox &&<BR>> SetSrid('BOX(13.760675 51.171104,15.231802 51.83737)'::box2d,4326);<BR><BR>"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)"<BR>" Index Cond: (bbox && '0103000020E610000001000000050000000000004077852B40000000C0E69549400000004077852B40000000002FEB4940000000C0AE762E40000000002FEB4940000000C0AE762E40000000C0E69549400000004077852B40000000C0E6954940'::geometry)"<BR>" Filter: (bbox && '0103000020E610000001000000050000000000004077852B40000000C0E69549400000004077852B40000000002FEB4940000000C0AE762E40000000002FEB4940000000C0AE762E40000000C0E69549400000004077852B40000000C0E6954940'::geometry)"<BR>"Total runtime: 0.137 ms"<BR><BR>> SELECT postgis_full_version();<BR><BR>"POSTGIS="1.2.1" GEOS="2.2.3-CAPI-1.1.1" PROJ="Rel. 4.4.9, 29 Oct 2004" USE_STATS"<BR><BR>> SELECT version();<BR><BR>"PostgreSQL 8.1.8 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.5 (Debian 1:3.3.5-13)"<BR><BR>As you see, the_geom is *not* selected (which may also increase query time).<BR>Are possibly constrains on srid, dims or geotype are important in queries?<BR><BR><BR><BR><BR>> -----Ursprüngliche Nachricht-----<BR>> Von: PostGIS Users Discussion <postgis-users@postgis.refractions.net><BR>> Gesendet: 07.09.07 11:47:30<BR>> An: PostGIS Users Discussion <postgis-users@postgis.refractions.net><BR>> Betreff: Re: [postgis-users] question on gist performance<BR><BR><BR>><BR>> Setfan,<BR>><BR>> * Stefan Zweig <stefanzweig1881@web.de> [070907 11:36]:<BR>> > this what analyze says:<BR>><BR>> the following output looks like from EXPLAIN, not from EXPLAIN ANALYSE,<BR>> the actual timings, loops etc are missing. Could you please repost?<BR>><BR>> > "Index Scan using mytable_the_geom_gist on mytable (cost=0.00..4.95 rows=1 width=13)"<BR>> > " Index Cond: ....<BR>> > " Filter: ...<BR>> ><BR>> > "Index Scan using mytable_bbox_gist on mytable (cost=0.00..4.95 rows=1 width=13)"<BR>> > " Index Cond: (...<BR>> > " Filter: (...<BR>><BR>> Also some version/platform information would help. Could you please post<BR>> the output of<BR>> select postgis_full_version();<BR>> and<BR>> select version();<BR>><BR>> Regards,<BR>><BR>> Frank<BR>><BR>> --<BR>> Frank Koormann | ++49-541-335 08 30 | <A href="http://www.intevation.net/">http://www.intevation.net/</A><BR>> Intevation GmbH, Osnabrück, DE | Commercial Register Osnabrück, HR B 18998<BR>> Managing Directors: Frank Koormann, Bernhard Reiter, Dr. Jan-Oliver Wagner<BR>> PostGIS Support (<A href="http://www.intevation.net/geospatial/postgis-support.en.html">http://www.intevation.net/geospatial/postgis-support.en.html</A>)<BR>> _______________________________________________<BR>> postgis-users mailing list<BR>> postgis-users@postgis.refractions.net<BR>> <A href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>><BR><BR><BR>_____________________________________________________________________<BR>Der WEB.DE SmartSurfer hilft bis zu 70% Ihrer Onlinekosten zu sparen!<BR><A href="http://smartsurfer.web.de/?mc=100071&distributionid=000000000066">http://smartsurfer.web.de/?mc=100071&distributionid=000000000066</A><BR><BR>_______________________________________________<BR>postgis-users mailing list<BR>postgis-users@postgis.refractions.net<BR><A href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR></FONT></P></DIV></BODY></HTML>
<HTML><BODY><P><hr size=1></P>
<P><STRONG>
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.
</STRONG></P></BODY></HTML>