Psycopg2: pagination больших запросов?

Question

[Eugene TI]

Пусть есть веб-приложение на flask.
База - PostgreSQL через psycopg2
И вот делается выборка (SELECT) из таблицы или view.
Результат выборки может быть нечаянно очень большим (сколько-то [сотен] миллионов записей).
Задача - узнать кол-во потенциально полученных строк до их фактической выборки.
Ну и - если допустимо - организовать pagination.
Типа такого:

@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))

Comments

[shurshur]

Из Довольно Корявых Идей можно попробовать в explain посмотреть количество потенциальных результатов:

messageStore=> explain select * from messages;
                         QUERY PLAN                          
-------------------------------------------------------------
 Seq Scan on messages  (cost=0.00..55.67 rows=67 width=2837)
(1 row)

messageStore=> select count(*) from messages;
 count 
-------
    70
(1 row)

Answer 1

[galaxy]

Задача - узнать кол-во потенциально полученных строк до их фактической выборки.

только SELECT count(*) ...

Ну и - если допустимо - организовать pagination.

А вот тут надо запрос и план смотреть. В простых случаях (типа SELECT * FROM table) выборка первых страниц происходит очень быстро (особенно через LIMIT). Но это легко сломать, например, сортировкой

Answer 2

[ScriptKiddo]

https://wiki.postgresql.org/wiki/Count_estimate

Как посоветовали в комментах - можно брать из EXPLAIN запроса

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=([[:digit:]]+)');
        EXIT WHEN rows IS NOT NULL;
    END LOOP;

    RETURN rows;
END
$func$;

Comments

[Eugene TI]

То есть запрос надо делать 2 раза?
Один раз для оценки, второй раз для получения результатов?

[ScriptKiddo]

Eugene TI, EXPLAIN - не выполняет запрос. EXPLAIN ANALYZE - выполняет.
В примере кода используется простой EXPLAIN

[shurshur]

ScriptKiddo, круто, не думал, что так кто-то реально делает... Кстати, там приведены и другие интересные рецепты.