Мне любопытно понять (и, возможно, исправить) проблему, с которой я столкнулся в PostgreSQL 9.6. Имена упрощены, но все остальное взято из сеанса psql
.
Я начинаю с материализованного представления, mv
.
Сначала я создаю две простые функции:
CREATE FUNCTION count_mv() RETURNS BIGINT AS $$
SELECT COUNT(*) FROM mv;
$$ LANGUAGE SQL STABLE PARALLEL SAFE;
а также
CREATE FUNCTION mv_pks() RETURNS TABLE (table_pk INTEGER) AS $$
SELECT table_pk FROM mv;
$$ LANGUAGE SQL STABLE PARALLEL SAFE;
Давайте время некоторые запросы.
db=>\timing on
Я могу очень быстро подсчитать результаты материализованного представления.
db=> SELECT COUNT(*) FROM mv;
count
---------
2567883
(1 row)
Time: 79.803 ms
Давайте посмотрим, как он это делает.
db=> EXPLAIN ANALYZE SELECT COUNT(*) FROM mv;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=41331.24..41331.25 rows=1 width=8) (actual time=765.681..765.681 rows=1 loops=1)
-> Gather (cost=41330.62..41331.23 rows=6 width=8) (actual time=765.557..765.670 rows=7 loops=1)
Workers Planned: 6
Workers Launched: 6
-> Partial Aggregate (cost=40330.62..40330.63 rows=1 width=8) (actual time=760.175..760.175 rows=1 loops=7)
-> Parallel Seq Scan on mv (cost=0.00..39261.09 rows=427809 width=0) (actual time=0.014..397.952 rows=366840 loops=7)
Planning time: 0.326 ms
Execution time: 769.934 ms
(8 rows)
Хороший. Так что это использование нескольких работников. Но почему запрос выполняется намного медленнее при использовании EXPLAIN ANALYZE
?
Теперь я использую функцию count_mv()
, которая имеет тот же базовый SQL и объявлена как STABLE
.
db=> select count_mv();
count_mv
------------
2567883
(1 row)
Time: 406.058 ms
Вау! Почему это медленнее, чем тот же SQL в материализованном представлении? И намного медленнее! Разве это не использование параллельных рабочих процессов, и если нет, то почему?
НАЧАТЬ РЕДАКТИРОВАНИЕ
Как было предложено в ответе ниже, я загрузил модуль auto_explain
и проверил вывод журнала для EXPLAIN
при вызове функции.
Query Text:
SELECT COUNT(*) FROM mv;
Finalize Aggregate (cost=41331.60..41331.61 rows=1 width=8) (actual time=1345.446..1345.446 rows=1 loops=1)
-> Gather (cost=41330.97..41331.58 rows=6 width=8) (actual time=1345.438..1345.440 rows=1 loops=1)
Workers Planned: 6
Workers Launched: 0
-> Partial Aggregate (cost=40330.97..40330.99 rows=1 width=8) (actual time=1345.435..1345.435 rows=1 loops=1)
-> Parallel Seq Scan on mv (cost=0.00..39261.38 rows=427838 width=0) (actual time=0.020..791.022 rows=2567883 loops=1)
Новый вопрос: почему запланировано 6 воркеров, но ни один не запущен. В остальном сервер простаивает, конфигурация такая же, и запрос тот же.
ЗАВЕРШИТЬ РЕДАКТИРОВАНИЕ
Хорошо. Так что, если я сделаю это:
db=> SELECT COUNT(*) FROM mv_pks();
count
---------
2567883
(1 row)
Time: 72.687 ms
Такая же производительность, как подсчет строк в материализованном представлении напрямую без использования EXPLAIN ANALYZE
, но здесь вам придется мне поверить: производительность этой функции зависит от состояния материализованного представления при создании функции. Быстрое время здесь является результатом создания функции, когда таблица пуста. Если я воссоздаю функцию, когда таблица заполнена, для запуска функции требуется более 1000 мс!
Подводя итоги моих вопросов:
- Почему SQL-запрос внутри
STABLE
функции SQL без параметров выполняется намного медленнее, чем запрос вне этой функции. - Почему SQL-запрос намного медленнее при использовании
EXPLAIN ANALYZE
? - Почему я получаю разные результаты при подсчете строк с помощью функции, которая может быть либо эквивалентно быстрой подсчету строк в материализованном представлении, либо медленнее, чем любой другой метод, в зависимости от того, когда функция была создана?
Заранее спасибо!