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 > r.rownum - 2
    AND t.rownum  r.colnum - 2
    AND t.colnum < 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 > r.rownum - 2
    AND t.rownum < 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 > r.rownum - 2
    AND t.rownum  r.colnum - 2
    AND t.colnum < 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.

StackExchange Database Administrators Q#177541, answer score: 4