Замена SQL_CALC_FOUND_ROWS или подсчет количества записей в PostgreSQL

На работе в новом проекте используется СУБД . Так как до сих пор я работал с MySQL, сейчас приходится изучать и открывать для себя постгри. Первая проблема, которая меня заинтересовала — замена мускулевского SQL_CALC_FOUND_ROWS. Сходу готового решения найти не удалось. На форумах постоянно констатировали, что _CALC_FOUND_ROWS в постгри нет. Некоторые писали, что надо юзать count(*). Но еще из MySQL мне известно, что поиск с count()-запросом работает почти в 2 раза медленнее, чем с _CALC_FOUND_ROWS. Я консультировался у тех, кто пользуется PostgreSQL, день мучал google и в результате получил 4 варианта замены _CALC_FOUND_ROWS в PostgreSQL, один из которых вполне приемлимый по скорости. Итак, сразу представлю те четыре варианта, о которых пойдет речь. Наш целевой запрос ищет в таблице записи, в которых встречается текст adf в поле `text`. Выбираем id 20 записей начиная от 180.000 по порядку и количество найденных всего. Вариант 1. Взят из phpPgAdmin. Я просто заглянул в код этого клиента для PostgreSQL и посмотрел как подсчет сделан у них при просмотре данных таблицы. Используется 2 запроса с подзапросами. Удобство в том, что не надо парсить и менять исходный запрос, чтобы подсчитать количество записей, найденных им.

select count(id) from (select id from testing where text like '%adf%') as sub;
select * from (select id from testing where text like '%adf%') as sub limit 20 offset 180000

Вариант 2. Самый простой вариант, который обычно юзают новички как в MySQL, так и в Postgres и других СУБД. 2 запроса.

select count(id) from testing where text like '%adf%';
select id from testing where text like '%adf%' limit 20 offset 180000

Вариант 3. © max_posedon. Это попытка эмуляции мускулевского SQL_CALC_FOUND_ROWS в Postgres по логике. Правда работает только при сортировке по id (в данном случае). Здесь подставляется id последней записи в выборке, т.е. записи под номером 180.000 + 20.

select * from testing where text like '%adf%' limit 20 offset 180000;
select count(id) from testing where text like '%adf%' and id > 132629;

Вариант 4. По советам пользователей irc.freenode.org, опять же max_posedon‘а, и этого ответа на форуме PostgreSQL, который прятался глубоко в гугле. Используется курсор.

DECLARE curs CURSOR FOR select id from testing where text like '%adf%';
MOVE FORWARD 180000 IN curs;
FETCH 20 FROM curs;
MOVE FORWARD ALL IN curs;

+ фунция PQcmdTuples() API Postgres (или $count = pg_cmdtuples($result); в PHP). Обратите внимание, что все 4 варианта запросов следует выполнять в одной транзакции, тогда они работают быстрее. 4й вариант вовсе не будет работать, если не использовать одну транзакцию: теряется курсор. Теперь о скоростях. Я провел тестирование скорости работы этих четырех вариантов. Вобщем-то тесты подтвердили ожидания. Но отмечу важный факт. Все запросы запускались на конфигурации PostgreSQL по умолчанию, которая не является оптимизированной на производительность. У меня под рукой просто не было оптимизированного сервера. Так что цифры могут немного корректироваться при запуске с «хорошим» конфигом. Однако суть не изменится. Тестовые запуски проводились в PHP по 20 повторов 2 раза на каждый вариант. Доступен php-скрипт, который запускал тесты. Кому интересно, есть полная статистика выборок в Excel™. Здесь опубликую лишь сводную таблицу:

Вар 1 Вар 2 Вар 3 Вар 4
Ср. время (мс) 647,41 648,25 450,64 370,67
Отношение к вар 4 1,75 1,75 1,22

Для сравнения время запросов без использования транзакции:

  • Вар 1: 1204 мс,
  • Вар 2: 689 мс,
  • Вар 3: 560 мс,
  • Вар 4 работает только в пределах транзакции.

Итоги. Самый быстрый вариант 4 с использованием курсора. Его скорость обусловлена тем, что «тяжелый» поисковый запрос выполняется только один раз. Далее проводятся операции с курсором. Аналогично работает и SQL_CALC_FOUND_ROWS в MySQL. На 20% от него отстает вариант 3 — попытка эмуляции SQL_CALC_FOUND_ROWS в PostgreSQL. Варианты 1 и 2 работат примерно с одинаковой скоростью и на 75% (более чем на 2/3!) уступают по скорости запросу с курсором.

Об авторе Валера Леонтьев

Программист PHP/MySQL.
Запись опубликована в рубрике IT, Web, Все рубрики с метками , . Добавьте в закладки постоянную ссылку.

2 комментария на «Замена SQL_CALC_FOUND_ROWS или подсчет количества записей в PostgreSQL»

  1. Артём Курапов говорит:

    В мемориз :) Теперь надо для DB2, Oracle и MSSql сравнение count’ов сделать

  2. patt говорит:

    Для этого когда то использовал RETURNING, как именно уже не помню как раз ищу. Синтаксис был примерно такой: SELECT …. WHERE… RETURNING ….

Добавить комментарий