postgresql - Postgres 查询不会在功能中完成,但如果单独运行它可以工作
问题描述
这是我的函数的简化版本,其中包含查询(因此任何变量现在都无用)并且此函数不会完成,但如果我单独运行相同的查询,它会在一秒钟内完成。
永远不会完成的功能
select * from test_function_difference(1);
CREATE OR REPLACE FUNCTION test_function_difference (
p_does_nothing int
)
RETURNS TABLE(
t_datum date,
t_capacity numeric,
t_used numeric,
t_category int,
t_category_name text,
t_used_p numeric,
t_unused_p numeric
)
VOLATILE
AS $dbvis$
declare
p_sql text := '';
p_execute text := '';
rec record;
begin
p_sql :=
'
with
vytizeni as (
select
date_trunc(''day'',mcz.datum)::date as datum ,
sum(zd.v_vytizeni)/3600.0 used
from v_ui_cdc_s5_misto_cas_zdroj_aggregace mcz
left join (select * , pul_den as den_noc from v_ui_cdc_s5_misto_cas_zdroj_aggregace_zdrobneni) zd on mcz.id = zd.id
where
datum between ''2018-12-31'' and ''2018-12-31''
and ( zahranicni = 0 or zahranicni is null )
and den_noc = -1
group by
date_trunc(''day'',mcz.datum)::date
)
,kapacita as (
select
date_trunc(''day'',datum)::date as datum ,
sum(obsazeni_g)/3600.0 capacity
from v_ui_cdc_s5_misto_cas_zdroj_aggregace
where
datum between ''2018-12-31'' and ''2018-12-31''
group by
date_trunc(''day'',datum)::date
)
,zdroj as (
select
k.datum,
k.capacity,
v.used,
-1 category
from kapacita k
join vytizeni v on k.datum = v.datum
)
select
c.* ,
kc.nazev::text categeroy_name,
case when sum(capacity)over(partition by datum) = 0 then 1 else used/sum(capacity)over(partition by datum) end as used_p,
greatest(1 - case when sum(capacity)over(partition by datum) = 0 then 1 else sum(used)over(partition by datum)/sum(capacity)over(partition by datum) end,0) as unused_p
from zdroj c
left join v_ui_cdc_s5_kategorie_cinnosti kc on kc.id = c.category
order by c.datum
';
raise notice '% ' , p_sql;
RETURN QUERY
execute p_sql;
END;
$dbvis$ LANGUAGE plpgsql
以及我单独运行的查询(在 533 毫秒内完成)
with
vytizeni as (
select
date_trunc('day',mcz.datum)::date as datum ,
sum(zd.v_vytizeni)/3600.0 used
from v_ui_cdc_s5_misto_cas_zdroj_aggregace mcz
left join (select * , pul_den as den_noc from v_ui_cdc_s5_misto_cas_zdroj_aggregace_zdrobneni) zd on mcz.id = zd.id
where
datum between '2018-12-31' and '2018-12-31'
and ( zahranicni = 0 or zahranicni is null )
and den_noc = -1
group by
date_trunc('day',mcz.datum)::date
)
,kapacita as (
select
date_trunc('day',datum)::date as datum ,
sum(obsazeni_g)/3600.0 capacity
from v_ui_cdc_s5_misto_cas_zdroj_aggregace
where
datum between '2018-12-31' and '2018-12-31'
group by
date_trunc('day',datum)::date
)
,zdroj as (
select
k.datum,
k.capacity,
v.used,
-1 category
from kapacita k
join vytizeni v on k.datum = v.datum
)
select
c.* ,
kc.nazev::text categeroy_name,
case when sum(capacity)over(partition by datum) = 0 then 1 else used/sum(capacity)over(partition by datum) end as used_p,
greatest(1 - case when sum(capacity)over(partition by datum) = 0 then 1 else sum(used)over(partition by datum)/sum(capacity)over(partition by datum) end,0) as unused_p
from zdroj c
left join v_ui_cdc_s5_kategorie_cinnosti kc on kc.id = c.category
order by c.datum
编辑:我能够在将近 28 分钟后从该功能中获得结果(我也在周日晚上尝试过,这意味着我拥有整个服务器的资源,因为在正常加载过程中,即使一小时后功能也没有完成),然后就在我独立运行查询并在 2.1 秒后得到结果这是解释分析
功能:28 分钟 https://explain.depesz.com/s/v9xJ
独立查询:2.1 秒 https://explain.depesz.com/s/aBri
第二次运行独立 430 毫秒 https://explain.depesz.com/s/ENva
有趣的注意事项:如果我将间隔的开始日期编辑为“2018-12-30”或任何其他日期,该函数也会完成
意思是
start date = '2018-12-31'
query => finishes under 1 second
function => won't finish
start date = '2018-12-30'
query => finishes under 1 second
function => finishes under 1 second
版本详细信息:x86_64-pc-linux-gnu 上的 PostgreSQL 10.7,由 gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36) 编译,64 位
解决方案
性能差异的原因是函数内部的执行没有使用并行查询,并行执行偶然选择了一个更好的执行计划。
该功能是否未标记为并行安全?这可能会让一切变得不同。
然而,核心问题是对扫描结果行数的粗密估计ui_cdc_s5_misto_cas_zdroj_aggregace
,它估计为 1 而不是 2243 行。
您应该ANALYZE
使用该表以获得更好的估计。如果仅此一项并不能提高估计值,请尝试default_statistics_target
在ANALYZE
.
如果您需要提高default_statistics_target
以获得更好的估计,请坚持更改
ALTER TABLE ui_cdc_s5_misto_cas_zdroj_aggregace
ALTER datum SET STATISTICS <whatever proved useful>
推荐阅读
- sql - Postgres - How do I check for the date a specific field in a row has been last modified?
- python - 如何在使用python中的函数检查文件是否存在时继续循环
- ios - UIScrollView 中的 UILayoutPriority 不会影响任何内容
- python - 当等于特定值时移动列中的行
- cmd - 无法在 Windows 10 中使用 cmd 运行命令
- android - 为什么新的 google places api 不与 build.gradle 同步?
- javascript - 怎么做
默认值为“false” - amazon-web-services - 解析在 AWS Athena/AWS Glue 目录中的一列中具有换行符的 csv 文件
- android - Google Place Autocomplete 无法解析兼容库中的 Place.Field 字段
- java - 控制器中的原型范围 bean 返回相同的实例 - Spring Boot