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

Видя, что вам нужно знать для разбиения на страницы, я предлагаю выполнить полный запрос один раз, записав данные на диск в качестве кеш-памяти на стороне сервера, а затем подав их через механизм разбиения на страницы.

Если вы выполняете запрос COUNT с целью решить, предоставлять ли данные пользователю или нет (т.е. если есть> X записей, возвращать ошибку), вам необходимо придерживаться подхода COUNT.

Как я описываю в моем блоге, MySQL имеет функцию под названием SQL_CALC_FOUND_ROWS. Это избавляет от необходимости выполнять запрос дважды, но по-прежнему необходимо выполнить запрос полностью, даже если предложение limit позволило бы остановить его раньше.

Насколько мне известно, для PostgreSQL подобной функции нет. Одна вещь, на которую следует обратить внимание при разбиении на страницы (наиболее распространенная вещь, для которой используется LIMIT, IMHO): выполнение «OFFSET 1000 LIMIT 10» означает, что БД должна получить по меньшей мере 1010 строк, даже если она дает вам только 10. A более эффективный способ сделать это - запомнить значение строки, которую вы заказываете для предыдущей строки (в данном случае 1000-й), и переписать запрос следующим образом: «... WHERE order_row> value_of_1000_th LIMIT 10». Преимущество в том, что "order_row", скорее всего, проиндексирован (если нет, у вас проблемы). Недостатком является то, что если новые элементы добавляются между просмотрами страниц, это может немного рассинхронизироваться (но опять же, это может не наблюдаться посетителями и может быть большим приростом производительности).

Вы можете уменьшить снижение производительности, не выполняя запрос COUNT () каждый раз. Кешируйте количество страниц, скажем, за 5 минут до повторного выполнения запроса. Если вы не видите огромное количество INSERT, это должно работать нормально.

Поскольку Postgres уже выполняет определенный объем кэширования, этот тип метода не так неэффективен, как кажется. Это точно не удвоение времени выполнения. У нас есть таймеры, встроенные в наш уровень БД, поэтому я видел доказательства.

Чистый SQL

С 2008 года все изменилось. Вы можете использовать оконная функция, чтобы получить полный счет и ограниченный результат в одном запросе. Введено с PostgreSQL 8.4 в 2009 году.

SELECT foo
     , count(*) OVER() AS full_count
FROM   bar
WHERE  <some condition>
ORDER  BY <some col>
LIMIT  <pagesize>
OFFSET <offset>;

Обратите внимание, что этот может быть значительно дороже, чем без общего подсчета. Необходимо подсчитать все строки, и возможное сокращение только верхних строк из соответствующего индекса может оказаться бесполезным. Не имеет большого значения с маленькими столиками или full_count <= OFFSET + LIMIT. Имеет значение для существенно большего full_count.

Угловой корпус: когда OFFSET по крайней мере равно количеству строк из базового запроса, возвращается нет строки. Таким образом, вы также не получаете full_count. Возможная альтернатива:

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

Последовательность событий в запросе SELECT

(0. CTE оцениваются и материализуются отдельно. В Postgres 12 или более поздних версиях планировщик может встроить такие, как подзапросы, перед тем, как приступить к работе.) Не здесь.

1. Предложение WHERE (и условия JOIN, хотя в вашем примере их нет) фильтруют подходящие строки из базовой таблицы (таблиц). Остальное основано на отфильтрованном подмножестве.

(2. GROUP BY и агрегатные функции будут здесь.) Не здесь.

(3. Другие выражения списка SELECT оцениваются на основе сгруппированных / агрегированных столбцов.) Не здесь.

4. Оконные функции применяются в зависимости от предложения OVER и спецификации кадра функции. Простой count(*) OVER() основан на всех подходящих строках.

5. ORDER BY

(6. DISTINCT или DISTINCT ON подойдут сюда.) Не здесь.

7. LIMIT / OFFSET применяются на основе установленного порядка выбора строк для возврата.

LIMIT / OFFSET становится все более неэффективным с ростом числа строк в таблице. Рассмотрите альтернативные подходы, если вам нужна более высокая производительность:

• Оптимизировать запрос с помощью OFFSET на большой таблице

Альтернативы для окончательного подсчета

Существуют совершенно разные подходы к подсчету затронутых строк (нет - полный счет до применения OFFSET и LIMIT). В Postgres есть внутренняя бухгалтерия, на сколько строк повлияла последняя команда SQL. Некоторые клиенты могут получить доступ к этой информации или сами подсчитать строки (например, psql).

Например, вы можете получить количество затронутых строк в plpgsql сразу после выполнения команды SQL с помощью:

GET DIAGNOSTICS integer_var = ROW_COUNT;

Подробности в инструкции.

Или вы можете использовать pg_num_rows в PHP. Или аналогичные функции в других клиентах.

Связанный:

• Вычислить количество строк, затронутых пакетным запросом в PostgreSQL