Как я могу получить текущее значение параметра локальной конфигурации postgresql?
Я практикуюсь с оптимизацией запросов (Postgres 13). В частности, настройка параметра work_mem.
Вот схема БД: https://postgrespro.com/docs/postgrespro/12/apks02.
У меня есть функция sql get_passengers_and_flights:
CREATE FUNCTION get_passengers_and_flights(d timestamptz)
RETURNS TABLE(passenger_name text, flight_no text)
AS $$
SELECT t.passenger_name, f.flight_no
FROM tickets t
JOIN ticket_flights tf ON tf.ticket_no = t.ticket_no
JOIN flights f ON f.flight_id = tf.flight_id
WHERE f.scheduled_departure >= date_trunc('month', d)
AND f.scheduled_departure < date_trunc('month', d) + interval '1 month'
ORDER BY f.scheduled_departure, t.passenger_name;
$$ LANGUAGE sql;
И простой запрос:
SELECT * FROM get_passengers_and_flights('2017-06-01') LIMIT 3;
По умолчанию workmem = '4MB', поэтому сортировка занимает некоторое время. Один из способов улучшить работу — увеличить work_mem с «4 МБ» до «32 МБ» или более. В последних версиях postgresql мы можем сделать это локально (для функции, таблицы и т. д.):
ALTER FUNCTION get_passengers_and_flights SET work_mem = '32MB';
Итак, была выбрана быстрая сортировка, и время выполнения сократилось.
Но вопрос в том, как я могу увидеть локальное значение work_mem для функции get_passengers_and_flights?
Очевидный способ получить значение work_mem:
SELECT current_setting('work_mem');
Вывод представляет собой глобальное значение «4 МБ», так как я могу получить локальное «32 МБ»? Я пробежался по описанию системных каталогов и ничего не нашел.
Вопрос такой: увидеть локальное значение где? Это просто видно внутри тела функции. Или действительно хотите увидеть свойства функции? (По сути, оператор CREATE FUNCTION.) Кроме того, в моих руках на Postgres 15 этот запрос использует Incremental Sort с таким маленьким LIMIT. Вы можете начать с неправильного дерева, но недостаточно информации, чтобы задать правильный вопрос о производительности. См.: stackoverflow.com/tags/postgresql/info
Я понимаю. У меня Postgres 13. Сначала он получает всю таблицу как результат функции, а затем подбирает ее. Так что у меня нет инкрементной сортировки в моем случае, потому что Postgres 13 просто не может заглянуть в результат функции до самого конца. Вероятно, это было исправлено в Postgres 15...
Честно говоря, это не вопрос производительности. Я просто дал контекст проблемы. Это больше о настройке Postgres. В любом случае, спасибо за ваше мнение, в следующий раз постараюсь быть более точным.
Прежде чем что-либо менять в конфигурации, я бы начал с плана запроса, используя EXPLAIN(ANALYZE, VERBOSE, BUFFERS). При правильном индексировании это должно быть молниеносно, даже с небольшой настройкой памяти для work_mem.
Кстати, самая большая проблема — это LIMIT 3 вне функции.
Вы не получите план выполнения запроса внутри функции, используя объяснение. Auto_explain — это способ. Что касается оптимизации, то я просто играюсь с локальными параметрами, в том-то и дело) Я не утверждаю, что это лучший способ оптимизировать запрос.
Ответы 2
Следующий запрос должен дать желаемые результаты:
SELECT
REGEXP_SUBSTR(c.setting, '[^=]+$') AS work_mem
FROM
pg_proc
CROSS JOIN unnest(pg_proc.proconfig) c(setting)
WHERE
oid = 'public.get_passengers_and_flights(timestamptz)'::regprocedure
AND c.setting LIKE 'work_mem=%';
NULL возвращается, если work_mem не был локально сконфигурирован для функции.
Я пересмотрел свой ответ, чтобы обратиться к перегруженным функциям, используя regprocedure, чтобы гарантировать, что возвращается только настройка предполагаемой функции. Спасибо Эрвину Брандштеттеру за упоминание об этом.
Если все, что вам нужно, это просмотреть свойства созданной функции, используйте специальную функцию системной информации pg_get_functiondef():
SELECT pg_get_functiondef('public.get_passengers_and_flights(timestamptz)'::regprocedure);
Это надежно получает OID функции — при условии, что она создана в схеме public:
'public.get_passengers_and_flights(timestamptz)'::regprocedure
Видеть:
Я не уверен, как сделать именно то, что вы хотите, но... Чтобы заставить его, а не устанавливать и проверять его для каждого соединения, отредактируйте значение в /usr/local/var/postgres/postgresql.conf и перезапустите постгрес сервер. Просто будьте осторожны, так как это предназначено для предотвращения голодающих процессов. Я считаю, что общая оперативная память = work_mem * одновременные соединения. work_mem назначается для каждого соединения. Я точно не знаю, как проверить голодающие процессы, но есть чему поучиться.