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?
explain
on your queries to see what the optimiser is doing, at some point it may switch to sequential scans etcgeom && ST_MakeEnvelope