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

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
    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: enter image description here

1 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[],
  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.

  • 1
    This is much faster, bringing it down to ~14.1 seconds. The finalfunc is actually only adding about 200ms of time to the entire query, so the bottleneck is the tech_float8_accum function. Not sure how to write postgres C yet, going to have to learn. Also, I had to write INITCOND = '{}' since I was getting a syntax error using the array constructor. Commented Nov 21, 2023 at 18:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.