- получение данных об общем количестве записей для построения пагинации, это SELECT count(id)
1. count(*), а не count(id)
2. если вас не сильно интересует абсолютно точное значение для миллионов результатов, то делаете
оценку количества, начать проще с EXPLAIN <текст запроса> вы можете получить
оценку количества результатов. Мы для себя решили, что если по оценке меньше 50 000 строк, то вслед за этим делаем обычный SELECT count(*) для получения точного количества.
Потом дергается запрос для получения данных на экспорт
1. Пожалуй, вам от этого нужно избавляться в первую очередь. Нажал человек на кнопку "экспортировать" - экспортируете, а до этого и дёргаться нет смысла. Фильтры можно получить либо при клике, либо из заголовка
referer
2. Если вам нужно абсолютно все данные, то ставите задание на экспорт в очередь и выполняете его в отдельном процессе, сохраняете в файл. Для пользователя рисуете прогресс выполнения и выводите его в нажатую пользователем кнопку, хотя можно тупо на отдельной странице выводить список "заказанных" выгрузок и ссылки на скачивание.
Запросы на каждый выпадающий список в фильтрах - SELECT distinct field_name
Можно с какой-то периодичностью выгружать выхлоп таких запросов в
материализованное представление / справочную таблицу /
ENUM. Для обновления таких справочников "в реальном времени" можно повесить триггер на вставку в основную таблицу который будет делать INSERT INTO dictionary (value, column_oid)
ON CONFLICT / ALTER TYPE
ADD VALUE IF NOT EXISTS
После чего в основной таблице заводите рядом поле под идентификатор в справочнике и индексируете уже его.
Запрос при фильтрации и сортировке - SELECT * FROM some_table WHERE field_name LIKE '%value%'
1. если у вас значения длинные (от 8 - 10 символов), то стоит попробовать
триграммные индексы. Но на коротких значениях они могут замедлить поиск раза в полтора-два.
2.
Полнотекстовый поиск. В частности есть поиск лексемы
по префиксу ts_tsquery('сло
:*') (быстро найдёт и "слово" и "словарь", но не найдёт "однословное")
3. Для полей по которым вы сделаете словари лучше делать поиск через словарь SELECT * FROM table WHERE column_dictionary_id IN (SELECT id FROM dictionary WHERE value LIKE '%текст%'). В словаре у вас наверняка на порядок - три меньше значений, а несколько сотен или тысяч значений в IN постгрес нормально пережуёт.
Полей много, разные даты, guid, названия проектов, данные из поля типа json, цены.
Активнее используйте функциональные и частичные индексы.
Например, у нас есть кадастровые номера. Триграммный индекс по ним весит 56 мбайт, а BTREE по номерам урезанным до кадастровых кварталов - 15 мбайт, в поиске к "cadastre_id LIKE '11:22:333333:1%'" добавился "AND to_quarter(cadastre_id) = '11:22:333333'", но сам поиск получается на порядок быстрее (~5 мсек вместо 50 - 70).
Главное не забывайте о стоимости этих самых функций - индекс по to_quarter может строиться всего в 1,5 раза дольше нефункционального, если делать LEFT(cadastre, -(position(':' IN reverse(cadastre))), а может и в 100 раз, если использовать регулярку.
На все индексы не поставишь, тем более что один индекс может добавить гигов 5-10 к весу.
Если ещё не обновились, то обновляйтесь на 13-ю версию, там размер BTREE индексов уменьшили в 3 раза. Ну и посмотрите, возможно вам где-то нужны GIST, GIN или BRIN индексы.
