我正在使用 jooq 生成 SQL 查询,并且我有点懒惰从 Bottom_select 的 another_select 生成嵌套选择。我生成的查询包含重复的表达式。postgres 13+ 是否在幕后应用了一些优化技术?
查询的简化示例部分
SELECT
case
when b.count is null then 100.00
when c.count is null then -100.00
else round(
cast((100.00 * ((c.count - b.count) / cast(b.count as numeric))) as numeric),
2
)
end as expression_01,
case
when b.count is null then 100.00
when c.count is null then -100.00
else round(
cast((100.00 * ((c.count - b.count) / cast(b.count as numeric))) as numeric),
2
)
end as expression_02
from some_table
比方说
round(
cast((100.00 * ((c.count - b.count) / cast(b.count as numeric))) as numeric),
2
)
重复多次,Postgres会计算一次还是多次?
据我所知,PostgreSQL没有这样的优化,会多次计算表达式。如果查询足够昂贵并且启用了 JIT,它可能会为表达式创建自定义可执行代码以加快处理速度,但它仍然会执行两次。