How can I query postgres by page?-CodePudding

Assume a table that has many pages and I only want some of it, for example

SELECT * FROM t WHERE...

From pg_class.relpages I know how many pages are there, how can I access some page like

SELECT * FROM t WHERE PAGE_NUMBER=1

CodePudding user response：

For that, you need a sort order. For example

SELECT * FROM t WHERE ...
ORDER BY customer, created_ts;

Make sure that the sort condition is unique. If it isn't, add the primary key at the end. Then you create an index for the ORDER BY condition:

CREATE INDEX ON t (customer, created_ts);

Then you select the first page of 50 items like this:

SELECT * FROM t WHERE ...
ORDER BY customer, created_ts
LIMIT 50;

You display the page and remember customer and created_ts from the last result row in <latest_cust> and <latest_ts>.

The next page is fetched with

SELECT * FROM t WHERE ...
AND (customer, created_ts) > (<latest_cust>, <latest_ts>)
ORDER BY customer, created_ts
LIMIT 50;

and so on.

This method (it is called “keyset pagination”) is efficient and quite stable in the face of concurrent data modifications.

CodePudding user response：

Since you mention relpages I assume you are talking about "physical" pages where the data is stored on disk. In general you should mind your own business and let the database server mind its own business. But if you don't want to do that, the you can reference tuples by the hidden system column "ctid", which is a composite consisting of the page number and the slot within the page.

select * from pgbench_accounts where ctid between '(18,0)' and '(18,65535)';

This is the 19th page, as page numbers start at 0. Slot numbers start at 1, but for convenience you are allowed to reference slot 0 as if it were a valid (but empty) slot.

Note that until very recent versions, this will not be fast as it would scan the whole table and filter out the disqualified rows one by one.