Why is this integer range selection so slow? I'm currently doing some inner joins in PostgreSQL, which I know aren't the speediest, but I've run into an efficiency issue.

The following query returns in 167ms

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
)


This query returns 9 rows. The columns are filled with primitives.

The table territories has 5 million rows. The table resources has 400,000 rows. The structure query is negligible.

I have indices set up on all of the rows involved. When I simply query as such:

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 = r.colnum
)


eliminating the colnum variation, the query returns 3 rows and only takes 11ms. If I were to run each row individually it would only take 33ms, a fraction of what it takes when combined. I've tried the BETWEEN keyword, using a box contains method, etc. Can anyone point me in the right direction?
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.