How do I optimize a spatial query in PostGIS

Question

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
    );

Explain:

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))

Answer 1

I think you should filter your data with your object geom before you union them. By focussing on only the geom that can intersect your geom, you can make use of the index filter, and depending on the situation you can possibly gain a lot of compute time.

Here is an idea on what it can look like:

WITH new_object AS (
    SELECT
        id,
        postgis_area_geometry
    FROM
        object
    WHERE
        object.layer_id = 267
), above_objects AS (
    SELECT
        object.postgis_area_geometry as above_geom
    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)
), new_object_with_above AS (
    SELECT
        new_object.id,
        new_object.postgis_area_geometry,
        ST_Union(above_geom) as above_geoms
    FROM
        new_object
    LEFT JOIN
        above_objects
    ON ST_Intersects(new_object.postgis_area_geometry, above_objects.above_geom)
    GROUP BY new_object.id, new_object.postgis_area_geometry
)
SELECT
    id,
    postgis_area_geometry
FROM
    new_object_with_above
WHERE
    above_geoms IS NULL 
    OR NOT ST_CONTAINS
    (
        above_geoms,
        postgis_area_geometry
    );

Note: I decomposed it with CTE to try to be more clear, but there is probably more efficient ways of doing this. Be sure to try this on postgres 12 or above, otherwise you probably need to rewrite this query without CTE or you will probably not use geom index, as it will materialized each step instead of being more clever. Also, I didn't test it, maybe it's not doing much and you can try to rewrite it for example looking into LATERAL type of join to do the same idea ?