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: [EMAIL PROTECTED] 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@postgis.refractions.net>
> Gesendet: 07.09.07 10:44:16
> An: PostGIS Users Discussion <postgis-users@postgis.refractions.net>
> Betreff: Re: [postgis-users] question on gist performance
>
> Stefan,
>
> * Stefan Zweig <[EMAIL PROTECTED]> [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@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@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
