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

[Gregory S. Williamson]

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