> 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@postgis.refractions.net>
> Gesendet: 07.09.07 11:47:30
> An: PostGIS Users Discussion <postgis-users@postgis.refractions.net>
> Betreff: Re: [postgis-users] question on gist performance
>
> Setfan,
>
> * Stefan Zweig <[EMAIL PROTECTED]> [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@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
