[postgis-users] st_coveredby query taking too much time

[Martin Davis]

Am I right in reading the query plan to mean that the spatial index on 
iller is not being used?  Not sure why this would be.  Perhaps try 
explicitly adding the && test?

Balkan Uraz wrote:
> 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.
>  
>  
> ------------------------------------------------------------------------
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>   

-- 
Martin Davis
Senior Technical Architect
Refractions Research, Inc.
(250) 383-3022