[postgis-users] question on gist performance

[Stefan Zweig]

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