M
Assuming current Postgres 14 for lack of declaration.
Index
A https://www.postgresql.org/docs/current/indexes-multicolumn.html on (underlying_symbol, expiration_date) would make your query fast. Any variant will do as long as underlying_symbol is the leading column: equality first, range later. See:
https://dba.stackexchange.com/questions/33196/multicolumn-index-and-performance/33220#33220
https://dba.stackexchange.com/questions/27481/is-a-composite-index-also-good-for-queries-on-the-first-field
https://dba.stackexchange.com/questions/90128/unused-index-in-range-of-dates-query/90183#90183
Even a plain index on just (underlying_symbol) might go a long way - depending on undisclosed data distribution.
Seeing that your table has very wide rows, a "covering" index might be ideal - if you get index-only scans out of it:
CREATE INDEX foo ON cboe_raws (underlying_symbol, expiration_date DESC NULLS LAST)
INCLUDE (strike_price, call_put_flag, mm_buy_vol, mm_sell_vol, mm_buy_vol);
Function
You typically don't need PL/pgSQL for a single, plain SQL query. Use an SQL function instead. I suggest the new standard-SQL variant:
CREATE OR REPLACE FUNCTION market_maker_books(_symbol varchar)
RETURNS TABLE (contract_underlying_symbol varchar
, contract_expiration_date date
, contract_strike_price float8
, contract_call_put_flag varchar
, contract_mm_buy_vol_sum bigint
, contract_mm_sell_vol_sum bigint
, contract_buys_minus_sells bigint)
LANGUAGE sql STABLE STRICT PARALLEL SAFE
BEGIN ATOMIC
SELECT c.underlying_symbol
, c.expiration_date
, c.strike_price
, c.call_put_flag
, SUM(c.mm_buy_vol) -- AS mm_buy_vol_sum
, SUM(c.mm_sell_vol) -- AS mm_sell_vol_sum
, SUM(c.mm_buy_vol) - SUM(c.mm_sell_vol) -- AS buys_minus_sells
FROM cboe_raws c
WHERE c.underlying_symbol = _symbol
AND c.expiration_date >= CURRENT_DATE
GROUP BY c.underlying_symbol, c.expiration_date, c.strike_price, c.call_put_flag
ORDER BY c.expiration_date, c.call_put_flag, c.strike_price;
END;
See:
https://dba.stackexchange.com/questions/300507/what-does-begin-atomic-end-mean-in-a-postgresql-sql-function-procedure/300512#300512
Might be untangled and compacted further:
CREATE OR REPLACE FUNCTION market_maker_books(_symbol varchar)
RETURNS TABLE (expiration_date date
, call_put_flag varchar
, strike_price float8
, mm_buy_vol_sum bigint
, mm_sell_vol_sum bigint
, buys_minus_sells bigint)
LANGUAGE sql STABLE STRICT PARALLEL SAFE
BEGIN ATOMIC
SELECT c.expiration_date, c.call_put_flag, c.strike_price
, SUM(c.mm_buy_vol)
, SUM(c.mm_sell_vol)
, SUM(c.mm_buy_vol) - SUM(c.mm_sell_vol)
FROM cboe_raws c
WHERE c.underlying_symbol = _symbol
AND c.expiration_date >= CURRENT_DATE
GROUP BY 1, 2, 3
ORDER BY 1, 2, 3;
END;