How to improve custom aggregate function performance & identify bottlenecks

Question

This is a continuation of a question I asked on the best way to compute statistics on a list of rows unique per column, which can be found here (along with the table schema)

I have a table, which holds millions of rows of stock data and I want to compute custom aggregates on these rows. The idea is to append each input value in the state transition function. Then the finalfunc will compute the number on this array. The aggregate is defined as:

create or replace aggregate RSI(input float8) (
  SFUNC=tech_float8_accum,
  STYPE=float8[],
  FINALFUNC=RSI_Func
);

Where I have naively implemented the array aggregation function in plpython:

-- Append the next input value to the state array
CREATE OR REPLACE function tech_float8_accum(agg float8[], input float8)
returns float8[]
AS $$
    return agg + [input] if agg != None else [input]
$$ LANGUAGE plpython3u;

The finalfunc is also written in plpython, and from experience this quite fast at least outside of a database context given it uses Cython under the hood.

CREATE OR REPLACE FUNCTION RSI_Func(input float8[], out val float8)
AS $$
    import talib
    import numpy as np
    
    cd=np.array(input)
    rsi = talib.RSI(cd)
    return rsi[-1]
$$ LANGUAGE plpython3u;

Current usage:

select "security", RSI(ordered.close)
        from (
            select "security", close
            from stocks_data.bars
            where "timeframe" = '1d'
            and "timestamp" >= '2022-11-02'::timestamp
            order by "timestamp" asc
        ) as ordered
        group by ordered.security;

This takes approx 3 minutes, when in reality I need something around 3 seconds or less like the built in AVG function offers.

Is there something I can do to drastically improve this approach, or should I take another approach altogether? It is too much data to bring in-memory.

Analyze of usage query:

Answer 1

You should check how much time is spent in your FINALFUNC, but you can speed up the SFUNC by writing it in SQL:

CREATE FUNCTION tech_float8_accum(agg float8[], input float8)
RETURNS float8[]
RETURN agg || input;

To keep it simple, I omit the check for a NULL array. To make up for that, we need to add an INITCOND to the aggregate definition:

CREATE AGGREGATE rsi(float8) (
  SFUNC = tech_float8_accum,
  STYPE = float8[],
  FINALFUNC = RSI_Func,
  INITCOND = '{}'
);

If that still isn't fast enough, you could write the functions in C.

Next time, please add execution plans as formatted text, not as image.