postgresqlpostgisspatial-query

How to optimize a spatial query?

I need to optimize this query, 'coz execution time is too long. I've built gist indexes on geom(point), but they are not used unfortunaly. 

    select *, cast(st_distance_sphere(city_gis.point, tmp1.pnt) as int) as distance
        from tmp1
    inner join  city_gis
     on cast(st_distance_sphere(city_gis.point,tmp1.pnt) as int) between 0 and 60000

Here my query and execution plan

Solution

  • ST_DISTANCE functions do not use a geometry index. You should use e.g. ST_DWITHIN so your query would be something like

    select *, cast(st_distance_sphere(city_gis.point, tmp1.pnt) as int) as distance
from tmp1
inner join city_gis on st_dwithin(city_gis.point,tmp1.pnt,60000)

    Here is the documentation of this function.

    https://postgis.net/docs/ST_DWithin.html