R: [postgis-users] question on gist performance

Fri Sep 7 03:55:44 PDT 2007

I really don't know exactly what I'm talking about now... :-)
But there exist a hasbbox(geometry) function in PostGIS.
May it be that your slow geometries doesn't have a cached bbox
while fast ones have it???

Bye
Paolo Rizzi

-----Messaggio originale-----
Da: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-bounces at postgis.refractions.net]Per conto di Gregory Williamson
Inviato: venerdì 7 settembre 2007 12.18
A: PostGIS Users Discussion; PostGIS Users Discussion
Oggetto: RE: [postgis-users] question on gist performance

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.

QED ?

Greg Williamson
Senior DBA
GlobeXplorer LLC, a DigitalGlobe company

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.

(My corporate masters made me say this.)

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

hi frank,

this what analyze says:

"Index Scan using mytable_the_geom_gist on mytable  (cost=0.00..4.95 rows=1 width=13)"
"  Index Cond: (the_geom && '0103000020E610000001000000050000000000004077852B40000000C0E69549400000004077852B40000000002FEB4940000000C0AE762E40000000002FEB4940000000C0AE762E40000000C0E69549400000004077852B40000000C0E6954940'::geometry)"
"  Filter: (the_geom && '0103000020E610000001000000050000000000004077852B40000000C0E69549400000004077852B40000000002FEB4940000000C0AE762E40000000002FEB4940000000C0AE762E40000000C0E69549400000004077852B40000000C0E6954940'::geometry)"

"Index Scan using mytable_bbox_gist on mytable  (cost=0.00..4.95 rows=1 width=13)"
"  Index Cond: (bbox && '0103000020E610000001000000050000000000004077852B40000000C0E69549400000004077852B40000000002FEB4940000000C0AE762E40000000002FEB4940000000C0AE762E40000000C0E69549400000004077852B40000000C0E6954940'::geometry)"
"  Filter: (bbox && '0103000020E610000001000000050000000000004077852B40000000C0E69549400000004077852B40000000002FEB4940000000C0AE762E40000000002FEB4940000000C0AE762E40000000C0E69549400000004077852B40000000C0E6954940'::geometry)"

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)?

kind regards, stefan

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

>
> Stefan,
>
> * Stefan Zweig <stefanzweig1881 at web.de> [070907 10:07]:
> > thanks for your quick reply. i have tried your advice and rebuilded
> > all my gist indexes on the table (via drop index, create index) and
> > run a vacuum full afterwards. but that did not change the fact, that
> > the query on the_geom is much slower than the same one on the_geom_1.
> >
> [...]
> >
> >   '...WHERE the_geom && expr;' takes about 250ms,
> >   '...WHERE the_geom_envelope && expr;' takes about 20ms.
> >
> > ANALYZE says that GIST is used. As far as I know, the GIST makes use
> > only of the bounding boxes of Geometry objects, which are equal for
> > each object. How does it come that the query time using the_geom is more than
> > *ten times slower* than the query time using the_geom_envelope?"
>
> It would help if you post the explain analyse output.
>
> 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
>

__________________________________________________________________________
Erweitern Sie FreeMail zu einem noch leistungsstärkeren E-Mail-Postfach!               
Mehr Infos unter http://produkte.web.de/club/?mc=021131

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

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070907/c8d5e8c4/attachment.html>