I have a query that is slow on PostgreSQL 9 + PostGIS (running on a Linux computer), despite my efforts to improve its speed.
I basically have a table that contains Polygons called polygones_zones. Actually these polygons come from MultiPolygons that represent land areas broken down into mere polygons in a previous step using the PostGIS function ST_Dump() - the reason for doing this was to improve the speed of the query. This table has about 330,000 rows.
On the other hand, I have a table with Points called sites. This table has about 290,000 rows but when running the query, I currently use only a ~4000 sample of them before using the spatial joint.
I want to know in which areas (the original MultiPolygons) the Points are contained (Postgis function ST_Contains() ).
Everything has a 2D geometry : the Polygons were created using ST_Force2D and on the table with the points, there is a constraint "CHECK (ST_NDims(geom) = 2)". All geometries have been controlled as valid.
I have a GIST index on the geometry columns of both tables and have run a VACUUM ANALYZE and a TABLE REINDEX on both of them just before running the query.
The table definitions :
CREATE TABLE polygones_zones (
pkid serial PRIMARY KEY,
category varchar(50) NOT NULL,
value varchar (255),
origine varchar(255) NOT NULL,
origine_id integer,
geom geometry(Polygon, 2154)
CONSTRAINT enforce_dims_geom CHECK (ST_NDims(geom) = 2)
);
CREATE INDEX polygones_zones_geometry ON polygones_zones USING GIST(geom);
CREATE INDEX ON polygones_zones (origine, origine_id) ;
CREATE TABLE sites (
pkid serial PRIMARY KEY,
origine varchar(20) NOT NULL,
origine_id varchar(20) NOT NULL,
status varchar(255) NOT NULL,
geom geometry(Point, 2154),
CONSTRAINT enforce_dims_geom CHECK (ST_NDims(geom) = 2)
);
CREATE UNIQUE INDEX ON sites (origine, origine_id ) ;
CREATE INDEX ON sites (status);
CREATE INDEX sites_geometry ON sites USING GIST ( geom );
And now the query :
SELECT
sites.pkid,
zones.origine ,
zones.origine_id ,
FALSE,
zones.category,
zones.value
FROM
(
SELECT pkid, geom
FROM sites
WHERE status = 'selected'
) AS sites
JOIN
(
SELECT DISTINCT ON (origine_id , origine)
category, value, geom, origine_id , origine
FROM polygones_zones
) AS zones
ON ST_Contains(zones.geom , sites.geom)
;
The reason for the "DISTINCT ON (origine_id, origine)" is that when the MultiPolygons have been broken down into Polygons, the identifiers of the original MultiPolygons have been recorder in these two fields. Hence, if a multipolygon has been broken into two overlapping polygons that both contain on of the points in the table sites , without the DISTINCT, the JOIN would return two rows instead of one. Using the DISTINCT ON ensures that for a single original MultiPolygon, the JOIN will only return one row.
Running this query on the sample of Points that is about 1/100 of my data takes about 30 mn. That would mean the query on the whole data set should take about 50 hours ! What can I do to improve its speed ?
Edit : after John Powell's pertinent comment, here is the result of EXPLAIN :
"Nested Loop (cost=525286.40..547598.60 rows=24 width=40)"
" Join Filter: ((polygones_zones.geom ~ sites.geom) AND _st_contains(polygones_zones.geom, sites.geom))"
" -> Index Scan using sites_statut_idx on sites (cost=0.42..4.44 rows=1 width=36)"
" Index Cond: ((statut)::text = 'selected'::text)"
" -> Unique (cost=525285.98..527794.86 rows=72658 width=1599)"
" -> Sort (cost=525285.98..526122.27 rows=334517 width=1599)"
" Sort Key: polygones_zones.origine_id, polygones_zones.origine"
" -> Seq Scan on polygones_zones (cost=0.00..30379.17 rows=334517 width=1599)"
CREATE INDEX sites_geometry ON sites_candidats USING GIST ( geom );