Comments 24
Такие советы удобнее всего давать на примере конкретного плана - дайте ссылочку.
В статье нигде не сказано, что IN/ANY не индексируются - вполне себе индексируются. Разве что два ANY в одном условии не всегда индексируются хорошо.
А чтобы устранять хоть какой-то bottleneck, надо хотя бы одинаково представлять, о чем именно идет речь в плане производительности. Для этого снимите план запроса, закиньте его на наш сервис - и обсудим видимые проблемы.
Общая рекомендация - снять план с максимумом информации и посмотреть на него:SET track_io_timing = TRUE;
EXPLAIN (ANALYZE, BUFFERS) { SELECT | INSERT | UPDATE | DELETE } …
Там причин может быть вагон: от неактуальной статистики по таблице до небыстрой проверки присутствия ключа в большом массиве, от неудачно выбранного плана с Merge Join и полной вычиткой индекса до неприлично раздувшейся от UPDATE'ов таблицы.
Но без пары планов для сравнения это все не более чем гадание на кофейной гуще.
Типовых вариантов два:
долгое планирование - особенно, если в таблицах много секций
непрогретый кэш данных, когда первое обращение поднимает солидный кусок индекса с диска (shared read) в память, а последующие запросы обращаются к нему же уже в памяти (shared hit)
когда функция вызывается и возвращает refcursor, то запрос этого курсора уже выполнен и его recordset типа ждет уже где-то там в памяти, и каждый fetch просто передвигает указатель в памяти на следующую запись и считывает ее
Нет, наоборот. https://postgrespro.ru/docs/postgresql/14/plpgsql-cursors
Вместо того чтобы сразу выполнять весь запрос, есть возможность настроить курсор, инкапсулирующий запрос, и затем получать результат запроса по нескольку строк за раз. Одна из причин так делать заключается в том, чтобы избежать переполнения памяти, когда результат содержит большое количество строк.
Так они их "используют" в том смысле, что не формируют и не извлекают всю выборку целиком.
Фактически, надо получить план (например, подключив auto_explain) происходящего при первом FETCH и последующих. Поскольку OPEN стоит отдельно и тормозит не на нем (а план формируется именно в этот момент), то верна именно гипотеза №2 про shared read и чтение с носителя.
Если дело именно в чтении "холодных" данных, то pg_prewarm, например.
Конечно, всегда есть вариант, что сначала читается 100500 неподходящих под какую-нить фильтрацию записей, а потом идет каждая - нужная.
https://postgrespro.ru/docs/postgresql/14/pgprewarm
Предварительную загрузку можно выполнить вручную, вызвав функцию
pg_prewarm, или автоматически, добавивpg_prewarmв shared_preload_libraries. Во втором случае система запустит фоновый процесс, который будет периодически записывать содержимое разделяемых буферов в файлautoprewarm.blocksс тем, чтобы эти блоки подгружались в память при запуске сервера, используя два дополнительных фоновых процесса.
Будет что-то типа SELECT pg_prewarm('my_table_name'::regclass);, только не внутри этой функции, а где-то при начале работы.
Когда-то же оно стартует ведь?
Очевидно, чтобы эффект "прогрева" уже сказывался при чтении из курсора, он должен быть заказан где-то раньше. Как вариант - при старте самого приложения.
Наши умозрительные эксперименты "посоветуй то, не знаю что, тогда, не знаю когда" уже ушли слишком далеко от темы статьи, поэтому предлагаю продолжать в личке, чтобы не оффтопить тут дальше.
Ещё добавил бы, что индекс hash не поддерживает WAL, поэтому
они не crash safe. Их в случае сбоев надо их перестраивать (но, понятное дело никто после падения об этом не встромнит и после перезапуска пользователи будут жить с закорапченым индексом)
при физической репликации индексы не будут обновляться (тоже ничего хорошего не будет после сбоев)
Это не так уже с PG10: вот
Псс, парень… индекс нужен?