Compare row to all others in PostGIS

Question

Given a PostGIS table of polygons, what is the best way to check if each geometry is inside the union (ST_Collect) of all other geometries from the same table?

I have tried the following:

WITH first AS (
  SELECT geom, row_number OVER () AS row_no
  FROM test
)
SELECT geom, row_no, (
  SELECT ST_Contains(ST_Collect(b.geom), a.geom)
  FROM first b
  WHERE b.row_no != a.row_no
) isinside
FROM first a

While this works, the subquery can be very slow with a large number of geometries. Is there a more efficient way to achieve the same result?

Answer 1

A geometry can only be fully contained by things it intersects with, so reduce the unioning problem to just that subset, then test each feature on its potential container.

WITH containers AS (
 SELECT ST_Union(a.geom) AS GEOM, b.gid
  FROM tbl a JOIN tbl b
  ON ST_Intersects(a.geom, b.geom)
  WHERE a.gid != b.gid
)
SELECT tbl.gid
 FROM tbl JOIN containers 
 ON containers.gid = tbl.gid
 WHERE ST_Covers(containers.geom, tbl.com)

I use covers instead of contains to avoid edge effects in the case of instances where there's two duplicate geometries or geometries with duplicate edges and no neighbours.

Answer 2

Non Tested code, idea behind this is that it should be faster to create union from geoms that are disjoint than create union from all geoms in table.Other query uses ST_RELATE to find geoms that have relation, (TT*TT****) might be faster logic , there were some mentions about indexes and disjoint operation)

WITH tested as (SELECT t.geom, row_number OVER () AS row_no
  FROM test t
)
 SELECT tested.row_no , ST_Contains(tested.geom,ST_UNION(t.geom)) FROM test t 
 WHERE 
 t.row_no != tested.row_no
AND ST_Relate(tested.geom,t.geom,'FF*FF****') IS NOT TRUE -- not sure how this works in postgresql anymore, too much MSSQL

OR

  SELECT tested.row_no , ST_Contains(tested.geom,ST_UNION(t.geom)) FROM test t 
     WHERE 
     t.row_no != tested.row_no
    AND ST_Relate(tested.geom,t.geom,'TT*TT****') IS TRUE -- not sure how this works in postgresql anymore, too much MSSQL