2

I'm trying to incorporate the CTE into my final query. I can do it, but it slows it down a lot because I have to do the same sub query for CTE d over and over again (I think):

with  d AS(
    SELECT
        dd.company_id
      , dd.trade_date
      , dd.dates_id
      , dd.daily_val
    FROM
        daily_data dd
      , (
            SELECT DISTINCT
                company_id
            FROM
                daily_data d
            WHERE
                dates_id = 9590 
                AND company_id NOT IN
                (
                    SELECT DISTINCT
                        company_id
                    FROM
                        daily_data dd
                    WHERE
                        wh_calc_id = 368 
                        AND dd.dates_id = 9590

                )
        )   c
    WHERE
        wh_calc_id      = 344 
        AND trade_date >= ('2011-05-18'::date - interval '20 years') 
        AND c.company_id = dd.company_id
)

SELECT
    company_id
  , trade_date
  , pctl_calc
  , 368 
  , dates_id
FROM
    d d1
  , LATERAL
    (
        SELECT
            ROUND(percent_rank(d1.daily_val) WITHIN GROUP (ORDER BY d2.daily_val)::numeric , 6) AS pctl_calc
        FROM
            d d2
        WHERE
            d2.company_id     = d1.company_id
            AND d2.trade_date < d1.trade_date
            AND d1.dates_id = 9590 
    )
    x
WHERE
    9590 = d1.dates_id
;

Basically the query does a percent_rank for a value on a specified date (9590 is the id for '2011-05-18' in my dates table) relative to it's 20 year past. This query takes a long time to process and I have to do it once for every date, so speed is the main reason I am working on this again. Here is my daily_data table:

CREATE TABLE public.daily_data
(
    id integer NOT NULL DEFAULT nextval('daily_data_id_seq'::regclass),
    company_id integer NOT NULL,
    trade_date date NOT NULL,
    daily_val numeric,
    bbg_pulls_id integer,
    wh_calc_id integer,
    dates_id bigint NOT NULL,
    CONSTRAINT daily_data_pkey PRIMARY KEY (id),
    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_bbg_pulls_id_fkey FOREIGN KEY (bbg_pulls_id)
        REFERENCES public.bbg_pulls (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT daily_data_company_id_fkey FOREIGN KEY (company_id)
        REFERENCES public.company (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT daily_data_dates_id_fkey FOREIGN KEY (dates_id)
        REFERENCES public.dates (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT daily_data_dates_id_fkey1 FOREIGN KEY (trade_date, dates_id)
        REFERENCES public.dates (unique_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 public.wh_calc (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        DEFERRABLE,
    CONSTRAINT daily_data_check CHECK ((wh_calc_id IS NULL) <> (bbg_pulls_id IS NULL))
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public.daily_data
    OWNER to postgres;

GRANT ALL ON TABLE public.daily_data TO njenson;

GRANT ALL ON TABLE public.daily_data TO postgres;

-- Index: daily_data_bbg_pulls_id_idx

-- DROP INDEX public.daily_data_bbg_pulls_id_idx;

CREATE INDEX daily_data_bbg_pulls_id_idx
    ON public.daily_data USING btree
    (bbg_pulls_id)
    TABLESPACE pg_default    WHERE bbg_pulls_id IS NOT NULL
;

-- Index: daily_data_company_id_idx

-- DROP INDEX public.daily_data_company_id_idx;

CREATE INDEX daily_data_company_id_idx
    ON public.daily_data USING btree
    (company_id)
    TABLESPACE pg_default;

-- Index: daily_data_dates_id_idx

-- DROP INDEX public.daily_data_dates_id_idx;

CREATE INDEX daily_data_dates_id_idx
    ON public.daily_data USING btree
    (dates_id)
    TABLESPACE pg_default;

-- Index: daily_data_trade_date_idx

-- DROP INDEX public.daily_data_trade_date_idx;

CREATE INDEX daily_data_trade_date_idx
    ON public.daily_data USING btree
    (trade_date)
    TABLESPACE pg_default;

-- Index: daily_data_trade_date_idx1

-- DROP INDEX public.daily_data_trade_date_idx1;

CREATE INDEX daily_data_trade_date_idx1
    ON public.daily_data USING btree
    (trade_date)
    TABLESPACE pg_default    WHERE trade_date < trade_date
;

-- Index: daily_data_wh_calc_id_company_id_trade_date_idx

-- DROP INDEX public.daily_data_wh_calc_id_company_id_trade_date_idx;

CREATE INDEX daily_data_wh_calc_id_company_id_trade_date_idx
    ON public.daily_data USING btree
    (wh_calc_id, company_id, trade_date)
    TABLESPACE pg_default;

-- Index: daily_data_wh_calc_id_idx

-- DROP INDEX public.daily_data_wh_calc_id_idx;

CREATE INDEX daily_data_wh_calc_id_idx
    ON public.daily_data USING btree
    (wh_calc_id)
    TABLESPACE pg_default    WHERE wh_calc_id IS NOT NULL
;

Note that index daily_data_trade_date_idx1 isn't used for anything, but I am thinking it might be utilized if I can get the CTE in the query, but the main point it to speed things up however I can. The data looks like this:

id,company_id,trade_date,daily_val,bbg_pulls_id,wh_calc_id,dates_id
1,858,'2016-07-18','13.803000',34,,'11478'
2,858,'2016-07-15','13.806000',34,,'11475'
3,858,'2016-07-14','13.792000',34,,'11474'
4,858,'2016-07-13','13.789000',34,,'11473'
5,858,'2016-07-12','13.787000',34,,'11472'
6,858,'2016-07-11','13.787000',34,,'11471'
7,858,'2016-07-08','13.784000',34,,'11468'
8,858,'2016-07-07','13.782000',34,,'11467'
9,858,'2016-07-06','13.780000',34,,'11466'
10,858,'2016-07-05','13.780000',34,,'11465'
11,858,'2016-07-04','13.777000',34,,'11464'
12,858,'2016-07-01','13.774000',34,,'11461'
13,858,'2016-06-30','13.786000',34,,'11460'
14,858,'2016-06-29','13.784000',34,,'11459'
15,858,'2016-06-28','13.793000',34,,'11458'
16,858,'2016-06-27','13.791000',34,,'11457'
17,858,'2016-06-24','13.788000',34,,'11454'
18,858,'2016-06-23','13.787000',34,,'11453'

Where there are many of many all fields unlike what you see above. I know trade_date is not fully normalized (the dates_id refers to them in the dates table), but they are there because it is much less painful for me to write queries.

Here is the explain analyze of my query:

Nested Loop  (cost=2626418.09..5030481114.37 rows=31708 width=52) (actual time=10670.969..2153921.742 rows=1687 loops=1)
  CTE d
    ->  Hash Join  (cost=405853.13..2467850.24 rows=6341657 width=22) (actual time=1741.987..6786.627 rows=6568605 loops=1)
          Hash Cond: (dd_1.company_id = d.company_id)
          ->  Bitmap Heap Scan on daily_data dd_1  (cost=257733.11..2232036.20 rows=6473987 width=22) (actual time=1166.643..4002.505 rows=6920955 loops=1)
                Recheck Cond: (wh_calc_id = 344)
                Filter: (trade_date >= '1991-05-18 00:00:00'::timestamp without time zone)
                Rows Removed by Filter: 194
                Heap Blocks: exact=62451
                ->  Bitmap Index Scan on daily_data_wh_calc_id_idx  (cost=0.00..256114.61 rows=6527206 width=0) (actual time=1147.889..1147.889 rows=6921149 loops=1)
                      Index Cond: (wh_calc_id = 344)
          ->  Hash  (cost=148088.87..148088.87 rows=2492 width=4) (actual time=575.304..575.304 rows=2162 loops=1)
                Buckets: 4096  Batches: 1  Memory Usage: 109kB
                ->  Unique  (cost=148014.44..148063.95 rows=2492 width=4) (actual time=562.438..574.629 rows=2162 loops=1)
                      ->  Sort  (cost=148014.44..148039.20 rows=9902 width=4) (actual time=562.437..568.942 rows=30169 loops=1)
                            Sort Key: d.company_id
                            Sort Method: quicksort  Memory: 2183kB
                            ->  Bitmap Heap Scan on daily_data d  (cost=74315.85..147357.27 rows=9902 width=4) (actual time=469.644..542.737 rows=30169 loops=1)
                                  Recheck Cond: (dates_id = 9590)
                                  Filter: (NOT (hashed SubPlan 1))
                                  Heap Blocks: exact=29963
                                  ->  Bitmap Index Scan on daily_data_dates_id_idx  (cost=0.00..625.09 rows=19803 width=0) (actual time=15.008..15.008 rows=30169 loops=1)
                                        Index Cond: (dates_id = 9590)
                                  SubPlan 1
                                    ->  Unique  (cost=73685.18..73687.30 rows=391 width=4) (actual time=441.614..441.614 rows=0 loops=1)
                                          ->  Sort  (cost=73685.18..73686.24 rows=425 width=4) (actual time=441.613..441.613 rows=0 loops=1)
                                                Sort Key: dd.company_id
                                                Sort Method: quicksort  Memory: 25kB
                                                ->  Bitmap Heap Scan on daily_data dd  (cost=625.20..73666.62 rows=425 width=4) (actual time=441.586..441.586 rows=0 loops=1)
                                                      Recheck Cond: (dates_id = 9590)
                                                      Filter: (wh_calc_id = 368)
                                                      Rows Removed by Filter: 30169
                                                      Heap Blocks: exact=29963
                                                      ->  Bitmap Index Scan on daily_data_dates_id_idx  (cost=0.00..625.09 rows=19803 width=0) (actual time=12.422..12.422 rows=30169 loops=1)
                                                            Index Cond: (dates_id = 9590)
  ->  CTE Scan on d d1  (cost=0.00..142687.28 rows=31708 width=48) (actual time=1744.494..2805.162 rows=1687 loops=1)
        Filter: (9590 = dates_id)
        Rows Removed by Filter: 6566918
  ->  Aggregate  (cost=158567.85..158567.87 rows=1 width=32) (actual time=1275.106..1275.106 rows=1 loops=1687)
        ->  Result  (cost=0.00..158541.43 rows=10569 width=32) (actual time=653.133..1273.691 rows=2190 loops=1687)
              One-Time Filter: (d1.dates_id = 9590)
              ->  CTE Scan on d d2  (cost=0.00..158541.43 rows=10569 width=32) (actual time=653.129..1273.311 rows=2190 loops=1687)
                    Filter: ((trade_date < d1.trade_date) AND (company_id = d1.company_id))
                    Rows Removed by Filter: 6566415
Planning time: 3.471 ms
Execution time: 2153971.288 ms

PS - I should add that in all cases the query being tested is the only query going at the time. The DB is otherwise completely quiet.

EDIT 1: removed "on (company_id)" twice to simplify the question per comment. I am using Postgres 10 (now).

put on hold as off-topic by mustaccio, hot2use, Marcello Miorelli, Laurenz Albe, John Eisbrener Sep 16 at 12:27

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – mustaccio, hot2use, Marcello Miorelli, John Eisbrener
If this question can be reworded to fit the rules in the help center, please edit the question.

  • We need the explain analyze – Evan Carroll Sep 5 '17 at 19:52
  • @EvanCarroll - I am running one now with indiri's latest query suggestion, then I'll run another after adding his index suggestions. I will add them to my OQ when finished. – mountainclimber Sep 5 '17 at 20:07
  • added explain analyze of my original query. more on indiri's query later... – mountainclimber Sep 6 '17 at 15:08
  • @EvanCarroll - I think you have everything now. It looks like my original query is still best. – mountainclimber Sep 6 '17 at 16:48
  • The SELECT DISTINCT ON (company_id) company_id FROM construct - used twice - seems overly complex for what it does. You can replace it with the simpler SELECT DISTINCT – ypercubeᵀᴹ Nov 12 '17 at 16:59
0
  1. From my experience, using with is not the best option for big tables and can influence the performance.
  2. On the first look, your query within WITH is unnecessarily complicated with so many subqueries. Can you use join with conditions or use partition over by?
  3. In your case probably you don't need WITH subquery. You repeat the same table so many time that is very difficult understand your assumptions. Try to write your query using join condition and group by instead of subqueries and WITH clause.
  4. I'm pretty sure that index below return 0 elements.

    CREATE INDEX daily_data_trade_date_idx1 ON public.daily_data USING btree (trade_date) TABLESPACE pg_default WHERE trade_date < trade_date ;

  • Thank you for trying to help. My responses: 1) That is part of the reason I am asking the question. 2) I don't know. That was the only way I could sort out how to do it. Can you provide a solution? 3) I have tried. I haven't come up with a way to pull it off. Do you have a solution? 4) Agreed. The thing that takes so much time is within the LATERAL. So my idea was to put the WITH within the final query and create and leverage some index to make the entire thing faster. If you can provide guidance on how to do that, that would be helpful. – mountainclimber Nov 13 '17 at 14:34

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