Моя проблема началась, когда я пытался обернуть простой оператор sql как функцию. Запрос, на выполнение которого уходило несколько мс, стал выполняться за секунды. Я прочитал все, что мог, в Google, но это было все об использовании параметров функции. Затем я избавился от параметров, но это не решило проблему.
Итак, теперь у меня есть следующий оператор SQL
select fn_tz(idata.system_timestamp), gp.zname, fc.*
from jv idata
join gp on st_distance(waypoint,geom)=0
join fc ON
idata.fare_code = fc.fare_code AND
fc.validity @> fn_tz(idata.system_timestamp)::date and
fc.polygon_name = gp.name
where idata.item_id='21159704983720122917';
Когда я запускаю этот запрос напрямую, для его выполнения требуется около 80 мс. Но если я помещу тот же запрос без каких-либо изменений в тело функции, это займет около 10 секунд!
Во всяком случае, я пробовал все, что пришло мне в голову
- Обычная функция PLPGSQL (
return query ...
) - Обычная функция SQL (
select ...
) - Функция PLPGSQL с динамическим SQL-запросом (
return query execute 'select ...'
) - Наконец-то я попробовал подготовить/выполнить оператор
Все вышеперечисленные способы дают один и тот же результат - 10 сек.
Затем я запускаю EXPLAIN ANALYZE EXECUTE ...
для подготовленного оператора, но даже из его вывода я не могу понять, почему он работает 10 секунд.
Hash Join (cost=75.05..962862.24 rows=110 width=8) (actual time=1.075..10290.527 rows=476091 loops=1)
Hash Cond: ((idata.fare_code = fc.fare_code) AND (gp.name = (fc.polygon_name)::text))
Join Filter: (fc.validity @> (fn_tz(idata.system_timestamp))::date)
-> Nested Loop (cost=0.00..925098.69 rows=59399 width=54) (actual time=0.298..8300.070 rows=53922 loops=1)
Join Filter: (st_distance(idata.waypoint, gp.geom) = '0'::double precision)
Rows Removed by Join Filter: 2212398
-> Seq Scan on jv idata (cost=0.00..4402.99 rows=53999 width=54) (actual time=0.039..33.038 rows=53960 loops=1)
Filter: (item_id = '21159704983720122917'::text)
Rows Removed by Filter: 3079
-> Materialize (cost=0.00..13.30 rows=220 width=64) (actual time=0.000..0.003 rows=42 loops=53960)
-> Seq Scan on gp (cost=0.00..12.20 rows=220 width=64) (actual time=0.006..0.025 rows=42 loops=1)
-> Hash (cost=40.22..40.22 rows=2322 width=16) (actual time=0.717..0.717 rows=2268 loops=1)
Buckets: 4096 Batches: 1 Memory Usage: 141kB
-> Seq Scan on fc (cost=0.00..40.22 rows=2322 width=16) (actual time=0.008..0.332 rows=2322 loops=1)
Planning Time: 0.008 ms
Execution Time: 10324.558 ms
Удивительно, но если я запускаю EXPLAIN ANALYZE
для исходного запроса, это также занимает около 10 секунд и дает почти идентичный план выполнения.
Мой сервер — управляемый экземпляр Postgres 11.8 на Google Cloud Platform
Что еще я могу сделать/попробовать?
Upd: Кажется, мне нужно подчеркнуть - я не ищу способ улучшить производительность запроса. Прямой запрос выполняется 80 мс, и я доволен этим. Я хочу найти причину - почему прямой запрос выполняется в 100 раз(!) быстрее, чем тело функции.
fn_tz(idata.system_timestamp)::date
? Зачем вам здесь функция? - person wildplasser   schedule 17.08.2020return query execute
наreturn query
в коде функции и сравните время выполнения. postgresql.org/docs/11/. - person Slava Rozhnev   schedule 17.08.2020return query
мой первый вариант - person kayman   schedule 17.08.2020