I need to get the intersection of two polygons and both sides use the same dataset (titlenumberpolygon - UK National polygons). PostGIS is used in the analysis.

First a spatial index was created which took 27 mins. There are 30 million polygons.

CREATE INDEX geoid ON titlenumberpolygon USING GIST(geom)

The following code is used and for both the tables the polygon data is coming from titlenumberpolygon table.

SELECT tb2.UPRN,tb1.titleno
    SELECT t1.titleno, tp.*
    FROM Ownership as t1
    INNER JOIN titlenumberpolygon as tp
    ON t1.titleno=tp.title_no) as tb1
    SELECT AU.UPRN,AU.Postcode,tp.*
    INNER JOIN titlenumberpolygon as tp
    ON AU.inspiredid=tp.poly_id) as tb2    
 ON ST_Intersects(ST_MakeValid(tb1.geom),ST_MakeValid(tb2.geom));

It takes over 2 hours to run for one postcode and there are 500,000 postcodes in the table.

How can I optimise this code? Maybe a different spatial indexing method as I am using the same table in the intersection.

  • I think you need to use the && operations to boost performance for bounding box. revenant.ca/www/postgis/workshop/indexing.html plus VACUUM your data.
    – Mapperz
    Mar 6, 2018 at 15:15
  • the st_makevalid functions in the st_intersects is probably what is making the query either slow or not use the spatial index. is the make valid function necessary?
    – ziggy
    Mar 6, 2018 at 15:18
  • 2
    Do NOT use ST_MakeValid on each feature -- This forces a full table scan. If the features are not valid, you should fix that first (once, not with the use of every row).
    – Vince
    Mar 6, 2018 at 15:19
  • Yes, putting functions in joins makes the query non-sargable, ie, no index can be used. Which data set is this, as a matter of interest. It sounds like one of the address ones. Mar 6, 2018 at 16:32
  • Thanks everyone. It was the ST_MakeValid function made it much slower. But this query would run for about 2 days to get the results.
    – Shana
    Mar 6, 2018 at 23:27

1 Answer 1


The index cannot be used, because you're intersecting with modified fields, but the index only knows of the unmodified fields.

As @Vince said, your best option would be to fix the polygons of the tables before intersecting or at insert.

UPDATE titlenumberpolygon SET geom = ST_MakeValid(geom);


INSERT INTO titlenumberpolygon (geom) VALUES (ST_MakeValid(geom));

If that is not a possibility, you could create the indices including ST_Make_Valid():

CREATE INDEX valid_geoid ON titlenumberpolygon USING GIST(ST_MakeValid(geom));
  • The technical term is non-sargable, see, for example, this post. I love the word, and wish it were more commonly understood. Mar 6, 2018 at 16:34
  • Thanks this is it and it is about sargable queries. But still this query would run for about 2 days to get the results.
    – Shana
    Mar 6, 2018 at 23:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.