Just a thought to make sure your geoms do indeed have bboxes. Have you tried
SELECT HasBBOX(the_geom) from mytable
If that returns false then you may need to do a
update mytable set the_geom = addbbox(the_geom) from mytable
In theory this should have been automatically added, but I think in older
versions of postgresql/postgis there may have been conditions where it wasn't.
hope that helps,
Regina
________________________________
From: [EMAIL PROTECTED] on behalf of Stefan Zweig
Sent: Fri 9/7/2007 6:09 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] question on gist performance
> 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 <[email protected]>
> Gesendet: 07.09.07 11:47:30
> An: PostGIS Users Discussion <[email protected]>
> 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
> [email protected]
> 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
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
