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
