PostGIS & Pagination

Question

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?

Answer 1

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.