Why is this integer range selection so slow?
First thing stop using CTEs.

with structure as (
    SELECT s.id, s.terr_id
    FROM structures s
    WHERE s.id=55
)
SELECT r.id
FROM territories t
INNER JOIN structure ON (t.id = structure.terr_id)
LEFT JOIN resources r ON (
    t.rownum &gt; r.rownum - 2
    AND t.rownum &lt; r.rownum + 2
    AND t.colnum &gt; r.colnum - 2
    AND t.colnum &lt; r.colnum + 2
)


Get's rewritten as..

SELECT r.id
FROM structures AS s
JOIN territories t
  ON (t.id = s.terr_id)
LEFT JOIN resources r
  ON (
    t.rownum BETWEEN (r.rownum - 1) AND (r.rownum + 1)
    AND t.colnum BETWEEN (r.colnum - 1) AND (r.colnum + 1)
  )
WHERE s.id=55;


And equijoins are always going to be faster


They never fan and they're easy to optimize.
They're direct index lookups.