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
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?