<div dir="ltr"><div>Dear All;</div>
<div> </div>
<div>I have been trying to process and update query (by st_coveredby) using 2 spatial tables. It seems that everything goes on track except that it has been taking more than 2 hours right now.</div>
<div> </div>
<div>All the related info is below:</div>
<div> </div>
<div>max_connections = 300<br>shared_buffers = 256MB<br>work_mem = 2MB<br>maintenance_work_mem = 32MB<br>max_fsm_pages = 153600<br>wal_buffers = 1MB<br>checkpoint_segments = 16<br>effective_cache_size = 2GB</div>
<div>CPU: Quad Core Intel Xeon 2.33GHz<br>Memory: 4GB</div>
<div>balkan_20080908_0_0_1_postgis=# explain update yolorta_all set city =<br>(select iladi from iller where st_coveredby(yolorta_all.the_geom,<br>iller.the_geom) = 'TRUE');<br>                              QUERY PLAN<br>
-------------------------------------------------------------------------<br> Seq Scan on yolorta_all  (cost=0.00..3458534.64 rows=1526838 width=675)<br>  SubPlan<br>    ->  Seq Scan on iller  (cost=0.00..2.21 rows=1 width=7)<br>
          Filter: (($0 && the_geom) AND _st_coveredby($0, the_geom))<br>(4 rows)</div>
<div>balkan_20080908_0_0_1_postgis=# \d iller<br>                                 Table "public.iller"<br> Column  |         Type          |                      Modifiers<br>----------+-----------------------+-----------------------------------------<br>
------------<br> gid      | integer               | not null default<br>nextval('iller_gid_seq'::regclass)<br> plakano  | integer               |<br> iladi    | character varying(15) |<br> ilceadi  | character varying(20) |<br>
 the_geom | geometry              |<br>Indexes:<br>   "iller_pkey" PRIMARY KEY, btree (gid)<br>   "iller_gid" UNIQUE, btree (gid) CLUSTER<br>   "iller_the_geom_gist" gist (the_geom)<br>Check constraints:<br>
   "enforce_dims_the_geom" CHECK (ndims(the_geom) = 2)<br>   "enforce_geotype_the_geom" CHECK (geometrytype(the_geom) =<br>'MULTIPOLYGON'::text OR the_geom IS NULL)<br>   "enforce_srid_the_geom" CHECK (srid(the_geom) = 4326)</div>

<div>balkan_20080908_0_0_1_postgis=# \d yolorta_all<br>                                  Table "public.yolorta_all"<br>  Column    |         Type          |                         Modifiers<br>-------------+-----------------------+--------------------------------------<br>
---------------------<br> gid         | integer               | not null default<br>nextval('yolorta_all_gid_seq'::regclass)<br> adi         | character varying(50) |<br> tipi        | character varying(25) |<br> rnname      | character varying(10) |<br>
 adiuluslar  | character varying(5)  |<br> kkno        | character varying(25) |<br> the_geom    | geometry              |<br> city        | character varying(50) |<br> country     | character varying(50) |<br> district    | character varying(50) |<br>
 subdistrict | character varying(50) |<br>Indexes:<br>   "yolorta_all_pkey" PRIMARY KEY, btree (gid)<br>   "yolorta_gid" UNIQUE, btree (gid) CLUSTER<br>   "yolorta_all_the_geom_gist" gist (the_geom)<br>
Check constraints:<br>   "enforce_dims_the_geom" CHECK (ndims(the_geom) = 2)<br>   "enforce_geotype_the_geom" CHECK (geometrytype(the_geom) =<br>'MULTILINESTRING'::text OR the_geom IS NULL)<br>
   "enforce_srid_the_geom" CHECK (srid(the_geom) = 4326)</div>
<div> </div>
<div>Is this time spent for the query meaningful? What can I do for faster spatial queries in this context?</div>
<div> </div>
<div>Regards,</div>
<div> </div>
<div>Balkan.</div>
<div> </div>
<div> </div></div>