[postgis-users] Very long time for a bbox/within query

[Paul Ramsey]

I think I like this construction better...

select v_boundary.v_bndry_id, count(*)
from parcels,v_boundary
where
   v_boundary.the_geom && parcels.the_geom
   and
   within(v_boundary.the_geom,parcels.the_geom)
   and
   v_bndry_type = 'FIPS'
group by v_boundary.v_bndry_id;

Gregory S. Williamson wrote:

> Dear peoples,
> 
> I have a query which is taking a while to complete and I am wondering if I have missed some obvious thing.
> 
> We have a lot of vector data in various tables (statistics, roads, flood plains, parcel data, etc.). We need to be able to know at runtime which types of data for a given geographic area (currently a county, or FIPs code  but this might be a zip or MSA in other applications). So, I thought, lets build a cross-reference table with an entry for each table/area which has data.
> 
> Works well for most of the tables (lines, points and polygons), taking a few minutes to cross reference the 5000+ FIPS for each target table. Alas, though, we have a modest size table (parcels, about 4.1 million rows) which takes so long to run that i was convinced I had a bug. Eventually was patient enough to let it run and lo! and behold! it does work, but slowly.
> 
> Any suggestions would be most welcome !
> 
> This is the current query:
> 
> SELECT 1 FROM parcels WHERE the_geom && (SELECT box3d((SELECT v_bndry_geometry FROM v_boundry WHERE v_bndry_type = 'FIPS' AND v_bndry_id =
>  '12009')))::box3d AND within(the_geom, (SELECT v_bndry_geometry FROM v_boundry WHERE v_bndry_type = 'FIPS' AND v_bndry_id = '12009'));
> 
> And this is what explain analyze says:
>  Index Scan using parcels_s_ndx on parcels  (cost=154.24..198.39 rows=7 width=0)
>  (actual time=118806.770..118806.770 rows=0 loops=1)
>    Index Cond: (the_geom && ($1)::geometry)
>    Filter: within(the_geom, $2)
>    InitPlan
>      ->  Result  (cost=77.11..77.13 rows=1 width=0) (actual time=9.209..9.261 rows=1 loops=1)
>            InitPlan
>              ->  Seq Scan on v_boundry  (cost=0.00..77.11 rows=2 width=32) (actual time=5.670..6.327 rows=1 loops=1)
>                    Filter: (((v_bndry_type)::text = 'FIPS'::text) AND ((v_bndry_id)::text = '12009'::text))
>      ->  Seq Scan on v_boundry  (cost=0.00..77.11 rows=2 width=32) (actual time=
> 5.403..6.063 rows=1 loops=1)
>            Filter: (((v_bndry_type)::text = 'FIPS'::text) AND ((v_bndry_id)::text = '12009'::text))
>  Total runtime: 118807.170 ms
> (11 rows)
> 
> The parcels table looks like this:
> gex_vector=# \d parcels
>                                     Table "public.parcels"
>      Column     |       Type        |                        Modifiers
> 
> ----------------+-------------------+-------------------------------------------
> ---------------
>  gid            | integer           | not null default nextval('public.parcels_g
> id_seq'::text)
>  s_pin          | character varying |
>  s_id           | character varying |
>  s_parent_p     | character varying |
>  s_state        | character varying |
>  s_city         | character varying |
>  s_zip          | character varying |
>  s_zip_plus     | character varying |
>  s_house        | character varying |
>  s_house2       | character varying |
>  s_fraction     | character varying |
>  s_directio     | character varying |
>  s_street       | character varying |
>  s_suffix       | character varying |
>  s_post_dir     | character varying |
>  s_unit         | character varying |
>  s_unit         | character varying |
>  s_unit_des     | character varying |
>  s_r_type       | character varying |
>  s_r_number     | character varying |
>  s_box_numb     | character varying |
>  s_fips_cou     | character varying |
>  the_geom       | geometry          |
>  track_id       | integer           |
>  ownername      | character varying |
>  owneraddre     | character varying |
>  owneraddre__21 | character varying |
>  owneraddre__22 | character varying |
>  ownercity      | character varying |
>  ownerstate     | character varying |
>  ownerzip       | integer           |
>  ownerzip4      | character varying |
>  the_cntr       | geometry          |
> Indexes:
>     "parcels_pkey" primary key, btree (gid)
>     "parcel_cent_ndx" gist (the_cntr)
>     "parcels_ownername_ndx" btree (ownername)
>     "parcels_s_fips_cou" btree (s_fips_cou)
>     "parcels_s_house_ndx" btree (s_house)
>     "parcels_s_ndx" gist (the_geom)
>     "parcels_s_pin_ndx" btree (s_pin)
>     "parcels_s_street_ndx" btree (s_street)
> Check constraints:
>     "$1" CHECK (srid(the_geom) = 4326)
>     "$2" CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL)
> Foreign-key constraints:
>     "trackid_fk" FOREIGN KEY (track_id) REFERENCES v_detail(track_id)
> 
> [overindexed I think but this isn't "mine" in design]
> 
> And the boundry table is:
>  v_bndry_type     | character varying(20)  | not null
>  v_bndry_id       | character varying(100) | not null
>  v_bndry_ref_id   | integer                | not null default nextval('public.v_boundry_v_bndry_ref_id_seq'::text)
>  v_bndry_geometry | geometry               |
> Indexes:
>     "v_boundry_pkey" primary key, btree (v_bndry_ref_id)
> Check constraints:
>     "$2" CHECK (srid(v_bndry_geometry) = -1)
> Foreign-key constraints:
>     "$1" FOREIGN KEY (v_bndry_type) REFERENCES v_bndry_types(v_bndry_t_code)
> 
> Thanks for your time !
> 
> Greg Williamson
> DBA
> GlobeXplorer LLC
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users