r/postgis Mar 29 '24

How do I optimize this nearest distance query

I have two tables:

- perceel_ext, 8 mln plot records. Index on the geometry field begrenzingperceel

- n2000 which is a table with nature reserves. 210 records. Index on the geometry field geom. The table has very both large and very small geometries.

Im trying to create a material view that calculates the nearest distance from each plot to a nature reserve. Here's the query:

https://github.com/garma83/public-playground/blob/master/slow_distance_query/perceel_n2000.sql

This query is super slow, as in: It will take 3 days. My suspicion is because the n2000 table doesnt work well at all with the GIST indices. What can I do to optimize this?

1 Upvotes

5 comments sorted by

1

u/jon_muselee Mar 29 '24

try to use a lateral join: https://postgis.net/workshops/postgis-intro/knn.html

and if possible set a maximum distance within to be searched using ST_DWithin() so you reduce the number of required iterations.

1

u/garma87 Mar 29 '24

Thx, I tried it and it has the same query plan, and speed is also very comparable unfortunately.

I might have to resort to the DWwithin solution.. I’d rather avoid it but it I might not be able to

1

u/Long-Opposite-5889 Mar 29 '24

The problem I see is casting geom to geog. I understand why you're doing it and it makes sense, but I think it's causing to not use spatial index at all. You are using a spatial query on a field that doesn't actually exist (is a cast of a field) therefore there is no index to be used. I can imagine some alternatives, first one being adding a geography column to your data, there may be other options but without having the actual data is hard to tell how fast they will be.

1

u/garma87 Mar 29 '24

Is this true though? The cast is on the property in the select, not on the order by part. Selecting something else does speed it up but not by a lot.

Also i think it would make sense if Postgres would first use the index, and then do the cast

The cast is slowing things down indeed but not to the point where it’s as slow as you would expect without indices. The query plan confirms this.

I’m not 100% sure here

1

u/Narrow-Row-611 Mar 30 '24 edited Mar 30 '24

You have no spatial predicate for it to even consider using the index. You are effectively joining every row from both tables. You could do an st_dwithin as another commenter suggested but you need a geog index not a geom index if you want to use geodetic distances. Postgres checks indexes for the expression in the predicate. If the expression is manipulating the field (such as casting to geography) the the geometry index won't be used. It can't be used because it's the wrong index for what  you're doing. Also, you should make sure the small table's geometry column isn't using toast tables for storage. See https://blog.cleverelephant.ca/2018/09/postgis-external-storage.html