I have two polygon tables, 'cities' with ~5600 rows and 'lte_coverage' with ~469314 rows. I need to know wich cities have LTE Coverage.
Using ArcMap, I get this answer in about a minute, using 'Select By Location', but using PostGIS it takes really long, indeed after 90 minutes I cancelled the query. I am wondering what can I do to get a better performance on PostGIS.
Table cities:
Table "public.cities" Column | Type | Modifiers | Storage | Stats target | Description ------------+-------------------------+----------------------------------------------------------------+----------+--------------+------------- id | integer | not null default nextval('cities_gid_seq'::regclass) | plain | | geom | geometry(MultiPolygonM) | | main | | Indexes: "cities_pkey" PRIMARY KEY, btree (id) "cities_geom_idx" gist (geom)
Table lte_coverage:
Table "public.lte_coverage" Column | Type | Modifiers | Storage | Stats target | Description ------------+------------------------+---------------------------------------------------------------------+---------+--------------+------------- gid | integer | not null default nextval('lte_coverage_gid_seq'::regclass) | plain | | geom | geometry(Polygon,4326) | | main | | Indexes: "lte_coverage_pkey" PRIMARY KEY, btree (id) "lte_coverage_geom_idx" gist (geom)
I am using this query
SELECT distinct c.id FROM cities c, lte_coverage l WHERE ST_Intersects(c.geom,l.geom)
And I have this explain output:
HashAggregate (cost=304837.32..304892.99 rows=5567 width=8) Group Key: c.id -> Nested Loop (cost=0.29..302660.09 rows=870890 width=8) -> Seq Scan on cities c (cost=0.00..4715.67 rows=5567 width=40) -> Index Scan using lte_coverage_geom_idx on lte_coverage l (cost=0.29..53.36 rows=16 width=1132) Index Cond: (c.geom && geom) Filter: _st_intersects(c.geom, geom)
I am using
gisdb01=# select version(); version --------------------------------------------------------------------------------------------------------- PostgreSQL 9.5.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-4), 64-bit (1 row) gisdb01=# select postgis_full_version(); postgis_full_version ------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ---- POSTGIS="2.2.4 r15258" GEOS="3.5.0-CAPI-1.9.0 r4084" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.11.4, released 2016/01/25" LIBXML="2.9.1" LIBJSON="0.11" TOPOLOGY RAS TER (1 row)
So, is there anything I can do to PostGIS perform better?