how to make select query faster on Postgis

Question

this query selects geometry _id by within the circle

 select tbl2.geometry_id from geometryTable tbl1
        join geometryTable tbl2 on  ST_Intersects(ST_Buffer(ST_centroid(tbl1.geometry)::geography, 1000)::geometry, tbl2.geometry)
    where tbl1.geometry_id = '112233'

this query works fine but it has taken too many times, more than 20 seconds. I need to make this query faster.

Answer 1

To get the tbl2 geometries that are within 1000 meters of tbl1 geometries centroid, you can omit the buffer creation and rely on the distance directly by using ST_DWithin

select tbl2.geometry_id 
from geometryTable tbl1
   join geometryTable tbl2 
    on ST_DWithin(ST_centroid(tbl1.geometry)::geography, tbl2.geometry::geography,1000, true)
where tbl1.geometry_id = '112233';

If this is not enough, you can create a spatial index on the centroid geography

CREATE tl1_centroid_geog ON tbl1 USING GIST(geography(ST_centroid(geometry)));

and eventually on tbl2 geography as well

CREATE tl2_geog ON tbl2 USING GIST(geography(geometry));