Thanks Guido! The sample you gave performed slightly better: 304 seconds. I'll still have to wait a few months to let it run on all my data though. Yes, sitecode has a "normal" index, not a spatial one. Both the_geom's have spatial ones. The european_waters polygon is one single complex polygon, scale 1/100000. What's annoying is that when I do an intersect in Arc View it's almost instantaneous. Of course I don't get exaclty what I wan't but with some tweaking I can maybe get closer. That would be a great disappointment though. I was really looking forward to using ST_Within intensively!
Thanks Jean David, no performance gain though by using it in the WHERE clause, where I intended to use it in the first place. I put it in the Select for testing purposes. explain select sitecode from sites a, europe_waters b where st_intersects(a.the_geom,b.the_geom) and a.sitecode = 'xxxx'; "Nested Loop (cost=0.00..461.71 rows=1 width=10)" " Join Filter: _st_intersects(a.the_geom, b.the_geom)" " -> Seq Scan on sites a (cost=0.00..453.43 rows=1 width=35764)" " Filter: ((sitecode)::text = 'xxxx'::text)" " -> Index Scan using idx_europe_waters on europe_waters b (cost=0.00..8.27 rows=1 width=32)" " Index Cond: (a.the_geom && b.the_geom)" " Filter: (a.the_geom && b.the_geom)" On Tue, Aug 5, 2008 at 10:53 AM, Guido Lemoine <[EMAIL PROTECTED]> wrote: > Erik > > Try this, and see if it is any faster: > > select st_intersects(a.the_geom,b.the_geom) from sites a, europe_waters b > where a.the_geom && b.the_geom and > a.sitecode = 'xxxx'; > > I guess you mean that sitecode is indexed (not spatially indexed), because > it seems to be a varchar. > Both a and b should have a spatial index on the_geom, but I reckon that is > already the case. Also, > b (europe_layer) should not be a single polygon, but rather a set of > polygons. > > This is lesson 1 in the PostGIS tutorial, more or less. In return for my 2 > minutes effort, you are > obliged to report the new performance report, so that future users will > benefit. > After all, I just saved you half a year... > > GL > > > > danny wrote: > >> Hello, >> >> I'm wondering if it's normal to have a 420 second response time for the >> following query. >> A spatial index has been set on the spatial field (the_geom) and other >> important fields (like sitecode). I've generously tweaked the memory options >> for postgresql. >> With such a response time I would have to let my query run for half a year >> before getting the answer I'm interested in! :) >> >> Anybody know how I can boost up the process or is it doomed to always be >> so slow? The europe_layer is indeed a complex polygon.... >> >> select st_intersects(a.the_geom,b.the_geom) from sites a, europe_waters >> b where a.sitecode = 'xxxx'; >> >> "Nested Loop (cost=0.00..496.18 rows=1310 width=35786)" >> " -> Seq Scan on sites a (cost=0.00..453.43 rows=1 width=35754)" >> " Filter: ((sitecode)::text = 'xxxx'::text)" >> " -> Seq Scan on europe_waters b (cost=0.00..23.10 rows=1310 width=32)" >> >> My true objective would be to join thses two tables through an >> st_intersects but for the time it is unconceivable. >> Many thanks, >> >> Erik >> ------------------------------------------------------------------------ >> >> _______________________________________________ >> postgis-users mailing list >> [email protected] >> http://postgis.refractions.net/mailman/listinfo/postgis-users >> >> > > -- > ---------------------------------------------------------------- > Guido Lemoine > Joint Research Centre, European Commission > Institute for the Protection and Security of the Citizen (IPSC) > Support to External Security > Via E. Fermi, 2749 TP 267 Ispra 21027 (VA), Italy > Tel. +39 0332 786239 (direct line) Fax. +39 0332 785154 > WWW: http://ses.jrc.it > ---------------------------------------------------------------- > Disclaimer: > Views expressed are those of the individual and do not represent the views > of the European Commission > > > _______________________________________________ > postgis-users mailing list > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users >
_______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
