Gregory,
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?
> -----Ursprüngliche Nachricht-----
> Von: PostGIS Users Discussion <postgis-users@postgis.refractions.net>
> Gesendet: 07.09.07 12:21:26
> An: "PostGIS Users Discussion" <postgis-users@postgis.refractions.net>
> Betreff: 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: [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
>
_____________________________________________________________________
Der WEB.DE SmartSurfer hilft bis zu 70% Ihrer Onlinekosten zu sparen!
http://smartsurfer.web.de/?mc=100071&distributionid=000000000066
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
