0

I'm creating stock market related app and I've made this atrocity below trying to get current stock indicators. I'm storing some data in jsonb (because normalizing at this point would be rather hard since I'm just dumping data from other source).

SELECT
  s.ticker,
  round(p.current_price * lastQ.share_count) as market_cap,
  round(
    (p.current_price * lastQ.share_count) 
    + (((lastQ.balance->'BalanceNoncurrentLiabilities')::numeric * 1000.) + ((lastQ.balance->'BalanceCurrentLiabilities')::numeric * 1000.)) 
    - (lastQ.balance->'BalanceCash')::numeric * 1000.)
  as enterprise_value,
  round((p.current_price * lastQ.share_count) / (q.one_year_earnings)) as price_to_earnings
FROM stock s
  INNER JOIN (
    select distinct on (ticker) * from stock_price order by ticker, price_date desc
    ) p
    ON p.ticker = s.ticker
  INNER JOIN (
    select distinct on (ticker) * from quarterly order by ticker, quarter_date desc
  ) lastQ
    ON lastQ.ticker = s.ticker
  INNER JOIN (
    select 
      ticker, 
      (SUM((q.financial->'IncomeShareholderNetProfit')::numeric * 1000.)) as one_year_earnings 
      from (
            select 
            ROW_NUMBER () OVER (
              PARTITION BY ticker
              ORDER BY quarter_date desc
            ) rn,
            * from quarterly order by ticker, quarter_date desc
          ) q where q.rn <= 4 
    group by ticker
  ) q on q.ticker = s.ticker 

So I have 2 problems with this query. First - it's slow. That's to be expected I guess since I'm joining 3 tables, to calculate market cap I need latest 'stock_price' and multiply it by 'share_amount' that's in 'quarterly' table. For every stock I have thousands rows of prices and hundreds of quarterly data. I suspect that some of slowness might be coming from the fact that I'm joining on subqueries instead of tables, but I think I'm forced to do that as I want to retrieve current data for every stock and I need to get 1 row from 'stock' table, 1 from 'stock_price' and 4 from 'quarterly' (right now i'm only getting one).

My question is: what would you do in that situation. Offload calculating those derived values to backend and save them in new table at interval?

I don't see any easy way to speed up this query, what's worse it will probably get more complex when I'll start adding new stuff to it.

EDIT. According to your suggestions I'll post 'explain' here:

Hash Join  (cost=265412.84..282303.46 rows=767 width=28)
  Hash Cond: ((stock_price.ticker)::text = (s.ticker)::text)
  ->  Merge Join  (cost=265390.43..282231.08 rows=767 width=659)
        Merge Cond: ((stock_price.ticker)::text = (quarterly_1.ticker)::text)
        ->  Merge Join  (cost=249716.29..260964.40 rows=702 width=623)
              Merge Cond: ((stock_price.ticker)::text = (quarterly.ticker)::text)
              ->  Unique  (cost=233940.66..240932.93 rows=702 width=32)
                    ->  Sort  (cost=233940.66..237436.80 rows=1398455 width=32)
                          Sort Key: stock_price.ticker, stock_price.price_date DESC
                          ->  Seq Scan on stock_price  (cost=0.00..24270.55 rows=1398455 width=32)
              ->  Unique  (cost=15775.64..20006.08 rows=767 width=719)
                    ->  Gather Merge  (cost=15775.64..19917.18 rows=35560 width=719)
                          Workers Planned: 2
                          ->  Sort  (cost=14775.61..14812.65 rows=14817 width=719)
                                Sort Key: quarterly.ticker, quarterly.quarter_date DESC
                                ->  Parallel Seq Scan on quarterly  (cost=0.00..9038.17 rows=14817 width=719)
        ->  GroupAggregate  (cost=15674.14..21247.67 rows=767 width=36)
              Group Key: quarterly_1.ticker
              ->  WindowAgg  (cost=15674.14..20437.98 rows=35560 width=817)
                    Run Condition: (row_number() OVER (?) <= 4)
                    ->  Gather Merge  (cost=15674.14..19815.68 rows=35560 width=701)
                          Workers Planned: 2
                          ->  Sort  (cost=14674.11..14711.15 rows=14817 width=701)
                                Sort Key: quarterly_1.ticker, quarterly_1.quarter_date DESC
                                ->  Parallel Seq Scan on quarterly quarterly_1  (cost=0.00..9038.17 rows=14817 width=701)
  ->  Hash  (cost=12.74..12.74 rows=774 width=4)
        ->  Seq Scan on stock s  (cost=0.00..12.74 rows=774 width=4)
JIT:
  Functions: 35
  Options: Inlining false, Optimization false, Expressions true, Deforming true

This query takes ~1,5 sec on average and will get longer as I'll keep adding new columns to it. Now that I think about it it's still incredibly fast for what it's doing so I'll ask you one another question, is that time acceptable?

I don't have commercial coding experience and I'm not really sure if that's something that company would work on solving or just would let it run as long as it takes (in other words when would it be good time to optimize).

I'm using this query to fill data on page that showcases stock statistics for all stocks in DB, so I guess it's not very good idea to make page load take 2+ sec. I don't think it can be sped up and the only solutions I see for this kind of query are: caching or making new table for it. Materialized views could also work, but as far as I understand it materialized view always updates whole table and has no way to update just one 'row'. So it would be difficult to create trigger that would update data in this case.

6
  • 1
    Proper index support is vital. Have you used EXPLAIN to see where it’s sequentially scanning? For example, an index on stock_price.ticker + price_date, and a similar one on the quarterly table.
    – RonJohn
    Commented Mar 6, 2023 at 2:19
  • Without knowing the EXPLAIN (ANALYZE, BUFFERS) output, I can only give the vague recommendation to consider materialized views. Commented Mar 6, 2023 at 3:07
  • Maybe there is a way to speed it up without changing the structure. Without an EXPLAIN (ANALYZE), we might as well be bowling with cinder blocks and without any pins. But storing just the latest value, if that is what you always use, would certainly make things easier.
    – jjanes
    Commented Mar 6, 2023 at 3:09
  • There's no where vlause or order by ? Commented Mar 6, 2023 at 8:36
  • @RohitGupta there's no where or order by because I want to get indicators for all stocks so I can later filter them
    – nxyt
    Commented Mar 6, 2023 at 12:54

1 Answer 1

2

This is a classic reason for the existence of Timescale. This basic candlestick query becomes something like:

SELECT symbol, first(price,time), last(price, time), price,
...snip
your calculations here
...snip
FROM stocks_real_time srt
WHERE time > now() - INTERVAL '1 day'
GROUP BY symbol
ORDER BY symbol;

and works in milliseconds.

1
  • TimescaleDB seems to speed up things really well. Changing INNER JOIN ( select distinct on (ticker) * from stock_price order by ticker, price_date desc ) p ON p.ticker = s.ticker for INNER JOIN ( select ticker, last(current_price, price_date) current_price from stock_price group by ticker ) p ON p.ticker = s.ticker sped query from ~1,5sec to 0,35sec which is enough for now. It isn't really answer to my question, but it solved my problem so I'll mark it as answer, thanks :-)
    – nxyt
    Commented Mar 14, 2023 at 14:51

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.