Poor SQL for nearest neighbor join

[pramsey]

The closest.js implementation does some potentially very bad stuff in finding the nearest neighbors,

camshaft/lib/node/nodes/closest.js

Lines 33 to 41 in 1592497

	' SELECT * FROM (',
	' SELECT *,',
	' row_number() over(',
	' {{? it.category !== null}}PARTITION BY {{=it.category}}{{?}}',
	' ORDER BY source.the_geom <-> the_geom',
	' ) AS ranking',
	' FROM target',
	' ) AS ranked',
	' WHERE ranking <= {{=it.responses}}',

Running a ORDER BY <-> without a LIMIT will sort the whole relation for you. Then you number the rows and take the top N. Then because you're in a LATERAL CROSS JOIN you do it again. And again. And again.

It might be possible to do this while retaining the "top N by category" behaviour, but a short-term hack could just switch between a simpler formulation using SELECT * FROM foo ORDER BY __ <-> __ LIMIT <n> for the case where not category behaviour is requested, which will be much faster.