5

I'm looking to speed up some calculations on a single table.

Here is the table, which I believe has over 93 million rows and it grows every day:

CREATE TABLE daily_data
(
  id serial NOT NULL,
  company_id integer NOT NULL,
  trade_date date NOT NULL,
  daily_val numeric NOT NULL,
  bbg_pulls_id integer,
  gen_qtr_end_dt_id integer,
  ern_release_date_id integer,
  wh_calc_id integer,
  CONSTRAINT daily_data_pkey PRIMARY KEY (id),
  CONSTRAINT daily_data_bbg_pulls_id_fkey FOREIGN KEY (bbg_pulls_id)
      REFERENCES bbg_pulls (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT daily_data_company_id_fkey FOREIGN KEY (company_id)
      REFERENCES company (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT daily_data_ern_release_date_id_fkey FOREIGN KEY (ern_release_date_id)
      REFERENCES ern_dt (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT daily_data_wh_calc_id_fkey FOREIGN KEY (wh_calc_id)
      REFERENCES wh_calc (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT daily_data_company_id_trade_date_bbg_pulls_id_key UNIQUE (company_id, trade_date, bbg_pulls_id),
  CONSTRAINT daily_data_company_id_trade_date_wh_calc_id_key UNIQUE (company_id, trade_date, wh_calc_id),
  CONSTRAINT daily_data_check CHECK ((wh_calc_id IS NULL) <> (bbg_pulls_id IS NULL))
)

CREATE INDEX daily_data_bbg_pulls_id_idx
  ON daily_data
  USING btree
  (bbg_pulls_id)
  WHERE bbg_pulls_id IS NOT NULL;

CREATE INDEX daily_data_company_id_idx
  ON daily_data
  USING btree
  (company_id);

CREATE INDEX daily_data_gen_qtr_end_dt_id_idx
  ON daily_data
  USING btree
  (gen_qtr_end_dt_id)
  WHERE gen_qtr_end_dt_id IS NOT NULL;

CREATE INDEX daily_data_trade_date_idx
  ON daily_data
  USING btree
  (trade_date);

CREATE INDEX daily_data_wh_calc_id_idx
  ON daily_data
  USING btree
  (wh_calc_id)
  WHERE wh_calc_id IS NOT NULL;

Here is what I actually/ultimately want to do:

with dd2 as (select * from daily_data where wh_calc_id = 241 -- <- the 241 value is passed into a function where this is used
) 
INSERT INTO daily_data (
        company_id
        ,trade_date
        ,daily_val
        ,wh_calc_id
        )
SELECT  d.company_id
    ,d.trade_date
    , round(CASE WHEN x.ct = 0 THEN numeric '1'  
                  ELSE x.ct_lt / x.ct END, 6) AS pctl_calc
        ,1 -- <-- dummy value, value is passed into the function where this query is used
FROM   dd2 d, LATERAL (
   SELECT count(daily_val) AS ct
        , count(daily_val < d.daily_val OR NULL)::numeric As ct_lt
   FROM   dd2
   WHERE  company_id = d.company_id
 --   and     company_id < 8
   AND    trade_date < d.trade_date
   and    wh_calc_id = d.wh_calc_id
   -- and     wh_calc_id = 241
   ) x

I am testing it by doing this (basically everything except the insert), but it runs for 16 hours before I cancel it:

with dd2 as (select * from daily_data where wh_calc_id = 241 
) 
SELECT d.*
     , round(CASE WHEN x.ct = 0 THEN numeric '1'  
                  ELSE x.ct_lt / x.ct END, 6) AS pctl_calc
FROM   dd2 d, LATERAL (
   SELECT count(daily_val) AS ct
        , count(daily_val < d.daily_val OR NULL)::numeric As ct_lt
   FROM   dd2
   WHERE  company_id = d.company_id
 --   and     company_id < 8
   AND    trade_date < d.trade_date
   and    wh_calc_id = d.wh_calc_id
   -- and     wh_calc_id = 241

   ) x
ORDER  BY company_id, trade_date;

So I run a subset (AND company_id < 8) to get an explain analyze, which takes less than 3 minutes: explain analyze

with dd2 as (select * from daily_data where wh_calc_id = 241 AND company_id < 8 
) 
SELECT d.*
     , round(CASE WHEN x.ct = 0 THEN numeric '1'  
                  ELSE x.ct_lt / x.ct END, 6) AS pctl_calc
FROM   dd2 d, LATERAL (
   SELECT count(daily_val) AS ct
        , count(daily_val < d.daily_val OR NULL)::numeric As ct_lt
   FROM   dd2
   WHERE  company_id = d.company_id
   --   and       company_id < 8
   AND    trade_date < d.trade_date
   and    wh_calc_id = d.wh_calc_id
   -- and     wh_calc_id = 241

   ) x
ORDER  BY company_id, trade_date;

Here is the explain analyze output:

"Sort  (cost=8.56..8.57 rows=1 width=100) (actual time=219363.049..219367.217 rows=24444 loops=1)"
"  Sort Key: d.company_id, d.trade_date"
"  Sort Method: external merge  Disk: 1264kB"
"  CTE dd2"
"    ->  Index Scan using daily_data_wh_calc_id_idx on daily_data  (cost=0.43..8.46 rows=1 width=34) (actual time=0.415..70805.295 rows=24444 loops=1)"
"          Index Cond: (wh_calc_id = 241)"
"          Filter: (company_id < 8)"
"          Rows Removed by Filter: 8661143"
"  ->  Nested Loop  (cost=0.04..0.10 rows=1 width=100) (actual time=70835.311..219272.273 rows=24444 loops=1)"
"        ->  CTE Scan on dd2 d  (cost=0.00..0.02 rows=1 width=60) (actual time=0.423..64.374 rows=24444 loops=1)"
"        ->  Aggregate  (cost=0.04..0.05 rows=1 width=32) (actual time=8.965..8.965 rows=1 loops=24444)"
"              ->  CTE Scan on dd2  (cost=0.00..0.03 rows=1 width=32) (actual time=5.201..8.203 rows=2422 loops=24444)"
"                    Filter: ((trade_date < d.trade_date) AND (company_id = d.company_id) AND (wh_calc_id = d.wh_calc_id))"
"                    Rows Removed by Filter: 22022"
"Total runtime: 219374.219 ms"

Note I use the CTE and to get things to run faster. I was getting memory errors and/or things would run forever even with the subsetted test query. Ultimately all of this will occur in a function, so the values that are hardcoded here are actually passed into the function, but I don't think that is relevant for the question.

For more about that specific query/calculation go here: Percentile rank that takes sorted argument (or same functionality) in PostgreSQL 9.3.5

I went here to try and figure it out myself to no avail (supplied for someone in my shoes): https://explain.depesz.com/

I'm not seeing where I could add an index to make this go faster.

Question 1: what can I do to speed this query up? I'm just focused on the select part of the insert right know, but if you have ideas on how to speed up the insert (other than removing indexes) I would be happy to hear them.

Question 2: Is there something I can do from a server resource perspective to get calculations/queries to run faster?

SELECT version() "PostgreSQL 9.3.5 on x86_64-suse-linux-gnu, compiled by gcc (SUSE Linux) 4.8.3 20140627 [gcc-4_8-branch revision 212064], 64-bit"

Memory 2048 MB, CPU: 2 vCPU, Provisioned Storage: 202.11 GB

I know that I can remove some of the indexes that are not used in the query to speed up the insert. I will do that later if need be. For now I just want to get the select part to run faster. The other indexes are used for other important queries.

  • Found the tag wiki. Updating OQ. – mountainclimber Feb 23 '17 at 15:53
  • 2
    Edit the question and add the \d+ daily_data output. – ypercubeᵀᴹ Feb 23 '17 at 16:07
  • 1
    I meant either the CREATE TABLE with indexes (that you added, nice!) or the \d+ output (which provides the same info. You can remove the \d+ output, it's not helpful anyway as an image. – ypercubeᵀᴹ Feb 23 '17 at 19:35
  • 1
    Please change work_mem at least to 8Mb ("Sort Method: external merge Disk: 1264kB") – Roman Tkachuk Feb 23 '17 at 23:47
  • 2
    "Note I use the CTE and to get things to run faster" a CTE does not necessarily improve performance: modern-sql.com/feature/with/performance Also: can you upgrade to a more recent Postgres version? Performance of aggregates has improved 9.5. and 9.6 Increase work_mem would probably – a_horse_with_no_name Feb 27 '17 at 23:46
2

To testing I created small sample table:

INSERT INTO daily_data (
        company_id
        ,trade_date
        ,daily_val
        ,wh_calc_id
        )
SELECT
        (random() * 10)::int,
        '2010-01-01'::timestamp + (i::text || ' day')::interval,
        random() * 100,
        (random() * 20) + 230
FROM
        generate_series(1, 200000) AS i
;

Original query:

EXPLAIN ANALYZE
with dd2 as (select * from daily_data where wh_calc_id = 241 AND company_id < 8 
) 
SELECT d.*
, ct, ct_lt
     , round(CASE WHEN x.ct = 0 THEN numeric '1'  
                  ELSE x.ct_lt / x.ct END, 6) AS pctl_calc
FROM   dd2 d, LATERAL (
   SELECT count(daily_val) AS ct
        , count(daily_val < d.daily_val OR NULL)::numeric As ct_lt
   FROM   dd2
   WHERE  company_id = d.company_id
   --   and       company_id < 8
   AND    trade_date < d.trade_date
   and    wh_calc_id = d.wh_calc_id
   -- and     wh_calc_id = 241

   ) x
ORDER  BY company_id, trade_date;

Subquery version:

with dd2 as (
        select company_id, trade_date
   , (SELECT count(daily_val) FROM daily_data WHERE company_id = d.company_id AND trade_date < d.trade_date and wh_calc_id = d.wh_calc_id) AS ct
   , (SELECT count(daily_val < d.daily_val OR NULL)::numeric FROM daily_data WHERE company_id = d.company_id AND trade_date < d.trade_date and wh_calc_id = d.wh_calc_id) AS ct_lt
        from daily_data AS d where wh_calc_id = 241 AND company_id < 8 
)
SELECT d.*
     , round(CASE WHEN d.ct = 0 THEN numeric '1'  
                  ELSE d.ct_lt / d.ct END, 6) AS pctl_calc
FROM   dd2 d
ORDER  BY company_id, trade_date;

Partial window + partial subquery version:#

EXPLAIN ANALYZE
with dd2 as (select *,
        count(daily_val) OVER (PARTITION BY company_id, wh_calc_id ORDER BY trade_date) - 1 AS ct,
        (SELECT count(daily_val < d.daily_val OR NULL)::numeric FROM daily_data WHERE company_id = d.company_id AND trade_date < d.trade_date and wh_calc_id = d.wh_calc_id) AS ct_lt
        from daily_data AS d where wh_calc_id = 241 AND company_id < 8 
) 
SELECT d.*
     , round(CASE WHEN ct = 0 THEN numeric '1'  
                  ELSE ct_lt / ct END, 6) AS pctl_calc
FROM   dd2 d
ORDER  BY company_id, trade_date;

Pure window version (but some tricky):

EXPLAIN ANALYZE
with dd2 as (select d.company_id, d.trade_date, d.daily_val,
        count(daily_val) OVER w - 1 AS ct,
        array_agg(daily_val) OVER w AS ct_lt2
        from daily_data AS d
        where wh_calc_id = 241 AND company_id < 8 
        WINDOW w AS (PARTITION BY company_id, wh_calc_id ORDER BY trade_date)
) , dd1 AS (  
SELECT d.*
, (SELECT count(*) FILTER (WHERE u < d.daily_val) FROM unnest(ct_lt2) AS u) AS ct_lt

FROM   dd2 d
) SELECT *
     , round(CASE WHEN ct = 0 THEN numeric '1'  
                  ELSE ct_lt / ct END, 6) AS pctl_calc
FROM dd1 AS d
ORDER  BY company_id, trade_date;

Original query:

                                                                   QUERY PLAN                                                                    
-------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=2653.28..2653.84 rows=226 width=132) (actual time=8399.345..8399.886 rows=7540 loops=1)
   Sort Key: d.company_id, d.trade_date
   Sort Method: quicksort  Memory: 1253kB
   CTE dd2
     ->  Bitmap Heap Scan on daily_data  (cost=21.55..1224.03 rows=226 width=60) (actual time=1.026..4.794 rows=7540 loops=1)
           Recheck Cond: (wh_calc_id = 241)
           Filter: (company_id < 8)
           Rows Removed by Filter: 2526
           Heap Blocks: exact=1470
           ->  Bitmap Index Scan on daily_data_wh_calc_id_idx  (cost=0.00..21.50 rows=677 width=0) (actual time=0.848..0.848 rows=10066 loops=1)
                 Index Cond: (wh_calc_id = 241)
   ->  Nested Loop  (cost=6.22..1420.41 rows=226 width=132) (actual time=6.196..8391.961 rows=7540 loops=1)
         ->  CTE Scan on dd2 d  (cost=0.00..4.52 rows=226 width=60) (actual time=1.029..1.933 rows=7540 loops=1)
         ->  Aggregate  (cost=6.22..6.24 rows=1 width=40) (actual time=1.111..1.111 rows=1 loops=7540)
               ->  CTE Scan on dd2  (cost=0.00..6.22 rows=1 width=32) (actual time=0.003..0.995 rows=484 loops=7540)
                     Filter: ((trade_date < d.trade_date) AND (company_id = d.company_id) AND (wh_calc_id = d.wh_calc_id))
                     Rows Removed by Filter: 7056
 Planning time: 0.345 ms
 Execution time: 8400.233 ms
(19 rows)

Subquery version:

                                                                                              QUERY PLAN                                                                                              
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=9949.69..9950.25 rows=226 width=80) (actual time=10558.505..10559.017 rows=7540 loops=1)
   Sort Key: d.company_id, d.trade_date
   Sort Method: quicksort  Memory: 782kB
   CTE dd2
     ->  Bitmap Heap Scan on daily_data d_1  (cost=21.55..9934.07 rows=226 width=48) (actual time=1.138..10538.523 rows=7540 loops=1)
           Recheck Cond: (wh_calc_id = 241)
           Filter: (company_id < 8)
           Rows Removed by Filter: 2526
           Heap Blocks: exact=1470
           ->  Bitmap Index Scan on daily_data_wh_calc_id_idx  (cost=0.00..21.50 rows=677 width=0) (actual time=0.945..0.945 rows=10066 loops=1)
                 Index Cond: (wh_calc_id = 241)
           SubPlan 1
             ->  Aggregate  (cost=19.26..19.27 rows=1 width=8) (actual time=0.722..0.722 rows=1 loops=7540)
                   ->  Index Scan using daily_data_company_id_trade_date_wh_calc_id_key on daily_data  (cost=0.42..19.26 rows=1 width=32) (actual time=0.007..0.671 rows=484 loops=7540)
                         Index Cond: ((company_id = d_1.company_id) AND (trade_date < d_1.trade_date) AND (wh_calc_id = d_1.wh_calc_id))
           SubPlan 2
             ->  Aggregate  (cost=19.26..19.27 rows=1 width=32) (actual time=0.672..0.672 rows=1 loops=7540)
                   ->  Index Scan using daily_data_company_id_trade_date_wh_calc_id_key on daily_data daily_data_1  (cost=0.42..19.26 rows=1 width=32) (actual time=0.006..0.551 rows=484 loops=7540)
                         Index Cond: ((company_id = d_1.company_id) AND (trade_date < d_1.trade_date) AND (wh_calc_id = d_1.wh_calc_id))
   ->  CTE Scan on dd2 d  (cost=0.00..6.78 rows=226 width=80) (actual time=1.142..10552.746 rows=7540 loops=1)
 Planning time: 0.248 ms
 Execution time: 10559.387 ms
(22 rows)

Partial window + partial subquery version:

                                                                                       QUERY PLAN                                                                                        
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=5609.72..5610.28 rows=226 width=132) (actual time=4931.963..4932.433 rows=7540 loops=1)
   Sort Key: d.company_id, d.trade_date
   Sort Method: quicksort  Memory: 1253kB
   CTE dd2
     ->  WindowAgg  (cost=1232.87..5594.10 rows=226 width=100) (actual time=9.498..4914.892 rows=7540 loops=1)
           ->  Sort  (cost=1232.87..1233.43 rows=226 width=60) (actual time=9.468..11.291 rows=7540 loops=1)
                 Sort Key: d_1.company_id, d_1.trade_date
                 Sort Method: quicksort  Memory: 782kB
                 ->  Bitmap Heap Scan on daily_data d_1  (cost=21.55..1224.03 rows=226 width=60) (actual time=1.002..5.251 rows=7540 loops=1)
                       Recheck Cond: (wh_calc_id = 241)
                       Filter: (company_id < 8)
                       Rows Removed by Filter: 2526
                       Heap Blocks: exact=1470
                       ->  Bitmap Index Scan on daily_data_wh_calc_id_idx  (cost=0.00..21.50 rows=677 width=0) (actual time=0.814..0.814 rows=10066 loops=1)
                             Index Cond: (wh_calc_id = 241)
           SubPlan 1
             ->  Aggregate  (cost=19.26..19.27 rows=1 width=32) (actual time=0.649..0.649 rows=1 loops=7540)
                   ->  Index Scan using daily_data_company_id_trade_date_wh_calc_id_key on daily_data  (cost=0.42..19.26 rows=1 width=32) (actual time=0.005..0.529 rows=484 loops=7540)
                         Index Cond: ((company_id = d_1.company_id) AND (trade_date < d_1.trade_date) AND (wh_calc_id = d_1.wh_calc_id))
   ->  CTE Scan on dd2 d  (cost=0.00..6.78 rows=226 width=132) (actual time=9.504..4928.264 rows=7540 loops=1)
 Planning time: 0.223 ms
 Execution time: 4932.854 ms
(22 rows)

Pure window version (but some tricky):

                                                                         QUERY PLAN                                                                          
-------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=1601.04..1601.61 rows=226 width=120) (actual time=2006.537..2018.331 rows=7540 loops=1)
   Sort Key: d.company_id, d.trade_date
   Sort Method: quicksort  Memory: 63526kB
   CTE dd2
     ->  WindowAgg  (cost=1232.87..1239.08 rows=226 width=84) (actual time=8.298..86.164 rows=7540 loops=1)
           ->  Sort  (cost=1232.87..1233.43 rows=226 width=44) (actual time=8.285..9.752 rows=7540 loops=1)
                 Sort Key: d_1.company_id, d_1.trade_date
                 Sort Method: quicksort  Memory: 782kB
                 ->  Bitmap Heap Scan on daily_data d_1  (cost=21.55..1224.03 rows=226 width=44) (actual time=0.935..5.078 rows=7540 loops=1)
                       Recheck Cond: (wh_calc_id = 241)
                       Filter: (company_id < 8)
                       Rows Removed by Filter: 2526
                       Heap Blocks: exact=1470
                       ->  Bitmap Index Scan on daily_data_wh_calc_id_idx  (cost=0.00..21.50 rows=677 width=0) (actual time=0.722..0.722 rows=10066 loops=1)
                             Index Cond: (wh_calc_id = 241)
   CTE dd1
     ->  CTE Scan on dd2 d_2  (cost=0.00..346.34 rows=226 width=88) (actual time=8.315..1573.920 rows=7540 loops=1)
           SubPlan 2
             ->  Aggregate  (cost=1.50..1.51 rows=1 width=8) (actual time=0.155..0.156 rows=1 loops=7540)
                   ->  Function Scan on unnest u  (cost=0.00..1.00 rows=100 width=32) (actual time=0.034..0.058 rows=485 loops=7540)
   ->  CTE Scan on dd1 d  (cost=0.00..6.78 rows=226 width=120) (actual time=8.321..1774.450 rows=7540 loops=1)
 Planning time: 0.177 ms
 Execution time: 2443.855 ms
(23 rows)

Conclusion

Windows version is the best. I found no easy way to convert ct_lt calculations for it. As a workaround I used the aggregation of values in the array and count ct_lt from it. Probably, it can create some penalty for large amounts of data. But even with that, this version will be better than others.

UPD:

Adapting for 9.4:

EXPLAIN ANALYZE
with dd2 as (select d.company_id, d.trade_date, d.daily_val,
        count(daily_val) OVER w - 1 AS ct,
        array_agg(daily_val) OVER w AS ct_lt2
        from daily_data AS d
        where wh_calc_id = 241 AND company_id < 8 
        WINDOW w AS (PARTITION BY company_id, wh_calc_id ORDER BY trade_date)
) , dd1 AS (  
SELECT d.*
, (SELECT count(*) FROM unnest(ct_lt2) AS u WHERE u < d.daily_val) AS ct_lt

FROM   dd2 d
) SELECT *
     , round(CASE WHEN ct = 0 THEN numeric '1'  
                  ELSE ct_lt::numeric / ct END, 6) AS pctl_calc
FROM dd1 AS d
ORDER  BY company_id, trade_date;

Index:

CREATE INDEX daily_data_i1_idx
  ON daily_data
  (wh_calc_id, company_id, trade_date);
                                                                     QUERY PLAN                                                                     
----------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=1311.98..1312.82 rows=333 width=120) (actual time=1597.558..1613.481 rows=7407 loops=1)
   Sort Key: d.company_id, d.trade_date
   Sort Method: quicksort  Memory: 61659kB
   CTE dd2
     ->  WindowAgg  (cost=824.34..833.50 rows=333 width=84) (actual time=10.416..87.668 rows=7407 loops=1)
           ->  Sort  (cost=824.34..825.17 rows=333 width=44) (actual time=10.381..11.851 rows=7407 loops=1)
                 Sort Key: d_1.company_id, d_1.trade_date
                 Sort Method: quicksort  Memory: 771kB
                 ->  Bitmap Heap Scan on daily_data d_1  (cost=11.83..810.39 rows=333 width=44) (actual time=2.934..6.548 rows=7407 loops=1)
                       Recheck Cond: ((wh_calc_id = 241) AND (company_id < 8))
                       Heap Blocks: exact=1460
                       ->  Bitmap Index Scan on daily_data_i1_idx  (cost=0.00..11.75 rows=333 width=0) (actual time=2.661..2.661 rows=7407 loops=1)
                             Index Cond: ((wh_calc_id = 241) AND (company_id < 8))
   CTE dd1
     ->  CTE Scan on dd2 d_2  (cost=0.00..454.55 rows=333 width=88) (actual time=10.442..1320.450 rows=7407 loops=1)
           SubPlan 2
             ->  Aggregate  (cost=1.33..1.34 rows=1 width=8) (actual time=0.143..0.143 rows=1 loops=7407)
                   ->  Function Scan on unnest u  (cost=0.00..1.25 rows=33 width=0) (actual time=0.036..0.124 rows=234 loops=7407)
                         Filter: (u < d_2.daily_val)
                         Rows Removed by Filter: 243
   ->  CTE Scan on dd1 d  (cost=0.00..9.99 rows=333 width=120) (actual time=10.450..1468.533 rows=7407 loops=1)
 Planning time: 0.471 ms
 Execution time: 1960.835 ms
  • I just tried to run the pure windows version and got this: ERROR: syntax error at or near "(" LINE 10: , (SELECT count(*) FILTER (WHERE u < d.daily_val) FROM unnes... – mountainclimber Feb 24 '17 at 14:50
  • 1
    For 9.3 you can use: (SELECT count(*) FROM unnest(ct_lt2) AS u WHERE u < d.daily_val) – Roman Tkachuk Feb 24 '17 at 14:51
  • CREATE INDEX daily_data_i1_idx ON daily_data (wh_calc_id, company_id) ; – Roman Tkachuk Feb 24 '17 at 16:01
  • maybe i put that line in wrong, but the calc isn't working. All 1s and 0s: (SELECT count(*) FROM unnest(ct_lt2) AS u WHERE u < d.daily_val) AS ct_lt – mountainclimber Feb 24 '17 at 16:10
  • 1
    Sorry, Need to cast to numeric – Roman Tkachuk Feb 28 '17 at 15:00
0

This was the fastest way I could get it to work with 93 million plus records while still avoiding memory issues. Recommendations and suggestions welcome. It took 4 hours to run without sub-setting (without AND company_id < 8) the data.

I'm providing this answer to see if someone has any suggestions and as an alternative way to answer the ultimate question I had, including inserting, whilst avoiding memory limit issues/errors.

Basically I modified Roman Tkachuk's "UPD" answer so it uses a temp table, updates that temp table, then inserts the final calculation into my daily_data table.

This was the fastest way I could find to perform the calculation and avoid the memory issues I was having at the same time. Why did it work? I'm not sure, but I think less in-memory data usage?

create temp table tmp_dd2 as
    select 
        d.company_id
        , d.trade_date
        , d.daily_val
        ,wh_calc_id
        ,count(daily_val) OVER w - 1 AS ct
        ,array_agg(daily_val) OVER w AS ct_lt2 
        ,null::numeric as ct_lt 
        ,null::numeric pctl_calc 
    from daily_data AS d
    where wh_calc_id = i_prctl_wh_calc_id -- AND company_id < 8
    WINDOW w AS (PARTITION BY company_id, wh_calc_id ORDER BY trade_date);

    update tmp_dd2 set
        ct_lt = (SELECT count(*) FROM unnest(ct_lt2) AS u WHERE u < daily_val);

    INSERT INTO daily_data (
        company_id
        ,trade_date
        ,daily_val
        ,wh_calc_id
        )
    SELECT  company_id
        , trade_date
        , round((CASE WHEN ct = 0 THEN numeric '1'  
              ELSE ct_lt::numeric / ct END), 6) AS pctl_calc
        ,1
    FROM tmp_dd2;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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