I'm trying to use the Postgis KNN functionality as described here: https://postgis.net/docs/geometry_distance_knn.html

I have one table with all point geometries. The table has many samples, and in each sample there are various types of points. I would like to calculate the distance from every point to the nearest point of each type in the same sample. The following query does this:

select distinct on (t.sample_name,t.local_point_id,f.point_type)
  st_distance(t.geom,f.geom ) from point_table t join point_table f  on
       t.sample_name=f.sample_name and t.local_cell_id!=f.local_cell_id
order by t.sample_name,t.local_cell_id,f.phenotype,t.geom <-> f.geom;

However, it gets quite slow. Given the note on the link above about 9.5 and KNN functionality, I'm guessing that this query is not using the KNN index correctly. So my questions are the following:

  1. How can I tell if the KNN functionality is being used correctly (analyze explain shows a pretty high cost, but I'm not sure what to compare it to)?
  2. If it isn't using the KNN functionality optimally, how do I correct it to do so?


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.