1

Here's my query

SELECT "building"."id", 
"building"."details", 
"building"."status", 
ST_AsGeoJSON("building"."center")::json, 
ST_AsGeoJSON("building"."geometry")::json 
FROM "building" WHERE ST_Intersects(
    "building"."center",
    ST_MakeEnvelope(-157.91061401367188,21.197376124303936,-157.72951126098633,21.340868251190965,4326):: geography('POLYGON')
)

There are around 500,000 records in my database. These buildings' data are from OSM. I am trying to get the buildings that are inside a bounding box. It takes ~ 50sec to complete the query. My data will grow over time. It might take more time if data grows.

I have indexed my center column

CREATE INDEX "IDX_32ed92783d0144c88d8094b0ac" ON "building" USING GIST ( center );

My EXPLAIN and ANALYZE:

"Index Scan using ""IDX_32ed92783d0144c88d8094b0ac"" on building  (cost=0.28..35.80 rows=1 width=175) (actual time=6.473..146.382 rows=9792 loops=1)"
"  Index Cond: (center && '0103000020E61000000100000005000000000000C023BD63C08AE5DE3D87323540000000C023BD63C0231C4724435735400000002858B763C0231C4724435735400000002858B763C08AE5DE3D87323540000000C023BD63C08AE5DE3D87323540'::geography(Polygon,4326))"
"  Filter: st_intersects(center, '0103000020E61000000100000005000000000000C023BD63C08AE5DE3D87323540000000C023BD63C0231C4724435735400000002858B763C0231C4724435735400000002858B763C08AE5DE3D87323540000000C023BD63C08AE5DE3D87323540'::geography(Polygon,4326))"
"  Rows Removed by Filter: 105"
"  Buffers: shared hit=1313"
"Planning Time: 21.729 ms"
"Execution Time: 147.195 ms"

How can I improve my query performance and reduce response time?

Improve this question
9
  • 3
    Please use text to present the EXPLAIN plan. Images are not legible on all devices.
    – Vince
    Dec 8, 2022 at 12:33
  • I updated it in a readable form.
    – NinjaMAN
    Dec 8, 2022 at 12:42
  • Have you tried to store the Envelope geometry in other table in order to intersect 2 tables ? Take a look at the function ST_DWithin to optimize your intersect filter : postgis.net/docs/ST_DWithin.html
    – J. Monticolo
    Dec 8, 2022 at 13:41
  • 1
    the query plan shows it is taking 147ms, so less than 1 second... the 49 other seconds are spent somewhere else (establishing the connection, receiving the data, displaying it or whatever)
    – JGH
    Dec 8, 2022 at 13:51
  • 1
    Is your query returning about 500,000 rows, including generated GeoJSON? That's going to take a while, depending on your system and client. How fast is it if you do a "SELECT count(*) FROM ..."? Then you know where the problem is.
    – bugmenot123
    Dec 8, 2022 at 15:14

1 Answer 1

Reset to default
1

Using && would probably boost your performance. Take a look at https://dba.stackexchange.com/questions/191666/vs-st-intersects-performance

    SELECT * FROM "your_schema"."your_table" 
    WHERE "your_schema"."your_table".geometry &&
    ST_MakeEnvelope(min_lon, min_lat, max_lon, max_lat, your_epsg)
Improve this answer
3
  • 2
    Considering they only compare points ("center") against the search envelope, I am not convinced that && is going to help.
    – bugmenot123
    Dec 8, 2022 at 15:15
  • @bugmenot123 It's skipping function invocation, at least...and it's unnecessary. So better use the right tool.
    – geozelot
    Dec 8, 2022 at 17:36
  • It's not going to make a huge difference but worth doing. I updated my query as suggested but no significant change.
    – NinjaMAN
    Dec 9, 2022 at 2:12

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.