ST_Intersects AND ST_Touches combination is much slower with large geometry input

Question

I am trying to geom columns from multiple table and later combining with a table which only has linestrings, I am finding lines which are not intersecting OR just touching the union of geom columns. But it's very slow. The result of union is single row with size ~ 160kb. I have tried using ST_Subdivide but still the result is same. I also tried using ST_DWithin() but still no luck.

WITH union_table AS (
    SELECT ST_Union(geom) AS geom FROM (
    SELECT geom FROM table1
    UNION ALL
    SELECT geom FROM table2)
),
intersection_table AS (
    SELECT table3.geom FROM table3
    LEFT JOIN union_table ut ON ut.something = table3.something
    WHERE
    (ST_Intersects(table3.geom, ut.geom) = false OR
    ST_Touches(table3.geom, ut.geom) = true)
)
SELECT geom FROM intersection_table

Answer 1

Spatial non-predicates are tricky. Usually you want to inverse your problem definition to gain on performance and simplicity; e.g. in this case:

Find all target.geoms that spatially overlap with reference.geoms, then select all target.geoms that are not in the above set.

In PostgreSQL/PostGIS you would want to try a correlated exclusion filter query, i.e. using EXISTS like so:

SELECT
  tgt.*
FROM
  table3 AS tgt
WHERE
  NOT EXISTS (
    SELECT
      1
    FROM
      table1 AS ref
    WHERE
      ST_Crosses(tgt.geom, ref.geom)
  )
UNION
SELECT
  tgt.*
FROM
  table3 AS tgt
WHERE
  NOT EXISTS (
    SELECT
      1
    FROM
      table2 AS ref
    WHERE
      ST_Crosses(tgt.geom, ref.geom)
  )
;

or

SELECT
  tgt.*
FROM
  table3 AS tgt
WHERE
  NOT EXISTS (
    SELECT
      1
    FROM
      table1 AS ref
    WHERE
      ST_Crosses(tgt.geom, ref.geom)
  )
  OR
  NOT EXISTS (
    SELECT
      1
    FROM
      table2 AS ref
    WHERE
      ST_Crosses(tgt.geom, ref.geom)
  )

It is possible that the planner actually runs the same execution plan for both queries, or that it may be able to spin up worker threads in the UNION'ed approach. The OR'ed approach is likely a tad faster by itself - but test yourself.

Depending on your geometry setup, you'd need switch from ST_Crosses to ST_Overlaps - see their docs for when either one is suitable. If none applies, use ST_Relate in conjunction with a && filter.

UNION (without ALL) will return a result set of distinct row value combinations - which doesn't come for free, but should beat other means of set distinction here (you could try an outer GROUP BY to compare).

---

Needless to say, eliminating the need to filter from two reference tables per query would certainly have the most significant effect - if you can, and plan to run this over and over, consider merging table1.geom and table2.geom into a separate relation (referenced from the source tables via PK).