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

[TermiT]

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

[TermiT]

Первый и второй вариант

[feedbee]

Да, пардон, промахнулся. Уже поправил. Спасибо.

[PQR]

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

[Tr1aL]

разница есть, смотри внимательней :)

[symbix]

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

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

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

[razawa]

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

[romychs]

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

[Frank]

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

[LDEV]

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

[3stark]

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

[feedbee]

В данном случае это не существенно, но таки да: там должен быть id.

[AlienZzzz]

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

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

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

[feedbee]

Я специально сделал поиск через LIKE, чтобы получить "тяжелый" запрос. Понятно, что по тексту лучше искать full text search'ем. А тяжелые запросы не получаются не только при поиске по тексту. На счет варианта "select count(*) from ( .. запрос .. ) ну и далее лимитом уже, постранично когда показать надо. " как раз написано в этой статье, почитайле пожалуйста. Там еще указана разница во времени такого запроса и варианта с курсором — ваш вариант на 75% медленнее при выборках далеких страниц.

[AlienZzzz]

у меня немного сложно реализовать через курсор, так как сам ".. запрос .." у меня еще парсится в зависимости от фильтров (у мен каждое поле можно отфильтровать и т.д.), поэтому запрос может быть разным.

select count(*) from ( .. запрос .. )

___

на 75 проц. медленнее если лайк , но не фул текстом )

[feedbee]

Не важно, лайк или полнотекстный поиск. В 2 запроса при использовании сортировки или выборе далекой страницы будет обрабатываться примерно в 1,75 раза дольше. Через лайк речь будет о 0,6 с против 0,4 с; при фултексте речь будет например о 0,2 против 0,12

[AlienZzzz]

в любом случае респект. я попробую подкрутить это к себе (ессли смогу задавать запрос, как строчку 'DECLARE curs CURSOR FOR 'se ....'; )

[feedbee]

Спасибо :) Было бы интересно сравнение скоростей в вашем конкретном случае. Толкьо не забудьте, что сравнивать надо на далеких страницах от начала. Чем ближе страница к началу, тем меньше времени работает второй запрос, если нет сортировки.

[AlienZzzz]

сортировка есть .

[zolotukhin]

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

[greeka]

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

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

[AlienZzzz]

не выполняеться а анализируется 1 раз, собствено на анализ то время большое тратится.

[greeka]

и выполняется тоже.
так как у автора в тестах искомая строка не меняется!

[galaxy]

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

[greeka]

сейчас запустил тест немного его изменив - засекаю время между выполнениями запросов, так вот, первый выполняется долго (20 секунд), последующие - намного быстрее (~0,01 сек).
хотя, я тут действительно мог ошибиться с транзакциями. но все равно результаты налицо.
хотя у меня сервак монстр, оптимизирован, и пока нет изменений в табл. просто выборка. да и сама выборка всего 20 записей - их закешировать много ресурсов не нужно

[shuffle]

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

[greeka]

одинаково на 10 лимонах записей, перебор что там, что там по всей таблице

[yar]

еще проще select count(1) from test where text like '%adf%', без всяких case :)

[greeka]

ну я count никогда по звездочке не делаю - по первичному ключу ли как вы написали...

[greeka]

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

[feedbee]

Это если всегда одинаковый запрос. Тогда так и надо делать. А если запросы постоянно разные (фильтры, поиски текста), то не перекешируешь все.

[greeka]

ну это я для пейджера написал вариант, а так понятно что нужно каждый раз пересчитывать

[yar]

делать пейджинг при больших объемах в любом случае бессмысленно
ну какой смысл листать миллион записей?

[greeka]

на миллион записей нет смысла, но смысл в том, сколько записей в таблице. если из миллиона выберется 100 записей, то листать нужно, но от этого count меньше весить не станет

[Psih]

Использовать 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 (да, и на памяти вы тоже сильно экономите)

[feedbee]

Проверял на практике: в запросе нет ни FT, ни LIKE. Запросы с count() работали дольше. А вот в случае с JOIN ваша правда (во всяком случае по логике).

[Q2W]

Да, вариант с count() чаще всего лучше, ибо:
1. Не нужна сортировка (а это очень тяжёлая операция).
2. Часто можно выключить join'ы из запроса.
3. Можно выключить всякие расчёты и проч. из запроса.

В результате запрос с count() становится гораздо легче и быстрее.
А вот в варианте с SQL_CALC_FOUND_ROWS базе приходится применять всё вышеописанное ко всем строкам таблицы, а не только к выбираемым. И хоть и есть экономия на выводе только того, что нужно, в результате получается медленнее.

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

[fisher]

>>базе приходится применять всё вышеописанное ко всем строкам таблицы
а это извините из общих физических соображений-то вообще зачем? где такое написано? и не путаете ли Вы с count у innodb vs isam.

[johnny_bee]

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

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

[munkie]

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

[xiWera]

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

[Sway]

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

[vajadhava]

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

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