<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META NAME="Generator" CONTENT="MS Exchange Server version 6.5.7652.24">
<TITLE>RE: [postgis-users] question on gist performance</TITLE>
</HEAD>
<BODY>
<!-- Converted from text/plain format -->
<P><FONT SIZE=2>I may be wrong, but I think that once PostgreSQL gets a match from the index, it has to go to disk to "make sure" that the data is valid (i.e. visible given the rules of the MVCC environment); indexes don't store info on concurrency of the data as does Oracle's (as I understand it -- retreaded Informix guy myself). Perhaps that accounts for some of the timing difference ?<BR>
<BR>
GSW<BR>
-----Original Message-----<BR>
From: postgis-users-bounces@postgis.refractions.net on behalf of Stefan Zweig<BR>
Sent: Fri 9/7/2007 5:21 AM<BR>
To: PostGIS Users Discussion<BR>
Subject: RE: [postgis-users] question on gist performance<BR>
<BR>
Gregory,<BR>
<BR>
Sorry, but I don't understand your point. We added the GIST index to speed up the bounding box based queries. Thus, scanning the raw geometry data is not necessary since the GIST index already contains all information needed to execute the query, doesn't it?<BR>
<BR>
> -----Ursprüngliche Nachricht-----<BR>
> Von: PostGIS Users Discussion <postgis-users@postgis.refractions.net><BR>
> Gesendet: 07.09.07 12:21:26<BR>
> An: "PostGIS Users Discussion" <postgis-users@postgis.refractions.net><BR>
> Betreff: RE: [postgis-users] question on gist performance<BR>
<BR>
<BR>
><BR>
> 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>
><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>
<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>
<BR>
</FONT>
</P>
</BODY>
</HTML>