Psycopg2: pagination of large requests?

Question

PostgreSQL

Let there be a flask web application.
Base - PostgreSQL through psycopg2
And now a selection (SELECT) is made from a table or view.
The sample result can be unintentionally very large (some [hundreds] of millions of records).
The task is to find out the number of potentially received rows before actually fetching them .
Well and - if it is admissible - to organize pagination.
Like this:

@bp.route('/megaquery', methods=['GET'])
def megaquery():
    global conn, cur
    page = request.args.get('page', 1, type=int)
    if cur is None:
        cur = conn.cursor(name='coolquery', scrollable=True)
        cur.execute('SELECT * FROM megaview ORDER BY datime ASC')
    else:
        cur.scroll((page-1) * PAGE_SIZE, 'absolute')
    # вот в этом месте надо знать скока (cur.rowcount == -1)
    return render_template('megaquery.html', data=cur.fetchmany(PAGE_SIZE), paging=(page, cur.rowcount // PAGE_SIZE))

Answer 1

The task is to find out the number of potentially received rows before actually fetching them.

onlySELECT count(*) ...

Well and - if it is admissible - to organize pagination.

And here it is necessary to look at the request and the plan. In simple cases (like SELECT * FROM table) fetching the first pages is very fast (especially through LIMIT). But it's easy to break, for example, by sorting

Answer 2

Well, often the total number of pages can be ignored, and instead of OFFSET, use where ID>last_id_on the previous page, etc.

Answer 3

https://wiki.postgresql.org/wiki/Count_estimate
As advised in the comments - you can take it from the EXPLAIN query

CREATE FUNCTION count_estimate(query text)
  RETURNS integer
  LANGUAGE plpgsql AS
$func$
DECLARE
    rec   record;
    rows  integer;
BEGIN
    FOR rec IN EXECUTE 'EXPLAIN ' || query LOOP
        rows := substring(rec."QUERY PLAN" FROM ' rows=(+)');
        EXIT WHEN rows IS NOT NULL;
    END LOOP;

    RETURN rows;
END
$func$;