[postgis-users] st_coveredby query taking too much time

[Balkan Uraz]

Dear All;

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.

All the related info is below:

max_connections = 300
shared_buffers = 256MB
work_mem = 2MB
maintenance_work_mem = 32MB
max_fsm_pages = 153600
wal_buffers = 1MB
checkpoint_segments = 16
effective_cache_size = 2GB
CPU: Quad Core Intel Xeon 2.33GHz
Memory: 4GB
balkan_20080908_0_0_1_postgis=# explain update yolorta_all set city =
(select iladi from iller where st_coveredby(yolorta_all.the_geom,
iller.the_geom) = 'TRUE');
                              QUERY PLAN
-------------------------------------------------------------------------
 Seq Scan on yolorta_all  (cost=0.00..3458534.64 rows=1526838 width=675)
  SubPlan
    ->  Seq Scan on iller  (cost=0.00..2.21 rows=1 width=7)
          Filter: (($0 && the_geom) AND _st_coveredby($0, the_geom))
(4 rows)
balkan_20080908_0_0_1_postgis=# \d iller
                                 Table "public.iller"
 Column  |         Type          |                      Modifiers
----------+-----------------------+-----------------------------------------
------------
 gid      | integer               | not null default
nextval('iller_gid_seq'::regclass)
 plakano  | integer               |
 iladi    | character varying(15) |
 ilceadi  | character varying(20) |
 the_geom | geometry              |
Indexes:
   "iller_pkey" PRIMARY KEY, btree (gid)
   "iller_gid" UNIQUE, btree (gid) CLUSTER
   "iller_the_geom_gist" gist (the_geom)
Check constraints:
   "enforce_dims_the_geom" CHECK (ndims(the_geom) = 2)
   "enforce_geotype_the_geom" CHECK (geometrytype(the_geom) =
'MULTIPOLYGON'::text OR the_geom IS NULL)
   "enforce_srid_the_geom" CHECK (srid(the_geom) = 4326)
balkan_20080908_0_0_1_postgis=# \d yolorta_all
                                  Table "public.yolorta_all"
  Column    |         Type          |                         Modifiers
-------------+-----------------------+--------------------------------------
---------------------
 gid         | integer               | not null default
nextval('yolorta_all_gid_seq'::regclass)
 adi         | character varying(50) |
 tipi        | character varying(25) |
 rnname      | character varying(10) |
 adiuluslar  | character varying(5)  |
 kkno        | character varying(25) |
 the_geom    | geometry              |
 city        | character varying(50) |
 country     | character varying(50) |
 district    | character varying(50) |
 subdistrict | character varying(50) |
Indexes:
   "yolorta_all_pkey" PRIMARY KEY, btree (gid)
   "yolorta_gid" UNIQUE, btree (gid) CLUSTER
   "yolorta_all_the_geom_gist" gist (the_geom)
Check constraints:
   "enforce_dims_the_geom" CHECK (ndims(the_geom) = 2)
   "enforce_geotype_the_geom" CHECK (geometrytype(the_geom) =
'MULTILINESTRING'::text OR the_geom IS NULL)
   "enforce_srid_the_geom" CHECK (srid(the_geom) = 4326)

Is this time spent for the query meaningful? What can I do for faster
spatial queries in this context?

Regards,

Balkan.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20080923/f94ca7f5/attachment.html>