Joining relations based on distance between geometry columns?
ST_MakePoint(lat, long)



That's the wrong point order. You want ST_MakePoint(long,lat), check the docs here ST_MakePoint


AND ST_Distance("origin", ST_MakePoint(lat, long), false) - radius &lt;= 0 



That doesn't make any sense, it should be

ST_DWithin(origin, ST_MakePoint(long,lat), radius)


I think this is what you want,

SELECT * 
FROM user AS u
INNER JOIN regions AS r 
  ON u.id = r.userID
  AND ST_DWithin(r.origin, ST_MakePoint(long,lat)::geog, radius)
ORDER BY ST_MakePoint(long,lat) &lt;-&gt; r.origin
FETCH FIRST 10 ROWS ONLY


maxdistance and radius are the same thing. You have a LIMIT 10 there, what is the ORDER that you want? Do you just want any 10 rows?

Don't forget to add your spatial index. In this case you can actually add a compound index.

CREATE EXTENSION btree_gist;
CREATE INDEX ON region USING GIST (userID, origin);


See also,


Finding the nearest geo points across two tables?