Postgres, пагинация и длинные запросы

И два запроса внезапно станут работать быстрее в одном?

Не показывай общее количество страниц. Запрашивай LIMIT X+1, если в результате записей больше чем X, то показывай X записей и кнопку «Next page»

И два запроса внезапно станут работать быстрее в одном?

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

Не показывай общее количество страниц. Запрашивай LIMIT X+1, если в результате записей больше чем X, то показывай X записей и кнопку «Next page»

Блин, гениально. Спасибо

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

Offset это плохой вариант. База будет вычитывать все записи до offset. Для быстрой пагинации надо запоминать id последней записи на странице и потом запрашивать от этого id (это для упорядоченного по id запроса, для других полей так же). Число страниц точно и быстро вычислить нельзя, count(*) это просмотр всего ответа, быстро можно только приблизительную оценку у оптимизатора узнать. В принципе оно обычно точно никому не нужно.

Медленный SELECT COUNT(*) в постгрисе это же известная фича?

https://wiki.postgresql.org/wiki/Slow_Counting

В некоторых случаях он вынужден перебирать всю таблицу

Можно или выбирать приблизительное количество записей (в статье есть ссылка на пример) или держать отдельную таблицу с количеством записей. И обновлять ее триггерами, например

Спасибо.

Вообще, я кажется жутко туплю и нужно просто воткнуть эластик, а не пытаться геморроиться с постгресом.

Увы, у меня поиск не отсортирован по уникальным полям.

Сколько всего записей? Если мало - смотри индексы и кривизну запросов (не должно оно так тормозить). Если много - запили хранилку чтобы обновлять общее число элементов на инсерт/делит. Кешируй результаты запросов, запоминай какие элементы на какой запрос выбирались.

6млн записей, хочу по разным полям произвольно искать. Т.е. не по всем сразу, а по параметрам. Запросы практически все уникальные, за исключением просмотров разных страниц.

Плюс, базу очень штырит от постоянных записей (но хоть как-то это победил при помощи COPY TO)

Я тут выше уже отписался — кажется, самое разумное решение в моем случае — эластик. Странно, что сразу об этом не подумал

Запросы практически все уникальные

Есть статистика или умозрительная оценка?

Я — единственный пользователь этого дерьма пока :) Так что и статистика, и приблизительная оценка

Делал кеширование результатов на уровне nginx, особо не помогло

Я — единственный пользователь этого дерьма пока :) Так что и статистика, и приблизительная оценка

Понял.

огромной и медленной базой

а сколько всего элементов в базе?

7 млн сейчас. Для говновиртуалки довольно тяжело. Простые запросы выполняются в пределах секунды, но с тяжелыми — беда

rum не пробовал ещё? https://github.com/postgrespro/rum

https://github.com/tonsky/rum

это? как оно должно мне помочь?

а как тебе эластик поможет?:) точно также и rum, часть запросов через него делать

LIMIT .. OFFSET - не «пагинация».

Это Очень важное уточнение имеет какое-то влияние на ответ на заданный вопрос?

единственное он на postgresql 9.6+

извини, я так и не понял, причем тут либа для генерации html...

у меня даже clojure/js нигде нет

6млн записей, хочу по разным полям произвольно искать. Т.е. не по всем сразу, а по параметрам. Запросы практически все уникальные, за исключением просмотров разных страниц.

Плюс, базу очень штырит от постоянных записей (но хоть как-то это победил при помощи COPY TO)

Я тут выше уже отписался — кажется, самое разумное решение в моем случае — эластик. Странно, что сразу об этом не подумал

может это я не так понял, эластик это Elasticsearch или другой какой?

Вот даже на хабре есть примеры как делать консистентную пагинацию https://habrahabr.ru/post/301044/.
А LIMIT .. OFFSET - неконсистенты

Он самый.

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

ну, и в чем тогда несостыковка? я тебе предложил полнотекстовый поиск по всем полям какие захочешь не через Elasticsearch, а через rum который и есть дополнение для postgresql полнотекстового поиска сравнимый по скорости c Elasticsearch. Про генерацию html не шло ведь речи.

Все, нашел: https://github.com/postgrespro/rum

До этого нагуглил какой-то другой rum

Я просто сам на него не нарадуюсь, сейчас как раз его использую «и в хвост и в гриву», поэтому и рекомендую, он на лету добавляет элемент в поисковый индекс и много чего ещё.

Спасибо, полезно.

Увы, в моем случае подходят только limit/offset и курсоры.

Разве что для частных случаев (на которых постгрес и так неплох) уместны другие способы из статьи

https://habrahabr.ru/post/301044/#comment_9615182

SELECT * FROM test_table JOIN (SELECT id FROM test_table ORDER BY id LIMIT 100000, 30) as b ON b.id = test_table.id

Хм, интересно! Сейчас попробую, даст ли мне это какой-нибудь профит

А о банальной денормализации для оптимизации поиска вы не думали? Зачем лишнюю сущность в виде Эластика присобачивать?

Чтобы денормализовать, сначала надо нормализовать. А то, судя по всему, у тс все плохо.

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

Самые тяжелые запросы — это регулярки по нормальному тексту, регулярки по html (но это я придумал, как заменить на триггер+доп. поле) + поиск по тегам.

Если речь идёт о специфичной индексации страниц, то для быстрого старта можно взять что-нибудь типа Python Soup и соответствующей структуры таблиц, если уж PostgreSQL нужен. Да и текст наверное не в общем виде нужен, - можно сразу на входе преобразовать.

Да, что-то вроде того.

Оригинальный текст мне нужен.

О нужде в стеммированном виде думаю. Боюсь, что нет — места на впс довольно мало, а все это уже довольно прилично место занимает. С другой стороны, все-таки хочется быстро статистики считать.

При задаче поиска мне регулярок хватает, при «добыче» записей я, конечно, использую lxml

Блин, а пакетов для дебиана нет?

Нет, там сборка, да и не долгая.

Помог.

Как все было:

Сначала я перенес все на RUM (в последствии — на GIN) и долго-долго дрочил на производительность запросов.

Потом плюнул, выпилил полностью постгрес и стал использовать эластик в качестве БД.

В целом простые запросы стали выполняться медленней, но все равно за секунды. Принципиально стали возможны запросы, которые из-за особенностей постгреса вообще не использовали индексы (например, полнотекстовой поиск + сортировка по произвольному полю)

и долго-долго дрочил на производительность запросов

А чем rum то не угодил? И время запросов интересно посмотреть, можешь хоть по памяти для сравнения выложить.