0

I am storing basic end of day (EOD) stock values and need to calculate splits occasionally. The current SQL is extremely slow and consumes a large amount of temp space.

My database is Postgres14

The stock table is

CREATE TABLE appl.eod_data (
   eod_id BIGINT GENERATED ALWAYS AS IDENTITY,
   symbol_id BIGINT,
   exchange VARCHAR(10) NOT NULL,
   FOREIGN KEY(symbol_id) 
      REFERENCES appl.symbols(id),
   d_date DATE DEFAULT NOW(),
   open NUMERIC,
   high NUMERIC,
   low NUMERIC,
   close NUMERIC,
   volume BIGINT,
   UNIQUE (symbol_id, d_date, exchange)
)  PARTITION BY LIST(exchange);

CREATE INDEX eod_data_alldata_idx ON appl.eod_data(symbol_id, d_date);
CREATE INDEX eod_data_id_idx ON appl.eod_data(eod_id);

If a stock split is reported, for example Apple might split 4 to 1, it is recorded to a splits table as a ratio, then the following view calculates this allowing for previous splits (there may have been many)

SQL is based on this SO answer

CREATE OR REPLACE VIEW appl.eod_splits_view AS 
SELECT appl.symbols.symbol, 
        appl.symbols.overview_id, 
        E.exchange, 
        to_char(E.d_date, 'YYYY-MM-DD') AS ta_date, 
  E.open AS unadj_open, 
  E.high AS unadj_high, 
  E.low AS unadj_low, 
  E.close AS unadj_close, 
  E.volume, 
  E.eod_id,
  E.d_date,
  coalesce(
         exp(
           sum(ln(s.ratio)) 
             over (partition by E.symbol_id
                       order by E.d_date desc
                rows between unbounded preceding
                         and 1 preceding)
         ), 1
       ) * E.open as open,
  coalesce(
         exp(
           sum(ln(s.ratio)) 
             over (partition by E.symbol_id
                       order by E.d_date desc
                rows between unbounded preceding
                         and 1 preceding)
         ), 1
       ) * E.high as high,
  coalesce(
         exp(
           sum(ln(s.ratio)) 
             over (partition by E.symbol_id
                       order by E.d_date desc
                rows between unbounded preceding
                         and 1 preceding)
         ), 1
       ) * E.low as low,
   coalesce(
         exp(
           sum(ln(s.ratio)) 
             over (partition by E.symbol_id
                       order by E.d_date desc
                rows between unbounded preceding
                         and 1 preceding)
         ), 1
       ) * E.close as close
FROM appl.eod_data E
INNER JOIN appl.symbols ON E.symbol_id = appl.symbols.id left join appl.splits_view s 
              on s.symbol_id = E.symbol_id 
             and s.split_date = E.d_date 
ORDER BY E.d_date;

This can take 90 seconds to 2 mins per query

The Explain Plan output is

"QUERY PLAN"
"Subquery Scan on eod_splits_view  (cost=7781666.04..7973789.11 rows=1 width=222) (actual time=107723.051..111560.642 rows=1932 loops=1)"
"  Filter: (((eod_splits_view.exchange)::text = 'NYSE'::text) AND ((eod_splits_view.symbol)::text = 'SONY'::text))"
"  Rows Removed by Filter: 10976523"
"  Buffers: shared hit=16706 read=119604, temp read=988144 written=989380"
"  I/O Timings: read=12319.099"
"  ->  Sort  (cost=7781666.04..7809112.19 rows=10978461 width=230) (actual time=107721.784..110031.992 rows=10978455 loops=1)"
"        Sort Key: e.d_date"
"        Sort Method: external merge  Disk: 1401264kB"
"        Buffers: shared hit=16706 read=119604, temp read=988144 written=989380"
"        I/O Timings: read=12319.099"
"        ->  WindowAgg  (cost=1099956.42..2895526.84 rows=10978461 width=230) (actual time=22828.015..71429.798 rows=10978455 loops=1)"
"              Buffers: shared hit=16706 read=119604, temp read=314304 written=314759"
"              I/O Timings: read=12319.099"
"              ->  Gather Merge  (cost=1099956.42..2401496.10 rows=10978461 width=102) (actual time=22827.988..30292.781 rows=10978455 loops=1)"
"                    Workers Planned: 2"
"                    Workers Launched: 2"
"                    Buffers: shared hit=16706 read=119604, temp read=314304 written=314759"
"                    I/O Timings: read=12319.099"
"                    ->  Merge Left Join  (cost=1098956.40..1133309.27 rows=4574359 width=102) (actual time=22788.478..27128.013 rows=3659485 loops=3)"
"                          Merge Cond: ((e.symbol_id = s.symbol_id) AND (e.d_date = s.split_date))"
"                          Buffers: shared hit=16706 read=119604, temp read=314304 written=314759"
"                          I/O Timings: read=12319.099"
"                          ->  Sort  (cost=1097797.69..1109233.59 rows=4574359 width=70) (actual time=22761.664..25122.427 rows=3659485 loops=3)"
"                                Sort Key: e.symbol_id, e.d_date DESC"
"                                Sort Method: external merge  Disk: 326456kB"
"                                Buffers: shared hit=16291 read=119604, temp read=314304 written=314759"
"                                I/O Timings: read=12319.099"
"                                Worker 0:  Sort Method: external merge  Disk: 308344kB"
"                                Worker 1:  Sort Method: external merge  Disk: 323592kB"
"                                ->  Hash Join  (cost=401.10..216514.07 rows=4574359 width=70) (actual time=22.270..9968.309 rows=3659485 loops=3)"
"                                      Hash Cond: (e.symbol_id = symbols.id)"
"                                      Buffers: shared hit=16271 read=119604"
"                                      I/O Timings: read=12319.099"
"                                      ->  Parallel Append  (cost=0.00..204101.35 rows=4574363 width=57) (actual time=13.240..6830.104 rows=3659485 loops=3)"
"                                            Buffers: shared hit=15882 read=119604"
"                                            I/O Timings: read=12319.099"
"                                            ->  Seq Scan on eod_asx e_1  (cost=0.00..0.00 rows=1 width=194) (actual time=0.019..0.020 rows=0 loops=1)"
"                                            ->  Seq Scan on eod_jsx e_2  (cost=0.00..0.00 rows=1 width=194) (actual time=0.008..0.009 rows=0 loops=1)"
"                                            ->  Seq Scan on eod_lse e_3  (cost=0.00..0.00 rows=1 width=194) (actual time=0.012..0.012 rows=0 loops=1)"
"                                            ->  Seq Scan on eod_nse e_5  (cost=0.00..0.00 rows=1 width=194) (actual time=0.009..0.009 rows=0 loops=1)"
"                                            ->  Seq Scan on eod_sse e_7  (cost=0.00..0.00 rows=1 width=194) (actual time=0.007..0.008 rows=0 loops=1)"
"                                            ->  Seq Scan on eod_tsx e_8  (cost=0.00..0.00 rows=1 width=194) (actual time=0.020..0.020 rows=0 loops=1)"
"                                            ->  Parallel Seq Scan on eod_nyse e_6  (cost=0.00..160065.88 rows=4036588 width=57) (actual time=13.334..5903.960 rows=3229270 loops=3)"
"                                                  Buffers: shared hit=96 read=119604"
"                                                  I/O Timings: read=12319.099"
"                                            ->  Parallel Seq Scan on eod_nasdaq e_4  (cost=0.00..21163.69 rows=537769 width=59) (actual time=0.006..414.838 rows=645322 loops=2)"
"                                                  Buffers: shared hit=15786"
"                                      ->  Hash  (cost=238.82..238.82 rows=12982 width=21) (actual time=8.827..8.829 rows=13177 loops=3)"
"                                            Buckets: 16384  Batches: 1  Memory Usage: 806kB"
"                                            Buffers: shared hit=327"
"                                            ->  Seq Scan on symbols  (cost=0.00..238.82 rows=12982 width=21) (actual time=0.030..4.194 rows=13177 loops=3)"
"                                                  Buffers: shared hit=327"
"                          ->  Sort  (cost=1158.71..1168.76 rows=4023 width=44) (actual time=26.803..29.037 rows=4401 loops=3)"
"                                Sort Key: s.symbol_id, s.split_date DESC"
"                                Sort Method: quicksort  Memory: 538kB"
"                                Buffers: shared hit=415"
"                                Worker 0:  Sort Method: quicksort  Memory: 538kB"
"                                Worker 1:  Sort Method: quicksort  Memory: 538kB"
"                                ->  Subquery Scan on s  (cost=867.56..917.85 rows=4023 width=44) (actual time=21.322..23.472 rows=4417 loops=3)"
"                                      Buffers: shared hit=415"
"                                      ->  Sort  (cost=867.56..877.62 rows=4023 width=204) (actual time=21.318..21.950 rows=4417 loops=3)"
"                                            Sort Key: d.split_date"
"                                            Sort Method: quicksort  Memory: 538kB"
"                                            Buffers: shared hit=415"
"                                            Worker 0:  Sort Method: quicksort  Memory: 538kB"
"                                            Worker 1:  Sort Method: quicksort  Memory: 538kB"
"                                            ->  Hash Join  (cost=401.10..626.70 rows=4023 width=204) (actual time=7.932..16.146 rows=4417 loops=3)"
"                                                  Hash Cond: (d.overview_id = symbols_1.overview_id)"
"                                                  Buffers: shared hit=405"
"                                                  ->  Seq Scan on splits d  (cost=0.00..60.13 rows=3413 width=22) (actual time=0.042..0.602 rows=3413 loops=3)"
"                                                        Buffers: shared hit=78"
"                                                  ->  Hash  (cost=238.82..238.82 rows=12982 width=16) (actual time=7.812..7.813 rows=7427 loops=3)"
"                                                        Buckets: 16384  Batches: 1  Memory Usage: 477kB"
"                                                        Buffers: shared hit=327"
"                                                        ->  Seq Scan on symbols symbols_1  (cost=0.00..238.82 rows=12982 width=16) (actual time=0.013..3.125 rows=13177 loops=3)"
"                                                              Buffers: shared hit=327"
"Settings: effective_cache_size = '3053008kB'"
"Planning:"
"  Buffers: shared hit=41"
"Planning Time: 1.031 ms"
"Execution Time: 111890.061 ms"

Is there a more efficient method?

Improve this question
9
  • Where is the EXPLAIN (ANALYZE, BUFFERS, SETTINGS) output?
    – Laurenz Albe
    Nov 2, 2022 at 5:51
  • .. ON .. s.split_date = DATE(E.d_date) .. ??? E.d_date (by source - eod_data.d_date) column have DATE datatype, so addditional DATE() function usage is obviously excess - remove it. Then investigate EXPLAIN and check for suitable indices presenсe and usage.
    – Akina
    Nov 2, 2022 at 6:33
  • A Stock generally doesn't have a huge number of splits over its lifetime, so your appl.splits_view view presumably doesn't have a lot of rows for any given Stock. You may want to try calculating your window functions of the s.ratio in a separate CTE first then joining the results to your main query to multiply the E.high, E.low, and E.close.
    – J.D.
    Nov 2, 2022 at 13:29
  • I've removed the unnecessary Date() function
    – rossco
    Nov 3, 2022 at 1:37
  • I have added the Explain Plan output
    – rossco
    Nov 3, 2022 at 1:37

1 Answer 1

Reset to default
1
+100

As I mentioned in the comments, the fields that the 4 instances of your window functions are using, are the same key fields you're joining on between appl.eod_data (E) and appl.splits_view (s), which are E.symbol_id (aka s.symbol_id) and E.d_date (aka s.split_date). Knowing this we can substitute in the fields from s instead (since they are equal), like so:

sum(ln(s.ratio)) 
over 
(
    partition by s.symbol_id
    order by s.split_date desc
    rows between 
        unbounded preceding
        and 1 preceding
)

(Please don't mind how I reformatted the code.)

Now that the window function is only dependent on s, we can refactor it into it's own CTE so it only needs to be defined once (as opposed to 4 times):

WITH SplitRatioTotals AS
(
    SELECT
        s.symbol_id,
        s.split_date,
        sum(ln(s.ratio))
        over 
        (
            partition by s.symbol_id
            order by s.split_date desc
            rows between 
                unbounded preceding
                and 1 preceding
        ) AS ratio_total
    FROM appl.splits_view s
)

Then we just have to reference the CTE in place of each previous instance of your window function. Putting it all together like so:

CREATE OR REPLACE VIEW appl.eod_splits_view AS 

WITH SplitRatioTotals AS
(
    SELECT
        s.symbol_id,
        s.split_date,
        sum(ln(s.ratio))
        over 
        (
            partition by s.symbol_id
            order by s.split_date desc
            rows between 
                unbounded preceding
                and 1 preceding
        ) AS ratio_total
    FROM appl.splits_view s
)

SELECT appl.symbols.symbol, 
        appl.symbols.overview_id, 
        E.exchange, 
        to_char(E.d_date, 'YYYY-MM-DD') AS ta_date, 
  E.open AS unadj_open, 
  E.high AS unadj_high, 
  E.low AS unadj_low, 
  E.close AS unadj_close, 
  E.volume, 
  E.eod_id,
  E.d_date,
  coalesce(exp(s.ratio_total), 1) * E.open as open,
  coalesce(exp(s.ratio_total), 1) * E.high as high,
  coalesce(exp(s.ratio_total), 1) * E.low as low,
  coalesce(exp(s.ratio_total), 1) * E.close as close
FROM appl.eod_data E
INNER JOIN appl.symbols ON E.symbol_id = appl.symbols.id left join SplitRatioTotals s 
              on s.symbol_id = E.symbol_id 
             and s.split_date = E.d_date 
ORDER BY E.d_date;

This may not necessarily fix the root performance problem you're having but it might work around it, to get you to the finish line. It also is just cleaner code that'll be easier to maintain since it's not repeating the same window function code 4 times anymore.

You theoretically could even refactor the exp() function reference to inside of the CTE as well, but I wasn't sure if it would be faster to leave it out until the end so that it only really needs to calculate on only the ratio_totals that matched instead of all of them in the CTE (since the non-matching ones will be null and that should be faster to calculate on). I'll leave it for you to experiment with. 🙂

Improve this answer

Your Answer

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

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