Запустите запрос с LIMIT/OFFSET, а также получите общее количество строк

Question

Для целей нумерации страниц мне нужно выполнить запрос с LIMIT а также OFFSET статьи. Но мне также нужно подсчитать количество строк, которые будут возвращены этим запросом без LIMIT а также OFFSET статьи.

Я хочу запустить:

SELECT * FROM table WHERE /* whatever */ ORDER BY col1 LIMIT ? OFFSET ?

А также:

SELECT COUNT(*) FROM table WHERE /* whatever */

В то же время. Есть ли способ сделать это, в частности, способ, позволяющий Postgres оптимизировать его, чтобы он работал быстрее, чем запуск обоих?

Answer 1

Да. С простой оконной функцией:

SELECT *, count(*) OVER() AS full_count
FROM   tbl
WHERE  /* whatever */
ORDER  BY col1
LIMIT  ?
OFFSET ?

Имейте в виду, что стоимость будет значительно выше, чем без общего количества, но все же дешевле, чем два отдельных запроса. Postgres должен фактически подсчитать все строки в любом случае, что требует затрат в зависимости от общего количества подходящих строк. Подробности:

• Лучший способ получить счетчик результатов до применения LIMIT

Однако, как указал Дани, когда OFFSET равно как минимум числу строк, возвращаемых из базового запроса, строки не возвращаются. Таким образом, мы также не получаем full_count,

Если это не приемлемо, возможный обходной путь, который всегда возвращает полный счет, будет с CTE и OUTER JOIN:

WITH cte AS (
   SELECT *
   FROM   tbl
   WHERE  /* whatever */
   )
SELECT *
FROM  (
   TABLE  cte
   ORDER  BY col1
   LIMIT  ?
   OFFSET ?
   ) sub
RIGHT  JOIN (SELECT count(*) FROM cte) c(full_count) ON true;

Вы получаете ряд значений NULL с full_count добавлено, если OFFSET слишком большой Или он добавляется к каждой строке, как в первом запросе.

Если строка со всеми значениями NULL является возможным допустимым результатом, вы должны проверить offset >= full_count чтобы устранить неоднозначность происхождения пустой строки.

Это все еще выполняет базовый запрос только один раз. Но это добавляет дополнительную нагрузку к запросу и платит только в том случае, если это меньше, чем повторение базового запроса для подсчета.

Если доступны индексы, поддерживающие окончательный порядок сортировки, возможно, стоит включить ORDER BY в CTE (избыточно).

Answer 2

Хотя ответ user939860 работает как шарм, он возвращает общее количество строк в каждой строке , как показано ниже:

      col1 - col2 - col3 - total
--------------------------
aaaa - aaaa - aaaa - count
bbbb - bbbb - bbbb - count
cccc - cccc - cccc - count

Вы можете рассмотреть возможность использования подхода, который возвращает общее количество только один раз , например:

      total - rows
------------
count - [{col1: 'aaaa'},{col2: 'aaaa'},{col3: 'aaaa'}
         {col1: 'bbbb'},{col2: 'bbbb'},{col3: 'bbbb'}
         {col1: 'cccc'},{col2: 'cccc'},{col3: 'cccc'}]

SQL-запрос:

      SELECT 
    (SELECT COUNT(*) FROM table) as count, 
    (SELECT json_agg(t.*) FROM (
        SELECT * FROM table
        WHERE /* whatever */
        ORDER BY col1
        OFFSET ?
        LIMIT ?
    ) AS t) AS rows 

Answer 3

Edit : этот ответ действителен при получении неотфильтрованной таблицы. Я позволю, если это может кому-то помочь, но может не совсем ответить на первоначальный вопрос.

Ответ Эрвина Брандштеттера идеален, если вам нужно точное значение. Однако для больших таблиц часто требуется только довольно хорошее приближение. Postgres дает вам именно это, и он будет намного быстрее, поскольку ему не нужно будет оценивать каждую строку:

SELECT *
FROM (
    SELECT *
    FROM tbl
    WHERE /* something */
    ORDER BY /* something */
    OFFSET ?
    LIMIT ?
    ) data
RIGHT JOIN (SELECT reltuples FROM pg_class WHERE relname = 'tbl') pg_count(total_count) ON true;

На самом деле я совершенно не уверен, есть ли преимущество экстернализации RIGHT JOINили как в стандартном запросе. Это заслуживает некоторого тестирования.

SELECT t.*, pgc.reltuples AS total_count
FROM tbl as t
RIGHT JOIN pg_class pgc ON pgc.relname = 'tbl'
WHERE /* something */
ORDER BY /* something */
OFFSET ?
LIMIT ?

Answer 4

Плохая практика - вызывать два раза один и тот же запрос для Просто, чтобы получить общее количество строк результата восстановления. Это займет время выполнения и приведет к потере ресурсов сервера.

Лучше вы можете использовать SQL_CALC_FOUND_ROWS в запросе, который сообщит MySQL получить общее количество строк вместе с результатами запроса ограничения.

Пример установлен как:

SELECT SQL_CALC_FOUND_ROWS employeeName, phoneNumber FROM employee WHERE employeeName LIKE 'a%' LIMIT 10;

SELECT FOUND_ROWS();

В приведенном выше запросе просто добавьте SQL_CALC_FOUND_ROWS вариант в остальном требуемом запросе и выполнить вторую строку, т.е. SELECT FOUND_ROWS() возвращает количество строк в наборе результатов, возвращенном этим оператором.

Answer 5

Нет.

Возможно, есть небольшой выигрыш, который вы теоретически могли бы получить, управляя ими индивидуально с достаточно сложными механизмами под капотом. Но, если вы хотите узнать, сколько строк соответствует условию, вам нужно их посчитать, а не просто ОГРАНИЧЕННОЕ подмножество.