sql - 如何在 PostgreSQL 中使用 3 个函数调用加快查询速度

Question

我有一个 fucntion pb(IDCODE,0)使用 IDCODE=320 运行它给出这个示例数据:

select *
from pb(320,0)



logid entrydate qty
1       1.10.17   5
2       1.10.17   6
3       1.10.17   5
4       1.10.17   -3
5       2.10.17   6
6       3.10.17  -100

*它实际上提供了更多的行(比如 20000)但是我为了这个例子减少了它

pb 是一个非常重的函数，但简单来说，它根据顺序显示事件。

我想找到 entrydate第一次出现 qty<0在最后一行 qty>0 之后.

为了做到这一点，我需要做这样的事情:

Select Min(logid) where qty<0 and logid>(select max(logid) where qty>=0)

在上面的示例中，请求的结果是 3.10.17因为:

logid=5是max(logid) where qty>=0

和

logid=6是Min(logid) where qty<0 and logid>(select max(logid) where qty>=0) 实际上是:Select Min(logid) where qty<0 and logid>5

所以我写了下面的查询:

select entrydate
from pb(320,0)
where logid= (     SELECT min(logid)
                    FROM pb(320,0)
                    where qty<0 and logid>(SELECT coalesce(max(logid),0)
                                           FROM pb(320,0)
                                           WHERE qty >= 0))

这很好用，但我调用函数 pb(320,0) 的次数是 3 次.

这非常耗时，不用说我实际上在很多 IDCODES(比如 214)上运行这个查询所以 pb(IDCODE,0)实际运行214*3这太可怕了。

我能做什么？

Answer 1

首先，使用 CTE，因为 Postgres 可能会具体化 CTE。

但是，如果您使用窗口函数，您只需要一个表引用:

with t as (
      select *
      from pb(320,0)
     )
select t.*
from (select t.*, max(case when qty > 0 then logid end) over () as last_poslogid
      from t
     ) t
where id > last_poslogid and qty < 0
order by id
fetch first 1 row only;

较新版本的 Postgres 支持 filter 子句，它比 case 更有效。