1

I have a pretty standard table with one geometry column and a datetime column. Geom is indexed using gist and table is clustered + VACUUM ANALYZE. The Docker image is kartoza/postgis:12.0.

I consider the following query (bbox intersection):

SELECT product_id 
FROM products
WHERE 
    ST_Intersects(
        geom, 
        ST_MakeEnvelope(%s, %s, %s, %s, 4326)
    )
LIMIT 1000

I have noticed the following behavior: query runs < 10ms when the bbox is below 1 deg² (returning 1000 rows), but the performance degrades as the AOI surface increases (for example, 250ms if the bbox is 20 deg x 20 deg).

I would have expected the opposite as PostGIS is more likely to find quickly 1000 products intersecting a bbox as the bbox is larger.

So I guess the LIMIT clause is not quite effective in my query.

How can I correct this behavior, and implement a robust & fast pagination?

4
  • 1
    You need to use explain on your queries to see what the optimiser is doing, at some point it may switch to sequential scans etc
    – Ian Turton
    Commented Sep 18, 2020 at 11:15
  • 2
    Also, if that is a rectangular bbox, use geom && ST_MakeEnvelope
    – geozelot
    Commented Sep 18, 2020 at 11:19
  • Indeed, explain analyze helped me solve the issue. Thanks! Commented Sep 23, 2020 at 12:52
  • And what was the issue?
    – dr_jts
    Commented Sep 23, 2020 at 16:26

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.