how can I speed up query with 3 function calls in PostgreSQL

Question

I have a fucntion pb(IDCODE,0) running it with IDCODE=320 gives this sample data:

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

*it actually gives more rows (like 20000) but I reduced it for the example

pb is a very heavy function but in simple terms it shows activities based on their order.

I want to find the entrydate of the first occurrences of qty<0 after the last row of qty>0.

In order to do that I need to do something like this:

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

In the above sample the requested result is 3.10.17 Because:

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

and

logid=6 is Min(logid) where qty<0 and logid>(select max(logid) where qty>=0) which in fact is : Select Min(logid) where qty<0 and logid>5

So I wrote the following query:

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))

This works great but it's 3 times that I call the function pb(320,0).

It's huge time consuming and needless to say that I actually run this query on many IDCODES (like 214) so pb(IDCODE,0) actually runs 214*3 this is horrible.

What can I do?

if the output of the function won't change in single statement change the volatility of the function to stable and compare results — JustMe
– JustMe, Commented Oct 25, 2017 at 11:23
Rewrite the function into a VIEW? BTW: what is inside the function? — wildplasser
– wildplasser, Commented Oct 25, 2017 at 11:26
PostgreSQL sometimes loses a lot of performance on sub-selects. In functions you are better off assigning results into variables (like SELECT max() INTO maxValue...) and using the variable in a more simple query. Just my two cents. — Boris Schegolev
– Boris Schegolev, Commented Oct 25, 2017 at 11:35
@BorisSchegolev: for the planner there is no way to get an estimate for the number of rows resulting from the function call. — wildplasser
– wildplasser, Commented Oct 25, 2017 at 11:46

Gordon Linoff · Accepted Answer · 2017-10-25 11:31:58Z

2

First, use a CTE, because Postgres might materialize the CTE.

However, you need only one table reference if you use window functions:

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;

More recent versions of Postgres support the filter clause which is a bit more efficient than the case.

answered Oct 25, 2017 at 11:31

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

how can I speed up query with 3 function calls in PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related