[postgis-users] question on gist performance

[Stefan Zweig]

> EXPLAIN ANALYZE SELECT gid FROM mytable WHERE the_geom && 
> SetSrid('BOX(13.760675 51.171104,15.231802 51.83737)'::box2d,4326);

"Index Scan using mytable_the_geom_gist on mytable  (cost=0.00..4.95 rows=1 width=4) (actual time=100.198..247.711 rows=4 loops=1)"
"  Index Cond: (the_geom && '0103000020E610000001000000050000000000004077852B40000000C0E69549400000004077852B40000000002FEB4940000000C0AE762E40000000002FEB4940000000C0AE762E40000000C0E69549400000004077852B40000000C0E6954940'::geometry)"
"  Filter: (the_geom && '0103000020E610000001000000050000000000004077852B40000000C0E69549400000004077852B40000000002FEB4940000000C0AE762E40000000002FEB4940000000C0AE762E40000000C0E69549400000004077852B40000000C0E6954940'::geometry)"
"Total runtime: 247.782 ms"

> EXPLAIN ANALYZE SELECT gid FROM mytable WHERE bbox && 
> SetSrid('BOX(13.760675 51.171104,15.231802 51.83737)'::box2d,4326);

"Index Scan using mytable_bbox_gist on mytable  (cost=0.00..4.95 rows=1 width=4) (actual time=0.020..0.078 rows=4 loops=1)"
"  Index Cond: (bbox && '0103000020E610000001000000050000000000004077852B40000000C0E69549400000004077852B40000000002FEB4940000000C0AE762E40000000002FEB4940000000C0AE762E40000000C0E69549400000004077852B40000000C0E6954940'::geometry)"
"  Filter: (bbox && '0103000020E610000001000000050000000000004077852B40000000C0E69549400000004077852B40000000002FEB4940000000C0AE762E40000000002FEB4940000000C0AE762E40000000C0E69549400000004077852B40000000C0E6954940'::geometry)"
"Total runtime: 0.137 ms"

> SELECT postgis_full_version();

"POSTGIS="1.2.1" GEOS="2.2.3-CAPI-1.1.1" PROJ="Rel. 4.4.9, 29 Oct 2004" USE_STATS"

> SELECT version();

"PostgreSQL 8.1.8 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.5 (Debian 1:3.3.5-13)"

As you see, the_geom is *not* selected (which may also increase query time).
Are possibly constrains on srid, dims or geotype are important in queries?




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


> 
> Setfan,
> 
> * Stefan Zweig <stefanzweig1881 at web.de> [070907 11:36]:
> > this what analyze says:
> 
> the following output looks like from EXPLAIN, not from EXPLAIN ANALYSE,
> the actual timings, loops etc are missing. Could you please repost?
> 
> > "Index Scan using mytable_the_geom_gist on mytable  (cost=0.00..4.95 rows=1 width=13)"
> > "  Index Cond: ....
> > "  Filter: ...
> > 
> > "Index Scan using mytable_bbox_gist on mytable  (cost=0.00..4.95 rows=1 width=13)"
> > "  Index Cond: (...
> > "  Filter: (...
> 
> Also some version/platform information would help. Could you please post
> the output of 
>  select postgis_full_version();
> and 
>  select version();
> 
> 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
> 


_____________________________________________________________________
Der WEB.DE SmartSurfer hilft bis zu 70% Ihrer Onlinekosten zu sparen!
http://smartsurfer.web.de/?mc=100071&distributionid=000000000066