Подсчет количества найденных записей в PostgreSQL

Помоему первый и второй у вас идентичны...

Не уловил разницы в коде вариантов 1 и 2 - опечатка?

>>из MySQL мне было известно, что поиск с count()-запросом работает почти в 2 раза медленнее, чем с SQL_CALC_FOUND_ROWS

далеко не всегда

http://www.mysqlperformanceblog.com/2007…

А я вот про MySQL новое узнал отсюда :-). PostgreSQL еще не использовал

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

Не поверите, ровно 10 минут назад как раз искал эту информацию. Спасибо.

Открыл для себя новый параметр в MySQL - SQL_CALC_FOUND_ROWS. Если честно, никогда его не встречал. Возьму на вооружение!

кажется, в Варианте 3 описка:
select id from testing where text like '%adf%' limit 20 offset 180000;

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

я делаю так
select count(*) from ( .. запрос .. )

ну и далее лимитом уже, постранично когда показать надо.

Отличное сравнение, спасибо за него. Есть еще интересная возможность быстрого определения приблизительного количества рядов, когда не нужна идеальная точность (такое бывает в случае больших чисел): делать explain запросу при обновленной статистике (в свежих версиях PostgreSQL при включенном автовакууме статистика всегда актуальна) и смотреть на оценку рядов, которую делает планировщик запросов. Цена вопроса — миллисекунды.

1) так а чем по вашему отличается вариант 1 от варианта 2? вы реально полагаете, что это разные запросы?
я думаю, что анализатор приводит их к одному (а именно к первому варианту)...

2) по поводу ваших методов тестирования - выполенение одинаковых запросов в цикле не дает полной картины так как база кеширует такие запросы и он реально выполняется только первый раз.

лучше делать не count(*), а count(case when text like '%adf%' then 1 end) - попробуйте, скорее всего будет быстрее.

на самом деле на больших объемах данных при тяжелых запросах count нужно закешировать при первом обращении и потом просто брать из кеша. да, кол-во может измениться. в этом случае можно делать пересчет где-то "в фоне" через каждые N страниц и обновлять кеш.

Использовать SQL_CALC_FOUND_ROWS в MySQL следует только при LIKE или FullTextSearch или нету индексов на поисковых полях, в результате чего запрос работает долго. Если у вас выборка вида
SELECT fields
FROM table1
LEFT JOIN table2 ON table2.id = table2.reference
WHERE table1.reference = INT LIMIT 0, 20

из хотя бы десятков тысячь записей, то SELECT + SELECT COUNT(*) по условию будет сильно быстрее. SQL_CALC_FOUND_ROWS в данном случае заставляет сделать JOIN на все записи доступные в таблицах и только потом отрезает их. Без него это дело сильно оптимизируется и 2 запроса в итоге могут быть в несколько раз быстрее чем один с SQL_CALC_FOUND_ROWS (да, и на памяти вы тоже сильно экономите)

Небольшое замечание, почему в постгре нет SQL_CALC_FOUND_ROWS (что бы его не ругали). Постгря умеет использовать индексы для сортировки и выборок limit/offset, соотв. при выборке он может и не пройтись по всем полям, удовлетворяющим where.

На практике имеет смысл денормализовать по полям, которые используются в where.

В php лучше использовать функцию pg_affected_rows(), pg_cmdtuples() считается устаревшей.

это только у меня так, что после move на начало, move обратно в конец отработывает столько же времени сколько в первый раз? хотя внутри транзакции ничего не меняется

4й вариант, конечно прекрасен, но невозможен при использовании PDO т.к. нет альтернативы для pg_affected_rows/pg_cmdtuples и нет возможности вызвать их из-за отсутствия ресурса, который они требуют.
Также в сам postgres не нашел возможности узнать какую-либо информацию о позиции курсора.
Есть еще варианты?

Есть еще совершенно простой и безболезненный способ, выбирать одновременно вместе с данными:

SELECT stuff,
       count(*) OVER() AS total_count
FROM table
WHERE condition
ORDER BY stuff OFFSET 40 LIMIT 20