<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<TITLE>RE: [postgis-users] question on gist performance</TITLE>
<META content="MSHTML 6.00.2800.1491" name=GENERATOR></HEAD>
<BODY>
<DIV><SPAN class=045225310-07092007><FONT face=Arial color=#0000ff size=2>I
really don't know exactly what I'm talking about now... :-)</FONT></SPAN></DIV>
<DIV><SPAN class=045225310-07092007><FONT face=Arial color=#0000ff size=2>But
there exist a hasbbox(geometry) function in PostGIS.</FONT></SPAN></DIV>
<DIV><SPAN class=045225310-07092007><FONT face=Arial color=#0000ff size=2>May it
be that your slow geometries doesn't have a cached bbox</FONT></SPAN></DIV>
<DIV><SPAN class=045225310-07092007><FONT face=Arial color=#0000ff size=2>while
fast ones have it???</FONT></SPAN></DIV>
<DIV><SPAN class=045225310-07092007><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=045225310-07092007><FONT face=Arial color=#0000ff
size=2>Bye</FONT></SPAN></DIV>
<DIV><SPAN class=045225310-07092007><FONT face=Arial color=#0000ff size=2>Paolo
Rizzi</FONT></SPAN></DIV>
<DIV><SPAN class=045225310-07092007><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<BLOCKQUOTE dir=ltr
style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #0000ff 2px solid; MARGIN-RIGHT: 0px">
<DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma
size=2>-----Messaggio originale-----<BR><B>Da:</B>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net]<B>Per conto di
</B>Gregory Williamson<BR><B>Inviato:</B> venerdì 7 settembre 2007
12.18<BR><B>A:</B> PostGIS Users Discussion; PostGIS Users
Discussion<BR><B>Oggetto:</B> RE: [postgis-users] question on gist
performance<BR><BR></FONT></DIV><!-- Converted from text/plain format -->
<P><FONT size=2>To my untutored eye it looks like the slower one is using the
geometry ("index cond: (the_geom &&" vs "index cond: (bbox
&&") ... a bounding box has 4 count 'em 4 points (not sure if all are
even stored) while your geometry has way more than that. So the comparison of
the simple is *way* faster. As it should be.<BR><BR>QED ?<BR><BR>Greg
Williamson<BR>Senior DBA<BR>GlobeXplorer LLC, a DigitalGlobe
company<BR><BR>Confidentiality Notice: This e-mail message, including any
attachments, is for the sole use of the intended recipient(s) and may contain
confidential and privileged information and must be protected in accordance
with those provisions. Any unauthorized review, use, disclosure or
distribution is prohibited. If you are not the intended recipient, please
contact the sender by reply e-mail and destroy all copies of the original
message.<BR><BR>(My corporate masters made me say
this.)<BR><BR><BR><BR>-----Original Message-----<BR>From:
postgis-users-bounces@postgis.refractions.net on behalf of Stefan
Zweig<BR>Sent: Fri 9/7/2007 3:36 AM<BR>To: PostGIS Users
Discussion<BR>Subject: Re: [postgis-users] question on gist
performance<BR><BR>hi frank,<BR><BR>this what analyze says:<BR><BR>"Index Scan
using mytable_the_geom_gist on mytable (cost=0.00..4.95 rows=1
width=13)"<BR>" Index Cond: (the_geom &&
'0103000020E610000001000000050000000000004077852B40000000C0E69549400000004077852B40000000002FEB4940000000C0AE762E40000000002FEB4940000000C0AE762E40000000C0E69549400000004077852B40000000C0E6954940'::geometry)"<BR>"
Filter: (the_geom &&
'0103000020E610000001000000050000000000004077852B40000000C0E69549400000004077852B40000000002FEB4940000000C0AE762E40000000002FEB4940000000C0AE762E40000000C0E69549400000004077852B40000000C0E6954940'::geometry)"<BR><BR>"Index
Scan using mytable_bbox_gist on mytable (cost=0.00..4.95 rows=1
width=13)"<BR>" Index Cond: (bbox &&
'0103000020E610000001000000050000000000004077852B40000000C0E69549400000004077852B40000000002FEB4940000000C0AE762E40000000002FEB4940000000C0AE762E40000000C0E69549400000004077852B40000000C0E6954940'::geometry)"<BR>"
Filter: (bbox &&
'0103000020E610000001000000050000000000004077852B40000000C0E69549400000004077852B40000000002FEB4940000000C0AE762E40000000002FEB4940000000C0AE762E40000000C0E69549400000004077852B40000000C0E6954940'::geometry)"<BR><BR>still
strange.. both querys are using the same index, but there is a dramatically
difference in performance. could it be that the first query on the_geom column
needs to parse the geometry(s) to gather bounding box information from them
(which i actually would not have thought so)?<BR><BR>kind regards,
stefan<BR><BR><BR>> -----Ursprüngliche Nachricht-----<BR>> Von: PostGIS
Users Discussion <postgis-users@postgis.refractions.net><BR>>
Gesendet: 07.09.07 10:44:16<BR>> An: PostGIS Users Discussion
<postgis-users@postgis.refractions.net><BR>> Betreff: Re:
[postgis-users] question on gist performance<BR><BR><BR>><BR>>
Stefan,<BR>><BR>> * Stefan Zweig <stefanzweig1881@web.de> [070907
10:07]:<BR>> > thanks for your quick reply. i have tried your advice and
rebuilded<BR>> > all my gist indexes on the table (via drop index,
create index) and<BR>> > run a vacuum full afterwards. but that did not
change the fact, that<BR>> > the query on the_geom is much slower than
the same one on the_geom_1.<BR>> ><BR>> [...]<BR>> ><BR>>
> '...WHERE the_geom && expr;' takes about
250ms,<BR>> > '...WHERE the_geom_envelope && expr;'
takes about 20ms.<BR>> ><BR>> > ANALYZE says that GIST is used. As
far as I know, the GIST makes use<BR>> > only of the bounding boxes of
Geometry objects, which are equal for<BR>> > each object. How does it
come that the query time using the_geom is more than<BR>> > *ten times
slower* than the query time using the_geom_envelope?"<BR>><BR>> It would
help if you post the explain analyse output.<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>Erweitern
Sie FreeMail zu einem noch leistungsstärkeren
E-Mail-Postfach!
<BR>Mehr Infos unter <A
href="http://produkte.web.de/club/?mc=021131">http://produkte.web.de/club/?mc=021131</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><BR></FONT></P></BLOCKQUOTE></BODY></HTML>