Optmizing PostGIS minimum distance query

Question

I have the field_1 of my Table_1 that must have the minimum distance between the geometry of my Table_1 and any geometry of my Table_2. To do so, I used a CROSS JOIN (Full Cartesian Product) between Table_1 and Table_2. I also used the KNN operator <->.

My trouble is that it took more than 3 hours to run and I decided to abort the query while it was running.

Table_1 has 56230 entities.

Table_2 has 234843 entities.

Here is my code:

UPDATE Table_1
SET field_1 = list.distance
FROM(
    SELECT T1.gid AS name, MIN(T1.geom <-> T2.geom) AS distance
    FROM Table_1 AS T1
    CROSS JOIN Table_2 AS T2
    GROUP BY T1.gid
    ORDER BY T1.gid
     ) AS list
WHERE Table_1.gid = list.name;

Do you have any ideas with a lighter query that could execute this much faster ? I already have created spatial indices.

Answer 1

the <-> operator will use the index only if it is within the order by clause.

You can rewrite your query

select a.id,closest_pt.id, closest_pt.dist
from tablea a
CROSS JOIN LATERAL
  (SELECT
     id , 
     a.geom <-> b.geom as dist
     FROM tableb b
     ORDER BY a.geom <-> b.geom
   LIMIT 1) AS closest_pt;