Because it is in a beta thread, I'm quoting part of it here. I've added references in [] and highlighted timings.
...
[M8] Another query in 8:
--!fullfetch
SELECT parks.id AS parkid, count(parcels.id) AS num
FROM parks, parcels
WHERE Touches(parks.id, parcels.id) AND
Area(Buffer(parks.id, 200)) > 2 * Area(Buffer(parcels.id, 200))
GROUP BY parks.id
We take each park, find all parcels that touch it, then either leave or reject the parcel based on some convoluted criteria regarding its area compared to the area of the park (which is, again, to try and make operations take some time for performance comparisons).
The result: 9.230 sec (good).
[RS1] The straightforward attempt to port to RS again:
--SQL
SELECT p.mfd_id AS parkid, count(q.mfd_id) AS num
FROM [parks table] AS p, [parcels table] AS q
WHERE GeomTouches(p.[geom (i)], q.[geom (i)], 0) AND
GeomArea(GeomBuffer(p.[geom (i)], 200, 0), 0) >
2 * GeomArea(GeomBuffer(q.[geom (i)], 200, 0), 0)
GROUP BY p.mfd_id
THREADS 8 BATCH 4
This runs much better than 8 straight out of the box (multiple reasons): 3.250 sec. Threads, however, don't help.
[RS2] Second attempt, let's move computations into a subquery and leave GROUP outside:
--SQL
SELECT parkid, count(parcelid) AS num
FROM (
SELECT p.mfd_id AS parkid, q.mfd_id AS parcelid
FROM [parks table] AS p, [parcels table] AS q
WHERE GeomTouches(p.[geom (i)], q.[geom (i)], 0) AND
GeomArea(GeomBuffer(p.[geom (i)], 200, 0), 0) >
2 * GeomArea(GeomBuffer(q.[geom (i)], 200, 0), 0)
THREADS 8 BATCH 4
)
GROUP BY parkid
Nope, same 3.260 sec. Threads don't help.
[RS3] Third attempt, rework the inner subquery:
--SQL
SELECT parkid, count(parcelid) AS num
FROM (
SELECT p.mfd_id AS parkid, q.mfd_id AS parcelid,
GeomArea(GeomBuffer(p.[geom (i)], 200, 0), 0) >
2 * GeomArea(GeomBuffer(q.[geom (i)], 200, 0), 0) AS valid
FROM [parks table] AS p, [parcels table] AS q
WHERE GeomTouches(p.[geom (i)], q.[geom (i)], 0)
THREADS 8 BATCH 4
)
WHERE valid
GROUP BY parkid
Without threads, we have the same 3.260 sec (that's OK), but threads now help, the time with threads is 0.640 sec (!). Because we moved the expensive computations from WHERE into the SELECT list, where they can be accelerated.
...
See the changes made in RS2, but especially in RS3. This is really thinking outside the Rect (sorry, can't resist).