I have a pretty standard table with a geom column and a gist index on it. I would like to have a fast pagination on this table as it contains millions of rows.
The query involves a ST_Intersects:
SELECT id, geom FROM products WHERE ST_Intersects( geom, ST_MakeEnvelope(%s, %s, %s, %s, 4326) ) However, there is no ordering with ST_Intersects, and the OFFSET clause adds a too large penalty.
Is there a way to achieve a fast and robust pagination with spatial geometries?
The spatial index is of less concern here; you want to make sure you have an index on your id column!
Of course you can order the return set of your query consistently by id (or any other way), i.e.
SELECT id, geom FROM products WHERE geom && ST_MakeEnvelope(%s, %s, %s, %s, 4326) ORDER BY id ; and use LIMIT/OFFSET as you intended. Inconsistencies can only arise if that table gets rows inserted or updated at previous locations in your key column sequence during pagination requests.
However:
As you noted yourself, this can be a performance bottleneck. The issue here is that you have to rerun the costly computation each time you request the next batch, not so much the LIMIT/OFFSET penalty for large tables (for a simple SELECT on few million rows, you probably won't notice the increasing query time for increasing bounds that much, especially with clustered data and proper indexation). This rules out keyset pagination, too, as the concept here is to utilize the index by a filter clause; the main query still has to be run every time you request a batch.
If you can control the transaction flow, a cursor would be the way to go, e.g.
BEGIN; DECLARE batch CURSOR FOR SELECT id, geom FROM products WHERE geom && ST_MakeEnvelope(%s, %s, %s, %s, 4326) ORDER BY id ; FETCH 100 FROM batch; -- fetches first 100 entries and moves the cursor to the 101st position in the result set MOVE RELATIVE + 100 FROM batch; -- moves the cursor to the 201st position in the result set FETCH 100 FROM batch; -- fetches 100 entries from position 201 to 300 and moves the cursor to the 301st position in the result set CLOSE batch; COMMIT; This will return data from any position in consistent time over the result set.
But note that a cursor only lives within a single transaction; your client application needs AUTO-COMMIT turned off, and you will need to manage them yourself.
MATERIALIZED for PG12 and above) CTE and then selecting from it via LIMIT/OFFSET as usual can solve this. CREATE OR REPLACE MATERIALIZED VIEW AS (<your_query_with_a_row_number_over_column>); and add an index on it. You can refresh it whenever there is new data, and select from it using LIMIT/OFFSET with likely no noticeable overhead. You save the recomputation, and it is transaction agnostic.
select id ... where id > lastMaxID order by id limit 100) You would have to run the spatial query for each page anyways.idbut the spatial index does not kick in, hence the query is more than 10x slower.id+0or to add anoffset 0to prevent the default index from being used