Postgresql – PostGIS Intersects Query takes lot of time to get the result

optimizationpostgispostgresqlquery-performance

I am developing a webmapping application in which I used PosgreSQL with PostGIS plugin as my DB.

I have this two tables namely; evidensapp_polystructures and evidensapp_seniangcbr. Both tables contain geometry(multipolygons).

-- Table: evidensapp_seniangcbr

-- DROP TABLE evidensapp_seniangcbr;

CREATE TABLE evidensapp_seniangcbr
(
  id serial NOT NULL,
  hazard character varying(16) NOT NULL,
  geom geometry(MultiPolygon,32651),
  CONSTRAINT evidensapp_seniangcbr_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE evidensapp_seniangcbr
  OWNER TO postgres;

-- Index: evidensapp_seniangcbr_geom_id

-- DROP INDEX evidensapp_seniangcbr_geom_id;

CREATE INDEX evidensapp_seniangcbr_geom_id
  ON evidensapp_seniangcbr
  USING gist
  (geom);
ALTER TABLE evidensapp_seniangcbr CLUSTER ON evidensapp_seniangcbr_geom_id;


-- Table: evidensapp_polystructures

-- DROP TABLE evidensapp_polystructures;

CREATE TABLE evidensapp_polystructures
(
  id serial NOT NULL,
  bldg_name character varying(100) NOT NULL,
  bldg_type character varying(50) NOT NULL,
  brgy_locat character varying(50) NOT NULL,
  municipali character varying(50) NOT NULL,
  province character varying(50) NOT NULL,
  geom geometry(MultiPolygon,32651),
  CONSTRAINT evidensapp_polystructures_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE evidensapp_polystructures
  OWNER TO postgres;

-- Index: evidensapp_polystructures_geom_id

-- DROP INDEX evidensapp_polystructures_geom_id;

CREATE INDEX evidensapp_polystructures_geom_id
  ON evidensapp_polystructures
  USING gist
  (geom);
ALTER TABLE evidensapp_polystructures CLUSTER ON evidensapp_polystructures_geom_id;

With my query below it takes almost 15mins. to get the result.

-- Executing query:
SELECT  COUNT(evidensapp_polystructures.brgy_locat ) as High,evidensapp_polystructures.brgy_locat as Barangay, evidensapp_polystructures.municipali as Municipality
FROM  evidensapp_seniangcbr  INNER JOIN  evidensapp_polystructures  ON ST_Intersects( evidensapp_seniangcbr.geom ,  evidensapp_polystructures.geom )  WHERE  evidensapp_seniangcbr.hazard  = 'Low' GROUP BY  evidensapp_polystructures.brgy_locat , evidensapp_polystructures.municipali
Total query runtime: 896688 ms.
23 rows retrieved.

It is not acceptable. So, how do I optimize it? I've done spatial indexing and clustering based on this site.

Best Answer

I suggest to calculate the intersection of the geometries beforehand only once and store it in an additional table evidensapp_seniangcbr_polystructures_intersection containing an id, evidensapp_seniangcbr_id pointing to evidensapp_seniangcbr.id and evidensapp_polystructures_id pointing to evidensapp_polystructures.id.

Then you can get rid of the geometry intersection in

INNER JOIN  evidensapp_polystructures ON ST_Intersects( evidensapp_seniangcbr.geom ,  evidensapp_polystructures.geom )````

and instead use a join using the ids

INNER JOIN evidensapp_seniangcbr_polystructures_intersection intersec ON evidensapp_seniangcbr.id = intersec.evidensapp_seniangcbr_id 
INNER JOIN evidensapp_polystructures ON evidensapp_polystructures.id = intersec.evidensapp_polystructures_id

Off course this only works, if your data does not change. Otherwise you need a trigger doing the intersection and filling the evidensapp_seniangcbr_polystructures_intersection table.

Related Question