I'm trying to speed up my query which should return only these polygons that are not completly overlapped by polygons above them.
My tables looks like this:
Map -> Layer -> Object
The object table has a generated geometry field and an spatial index on that:
CREATE INDEX ix_object_polygon ON object USING gist(postgis_area_geometry);
My layer-table has an order field, so a layer with sort_order value of 10 is above a layer with a sort_order value of 5. some layer are always on top, so the field is just null.
There are a total of 33556 Objects split in 22 layers. For this specific layer, my query takes 12 sec. There are 12 layer above this specific layer, the highest layer (sort_value = null) usually contains the most polygons, in this case about 28.000.
This calculation takes almost 2 minutes for all of my layers.
My first idea was to cache the union result, so PostGIS don't have to recalculate it every time, the user have to recalculate this union result once in a while to keep it up to date though. But ST_Union is not the bottleneck here, I probably would safe a minute in total if I'm lucky. Maybe the union is not covered by the spatial index but I'm not sure.
Any ideas how to improve this query?
SELECT * FROM object WHERE object.layer_id = 267 AND NOT ST_CONTAINS ( ( SELECT st_union(object.postgis_area_geometry) FROM object LEFT JOIN layer ON layer.id = object.layer_id WHERE layer.map_id = 22 AND (layer.sort_order > 10 OR layer.sort_order IS NULL) ), object.postgis_area_geometry );
Index Scan using ix_object_layer on object object (cost=13022.91..31276.17 rows=648 width=855) Index Cond: (layer_id = 267) " Filter: (NOT st_contains($0, postgis_area_geometry))" InitPlan 1 (returns $0) -> Aggregate (cost=13022.61..13022.62 rows=1 width=32) -> Hash Join (cost=6.08..11918.61 rows=8632 width=202) Hash Cond: (object_1.layer_id = layer.id) -> Seq Scan on object object_1 (cost=0.00..11760.79 rows=55879 width=210) -> Hash (cost=5.84..5.84 rows=19 width=8) -> Seq Scan on layer layer (cost=0.00..5.84 rows=19 width=8) Filter: (((sort_order > 10) OR (sort_order IS NULL)) AND (map_id = 22))