Search code examples
sqlpostgresqlpaginationrow-numberpostgresql-9.4

Global row numbers in chunked query

I would like to include a column row_number in my result set with the row number sequence, where 1 is the newest item, without gaps. This works:

SELECT id, row_number() over (ORDER BY id desc) AS row_number, title
FROM mytable
WHERE group_id = 10;

Now I would like to query for the same data in chunks of 1000 each to be easier on memory:

SELECT id, row_number() over (ORDER BY id desc) AS row_number, title
FROM mytable
WHERE group_id = 10 AND id >= 0 AND id < 1000
ORDER BY id ASC;

Here the row_number restarts from 1 for every chunk, but I would like it to be as if it were part of the global query, as in the first case. Is there an easy way to accomplish this?

Solution

  • Assuming:

    • id is defined as PRIMARY KEY - which means UNIQUE and NOT NULL. Else you may have to deal with NULL values and / or duplicates (ties).

    • You have no concurrent write access on the table - or you don't care what happens after you have taken your snapshot.

    A MATERIALIZED VIEW, like you demonstrate in your answer, is a good choice.

    CREATE MATERIALIZED VIEW mv_temp AS
SELECT row_number() OVER (ORDER BY id DESC) AS rn, id, title
FROM   mytable
WHERE  group_id = 10;

    But index and subsequent queries must be on the row number rn to get

    data in chunks of 1000

    CREATE INDEX ON mv_temp (rn);

SELECT * FROM mv_temp WHERE rn BETWEEN 1000 AND 2000;

    Your implementation would require a guaranteed gap-less id column - which would void the need for an added row number to begin with ...

    When done:

    DROP MATERIALIZED VIEW mv_temp;

    The index dies with the table (materialized view in this case) automatically.

    Related, with more details: