I have a big query that I am looking to optimize. I am matching millions (expanding daily) of rows with Points against a mostly read-only table of (tens of thousands of) areas with (Multi)Polygons. The areas table contains 3 or more layers of overlapping areas of decreasing size (think country -> county -> municipality) in addition to user-defined areas that may overlap arbitrarily. Most of these polygons are made up of <300 points, some of 5-30 thousand points.
In the query I am joining
areas to aggregate an array of area UUIDs
SELECT positions.id, array_agg(areas.uuid) ... to back a materialized view. I would like to refresh this materialized view about every fifteen minutes, but currently the query takes a couple of minutes to finish. I have managed a 2x orders of magnitude speedup by creating a materialized view of areas with subdivided geometry, but I am worried that this too will slow down over time.
My idea to speed this up even further is as follows: Pre-compute a table of all overlapping areas with the UUID array populated, like in the image below, so that each point will only ever be contained by ONE polygon with the UUIDs already aggregated. This will probably also benefit from subdivision.
Is this likely to result in a good speedup? How would I construct this query?
This question seems to be almost exactly what I am asking, but I lack the expertise to account for holes and MultiPolygons. Additionally I tried to correlate the resulting geometry collection with my areas to aggregate UUIDs and am not getting the results I expect:
WITH geometry AS ( SELECT (ST_Dump(ST_Polygonize(geom))).geom AS geom FROM ( SELECT ST_Union(geom) AS geom FROM ( SELECT ST_ExteriorRing(geography::geometry) AS geom FROM areas ) AS noded_lines ) AS polygons ) SELECT array_agg(areas.uuid) id, ST_AsText(geom) FROM geometry -- geom should be contained by at least one area INNER JOIN areas ON ST_Contains(geom, areas.geography::geometry) GROUP BY geom
This query returns just 37 rows, where each of the aggregated UUID arrays only contain one entry. This is obviously incorrect for 800+ areas where I know for a fact every split polygon should be contained within at least 3 areas.
I'll go for the simple subdivision to get decent speedup for now, but I would very much like to try this optimization if it can be made to work. If only for my own sanity.