[postgis-users] Increase KNN efficiency

[Vispo Leblanc]

Hello PostGIS users,

I am looking for a way to increase the speed of my query with KNN.

I have two tables with millions of points in each table. I would like to
find the nearest neighbor (only one) of my points in table A with my points
in table B.

I wrote the following query:

WITH index_query as (
  SELECT
    t1.id,
    t1.geom AS geom,
    t2.geom AS geom2,
    t1.geom <-> t2.geom AS distance
  FROM research_scrapper.table1 t1,
      research_scrapper.table2 t2
  ORDER BY t1.geom <-> t2.geom
)
SELECT tbl.cas_id, tbl.geom
FROM index_query tbl
INNER JOIN
    (
        SELECT geom2,
               MIN(distance) as distance
        FROM index_query
        GROUP BY geom2
    )tbl1
    ON tbl1.geom_generated=tbl.geom_generated
    AND tbl1.distance = tbl.distance

This is taking forever, would you have any suggestions on how to increase
the efficiency?

Thanks for your help,
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20180530/2065c600/attachment.html>