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.
Best Answer
To testing I created small sample table:
Original query:
Subquery version:
Partial window + partial subquery version:#
Pure window version (but some tricky):
Original query:
Subquery version:
Partial window + partial subquery version:
Pure window version (but some tricky):
Conclusion
Windows version is the best. I found no easy way to convert
ct_ltcalculations for it. As a workaround I used the aggregation of values in the array and countct_ltfrom 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:
Index: