Disjoint is very slow in Virtual Layer but fast on symbology

Question

In QGIS, take a polygon grid (named 'grid') and a multipolygon layer (named 'surfaces').

For each feature in surfaces, I want to union all features of the grid that are disjoint (not intersected) with. I need to do this with a virtual layer (datasets regularly updated and exported with an atlas).

I have tried this two solutions, they work but are extremely slow (4-5 minutes):

select s.id, st_union(g.geometry)
from grid g, surfaces s
where not(st_intersects(g.geometry,s.geometry)) -- st_dissolve also works but is slower...
group by s.id

---

select s.id, st_union(g.geometry)
from grid g
join surfaces s on not(st_intersects(g.geometry,a.geometry))  -- st_dissolve also works but is slower...
group by s.id

The problem seems to be the intersects. The time is about the same with or without st_union.

For testing, I tried to add a rule-based symbology to my grid, with this rule:

not(intersects($geometry, @atlas_geometry ))

And I get the result instantly. Why intersects is slow on the Virtual Layer and fast in symbology? And how can I get a fast result in the Virtual layer?

---

EDIT

What I want to achieve (without union to see the grid):

And with union, to calculate the area of each part of the grid:

Answer 1

The issue is the cross join on non-intersection. Your query reads:

• for every row in g, return every row in s that does not intersect with the current g

which seems in fact what you want, but about the worst performing operation on unfiltered and non-indexed relations in SQL.

Example: for 100 rows in g and 100 rows in s, and with no intersection between any of them, you would get 10.000 rows in return that will then get grouped in a (costly) geometric union.

---

QGIS likely utilizes a low-level, in-memory index structure on loaded layers, and the expression is executed on features inside the view-port only - both of these facts do not apply to SQLite-interpreted Virtual Layers.