Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table historicalclient(
  id int generated by default as identity primary key,
  history_id int,
  history_date date);
insert into historicalclient values 
  (101,201,'yesterday'),
  (102,202,'yesterday'::date-interval'11 months -2 days'),
  (103,203,'yesterday'::date-interval'3 years')
returning *;

create table contactposition(
  id int generated by default as identity primary key,
  current boolean,
  history_id int,
  contact_id int,
  --Cont_ID int,
  client_id int,
  --historydate date,
  history_date date
);
insert into contactposition values
  (301,true,201,401,101,'yesterday'),
  (302,true,202,402,102,'yesterday'::date-interval'11 months -2 days'),
  (303,true,203,403,103,'yesterday'::date-interval'3 years')
returning *;
  
create table historicalcontact(
  id int generated by default as identity primary key
);
insert into historicalcontact values
  (401),
  (402),
  (403)
returning *;

CREATE TABLE




id
history_id
history_date


101
201
2024-01-23

102
202
2023-02-25

103
203
2021-01-23



INSERT 0 3



CREATE TABLE




id
current
history_id
contact_id
client_id
history_date


301
t
201
401
101
2024-01-23

302
t
202
402
102
2023-02-25

303
t
203
403
103
2021-01-23



INSERT 0 3



CREATE TABLE




id


401

402

403



INSERT 0 3


prepare query_after_corrections as
WITH Q AS (
    SELECT  Q_Start::DATE, 
            (Q_Start + '3 months -1 day')::DATE Q_End, 
            Q_Rank
    FROM    GENERATE_SERIES(DATE_TRUNC('quarter',CURRENT_DATE),
                            DATE_TRUNC('year',   CURRENT_DATE) - INTERVAL '1 year', 
                            INTERVAL '-3 months') WITH ORDINALITY g(Q_Start, Q_Rank)
    ),
HA AS (-- HA gathers the historic client records within the timeframe of Q 
       -- and filters to the last change per quarter.
    SELECT DISTINCT ON (ha.id, 
                        EXTRACT(year FROM history_date),
                        EXTRACT(quarter FROM history_date) )
           ha.id        AS CL_ID, 
           history_id   AS CL_Hist_ID, 
           history_date AS CL_Hist_Date
    FROM historicalclient ha
    WHERE EXTRACT(year FROM history_date) >= (EXTRACT(year FROM CURRENT_DATE) - 1)
    ORDER BY ha.id, 
             EXTRACT(year FROM history_date),
             EXTRACT(quarter FROM history_date),
             history_date DESC
    ),
HAQ AS (-- Tag clients to relevant quarters 
    SELECT * FROM Q
    ,LATERAL(SELECT CL_ID, 
                    MAX(CL_Hist_ID) CL_Hist_ID, 
                    MAX(CL_Hist_Date) CL_Hist_Date 
             FROM HA 
             WHERE CL_Hist_Date::DATE <= Q.Q_End 
             GROUP BY CL_ID) HA
),
HCP AS (-- Same deal as HA but for contactposition.
    SELECT DISTINCT ON (hcp.id, 
                        EXTRACT(year FROM history_date), 
                        EXTRACT(quarter FROM history_date)) 
            hcp.id AS CP_ID, 
            client_id AS CP_CL_ID, 
            contact_id AS CP_Cont_ID, 
            history_id AS CP_Hist_ID, 
            history_date AS CP_Hist_Date, 
            current 
    FROM contactposition hcp
    WHERE history_date >= '10/1/2023'
    AND current IS TRUE
    ),
HC AS (-- HC is HCP but for contacts
    SELECT DISTINCT ON (hc.id, 
                    EXTRACT(year FROM history_date), 
                    EXTRACT(quarter FROM history_date)) 
       hc.id AS Cont_ID, 
       history_id AS Con_Hist_ID, 
       history_date AS Con_Hist_Date
    FROM historicalcontact hc
    LEFT JOIN contactposition cp
    ON hc.id = cp.contact_id
    WHERE EXTRACT(year FROM history_date) >= (EXTRACT(year FROM CURRENT_DATE) - 1)
    ORDER BY hc.id, 
             EXTRACT(year FROM history_date), 
             EXTRACT(quarter FROM history_date),
             history_date DESC
)
-- Joining all of the base data sources together
SELECT DISTINCT *
FROM HAQ, HCP, HC 
WHERE HCP.CP_CL_ID = HAQ.CL_ID 
  AND HCP.CP_Hist_Date::DATE <= HAQ.Q_End
  AND HC.Cont_ID = HCP.CP_Cont_ID 
  AND HC.Con_Hist_Date::DATE <= HAQ.Q_End
;
execute query_after_corrections;
explain analyze verbose execute query_after_corrections;

PREPARE




q_start
q_end
q_rank
cl_id
cl_hist_id
cl_hist_date
cp_id
cp_cl_id
cp_cont_id
cp_hist_id
cp_hist_date
current
cont_id
con_hist_id
con_hist_date


2023-01-01
2023-03-31
5
102
202
2023-02-25
302
102
402
202
2023-02-25
t
402
202
2023-02-25

2023-07-01
2023-09-30
3
102
202
2023-02-25
302
102
402
202
2023-02-25
t
402
202
2023-02-25

2023-10-01
2023-12-31
2
102
202
2023-02-25
302
102
402
202
2023-02-25
t
402
202
2023-02-25

2023-04-01
2023-06-30
4
102
202
2023-02-25
302
102
402
202
2023-02-25
t
402
202
2023-02-25

2024-01-01
2024-03-31
1
101
201
2024-01-23
301
101
401
201
2024-01-23
t
401
201
2024-01-23

2024-01-01
2024-03-31
1
102
202
2023-02-25
302
102
402
202
2023-02-25
t
402
202
2023-02-25



SELECT 6




QUERY PLAN


HashAggregate  (cost=18144.21..19261.15 rows=63825 width=61) (actual time=0.268..0.379 rows=6 loops=1)

  Output: ((g.q_start)::date), (((g.q_start + '3 mons -1 days'::interval))::date), g.q_rank, ha.cl_id, (max(ha.cl_hist_id)), (max(ha.cl_hist_date)), hcp.id, hcp.client_id, hcp.contact_id, hcp.history_id, hcp.history_date, hcp.current, hc.cont_id, hc.con_hist_id, hc.con_hist_date

  Group Key: (g.q_start)::date, ((g.q_start + '3 mons -1 days'::interval))::date, g.q_rank, ha.cl_id, (max(ha.cl_hist_id)), (max(ha.cl_hist_date)), hcp.id, hcp.contact_id, hcp.history_id, hcp.history_date, hcp.current, hc.con_hist_id, hc.con_hist_date

  Batches: 1  Memory Usage: 1569kB

  ->  Hash Join  (cost=320.57..16069.90 rows=63825 width=61) (actual time=0.092..0.192 rows=6 loops=1)

        Output: (g.q_start)::date, ((g.q_start + '3 mons -1 days'::interval))::date, g.q_rank, ha.cl_id, (max(ha.cl_hist_id)), (max(ha.cl_hist_date)), hcp.id, hcp.client_id, hcp.contact_id, hcp.history_id, hcp.history_date, hcp.current, hc.cont_id, hc.con_hist_id, hc.con_hist_date

        Hash Cond: (hcp.contact_id = hc.cont_id)

        Join Filter: (hc.con_hist_date <= ((g.q_start + '3 mons -1 days'::interval))::date)

        ->  Nested Loop  (cost=158.46..7144.21 rows=69250 width=49) (actual time=0.048..0.140 rows=6 loops=1)

              Output: g.q_start, g.q_rank, ha.cl_id, (max(ha.cl_hist_id)), (max(ha.cl_hist_date)), hcp.id, hcp.client_id, hcp.contact_id, hcp.history_id, hcp.history_date, hcp.current

              ->  Function Scan on pg_catalog.generate_series g  (cost=0.02..10.02 rows=1000 width=16) (actual time=0.010..0.011 rows=5 loops=1)

                    Output: g.q_start, g.q_rank

                    Function Call: generate_series(date_trunc('quarter'::text, (CURRENT_DATE)::timestamp with time zone), (date_trunc('year'::text, (CURRENT_DATE)::timestamp with time zone) - '1 year'::interval), '-3 mons'::interval)

              ->  Hash Join  (cost=158.44..164.73 rows=69 width=33) (actual time=0.022..0.023 rows=1 loops=5)

                    Output: ha.cl_id, (max(ha.cl_hist_id)), (max(ha.cl_hist_date)), hcp.id, hcp.client_id, hcp.contact_id, hcp.history_id, hcp.history_date, hcp.current

                    Inner Unique: true

                    Hash Cond: (hcp.client_id = ha.cl_id)

                    Join Filter: (hcp.history_date <= ((g.q_start + '3 mons -1 days'::interval))::date)

                    ->  Unique  (cost=43.37..46.14 rows=277 width=85) (actual time=0.005..0.006 rows=2 loops=5)

                          Output: hcp.id, hcp.client_id, hcp.contact_id, hcp.history_id, hcp.history_date, hcp.current, (EXTRACT(year FROM hcp.history_date)), (EXTRACT(quarter FROM hcp.history_date))

                          ->  Sort  (cost=43.37..44.07 rows=277 width=85) (actual time=0.005..0.005 rows=2 loops=5)

                                Output: hcp.id, hcp.client_id, hcp.contact_id, hcp.history_id, hcp.history_date, hcp.current, (EXTRACT(year FROM hcp.history_date)), (EXTRACT(quarter FROM hcp.history_date))

                                Sort Key: hcp.id, (EXTRACT(year FROM hcp.history_date)), (EXTRACT(quarter FROM hcp.history_date))

                                Sort Method: quicksort  Memory: 25kB

                                ->  Seq Scan on public.contactposition hcp  (cost=0.00..32.13 rows=277 width=85) (actual time=0.002..0.003 rows=2 loops=5)

                                      Output: hcp.id, hcp.client_id, hcp.contact_id, hcp.history_id, hcp.history_date, hcp.current, EXTRACT(year FROM hcp.history_date), EXTRACT(quarter FROM hcp.history_date)

                                      Filter: ((hcp.current IS TRUE) AND (hcp.history_date >= '2023-01-10'::date))

                                      Rows Removed by Filter: 1

                    ->  Hash  (cost=113.19..113.19 rows=150 width=12) (actual time=0.013..0.013 rows=1 loops=5)

                          Output: ha.cl_id, (max(ha.cl_hist_id)), (max(ha.cl_hist_date))

                          Buckets: 1024  Batches: 1  Memory Usage: 9kB

                          ->  GroupAggregate  (cost=91.29..113.19 rows=150 width=12) (actual time=0.012..0.012 rows=1 loops=5)

                                Output: ha.cl_id, max(ha.cl_hist_id), max(ha.cl_hist_date)

                                Group Key: ha.cl_id

                                ->  Subquery Scan on ha  (cost=91.29..109.99 rows=227 width=12) (actual time=0.009..0.009 rows=1 loops=5)

                                      Output: ha.cl_id, ha.cl_hist_id, ha.cl_hist_date, (EXTRACT(year FROM ha_1.history_date)), (EXTRACT(quarter FROM ha_1.history_date))

                                      Filter: (ha.cl_hist_date <= ((g.q_start + '3 mons -1 days'::interval))::date)

                                      Rows Removed by Filter: 1

                                      ->  Unique  (cost=91.29..98.09 rows=680 width=76) (actual time=0.007..0.008 rows=2 loops=5)

                                            Output: ha_1.id, ha_1.history_id, ha_1.history_date, (EXTRACT(year FROM ha_1.history_date)), (EXTRACT(quarter FROM ha_1.history_date))

                                            ->  Sort  (cost=91.29..92.99 rows=680 width=76) (actual time=0.007..0.007 rows=2 loops=5)

                                                  Output: ha_1.id, ha_1.history_id, ha_1.history_date, (EXTRACT(year FROM ha_1.history_date)), (EXTRACT(quarter FROM ha_1.history_date))

                                                  Sort Key: ha_1.id, (EXTRACT(year FROM ha_1.history_date)), (EXTRACT(quarter FROM ha_1.history_date)), ha_1.history_date DESC

                                                  Sort Method: quicksort  Memory: 25kB

                                                  ->  Seq Scan on public.historicalclient ha_1  (cost=0.00..59.30 rows=680 width=76) (actual time=0.003..0.005 rows=2 loops=5)

                                                        Output: ha_1.id, ha_1.history_id, ha_1.history_date, EXTRACT(year FROM ha_1.history_date), EXTRACT(quarter FROM ha_1.history_date)

                                                        Filter: (EXTRACT(year FROM ha_1.history_date) >= (EXTRACT(year FROM CURRENT_DATE) - '1'::numeric))

                                                        Rows Removed by Filter: 1

        ->  Hash  (cost=155.19..155.19 rows=553 width=12) (actual time=0.038..0.040 rows=2 loops=1)

              Output: hc.cont_id, hc.con_hist_id, hc.con_hist_date

              Buckets: 1024  Batches: 1  Memory Usage: 9kB

              ->  Subquery Scan on hc  (cost=144.13..155.19 rows=553 width=12) (actual time=0.036..0.038 rows=2 loops=1)

                    Output: hc.cont_id, hc.con_hist_id, hc.con_hist_date

                    ->  Unique  (cost=144.13..149.66 rows=553 width=76) (actual time=0.035..0.037 rows=2 loops=1)

                          Output: hc_1.id, cp.history_id, cp.history_date, (EXTRACT(year FROM cp.history_date)), (EXTRACT(quarter FROM cp.history_date))

                          ->  Sort  (cost=144.13..145.52 rows=553 width=76) (actual time=0.035..0.035 rows=2 loops=1)

                                Output: hc_1.id, cp.history_id, cp.history_date, (EXTRACT(year FROM cp.history_date)), (EXTRACT(quarter FROM cp.history_date))

                                Sort Key: hc_1.id, (EXTRACT(year FROM cp.history_date)), (EXTRACT(quarter FROM cp.history_date)), cp.history_date DESC

                                Sort Method: quicksort  Memory: 25kB

                                ->  Hash Join  (cost=67.38..118.94 rows=553 width=76) (actual time=0.027..0.030 rows=2 loops=1)

                                      Output: hc_1.id, cp.history_id, cp.history_date, EXTRACT(year FROM cp.history_date), EXTRACT(quarter FROM cp.history_date)

                                      Inner Unique: true

                                      Hash Cond: (cp.contact_id = hc_1.id)

                                      ->  Seq Scan on public.contactposition cp  (cost=0.00..47.35 rows=553 width=12) (actual time=0.008..0.009 rows=2 loops=1)

                                            Output: cp.id, cp.current, cp.history_id, cp.contact_id, cp.client_id, cp.history_date

                                            Filter: (EXTRACT(year FROM cp.history_date) >= (EXTRACT(year FROM CURRENT_DATE) - '1'::numeric))

                                            Rows Removed by Filter: 1

                                      ->  Hash  (cost=35.50..35.50 rows=2550 width=4) (actual time=0.005..0.006 rows=3 loops=1)

                                            Output: hc_1.id

                                            Buckets: 4096  Batches: 1  Memory Usage: 33kB

                                            ->  Seq Scan on public.historicalcontact hc_1  (cost=0.00..35.50 rows=2550 width=4) (actual time=0.002..0.002 rows=3 loops=1)

                                                  Output: hc_1.id

Planning Time: 0.002 ms

Execution Time: 1.377 ms



EXPLAIN


prepare original_query as 
-- Q creates a baseline of quarters to analyze the client and contact tables over time. Last full year plus current year.

WITH Q AS (
    SELECT Q_Start, ((Q_Start + INTERVAL '3 months') - INTERVAL '1 day') :: DATE Q_End, RANK() OVER (ORDER BY Q_Start DESC) Q_Rank
    FROM (
        SELECT GENERATE_SERIES( DATE_TRUNC('year', CURRENT_DATE) - INTERVAL '1 year', DATE_TRUNC('quarter', CURRENT_DATE), INTERVAL '3 months') :: DATE Q_Start
        ) q1
    ),

-- HA gathers the historic client records within the timeframe of Q and filters to the last change per quarter.

HA AS (
    SELECT id CL_ID, history_id CL_Hist_ID, history_date CL_Hist_Date FROM (
        SELECT ha.id, history_id, history_date, ROW_NUMBER() OVER (PARTITION BY ha.id, EXTRACT(year FROM history_date),
               EXTRACT(quarter FROM history_date) ORDER BY history_date DESC) AS Row_Rank
        FROM historicalclient ha
        WHERE EXTRACT(year FROM history_date) >= (EXTRACT(year FROM CURRENT_DATE) - 1)
        ) ha1
    WHERE Row_Rank = 1
    ),

-- Tag clients to relevant quarters 

HAQ AS (
    SELECT *
    FROM Q
    JOIN LATERAL (SELECT CL_ID, MAX(CL_Hist_ID) CL_Hist_ID, MAX(CL_Hist_Date) CL_Hist_Date FROM HA WHERE CL_Hist_Date :: DATE <= Q.Q_End GROUP BY CL_ID) HA
    ON TRUE
),

-- Same deal as HA but for contactposition.

HCP AS (
    SELECT CP_ID, client_id CP_CL_ID, contact_id CP_Cont_ID, history_id CP_Hist_ID, history_date CP_Hist_Date, current FROM (
        SELECT hcp.id CP_ID, client_id, contact_id, history_id, history_date, current,
               ROW_NUMBER() OVER (PARTITION BY hcp.id, EXTRACT(year FROM history_date), EXTRACT(quarter FROM history_date) ORDER BY history_date DESC) AS Row_Rank
        FROM contactposition hcp
        WHERE history_date >= '10/1/2023'
        ) hcp1
    WHERE Row_Rank = 1
    AND current = TRUE
    ),

-- HC is HCP but for contacts

HC AS (
    SELECT id Cont_ID, history_id Con_Hist_ID, history_date Con_Hist_Date FROM (
        SELECT hc.id, history_id, history_date, ROW_NUMBER() OVER (PARTITION BY hc.id, EXTRACT(year FROM history_date), EXTRACT(quarter FROM history_date) ORDER BY history_date DESC) AS Row_Rank
        FROM historicalcontact hc
        LEFT JOIN contactposition cp
        ON hc.id = cp.contact_id
        WHERE EXTRACT(year FROM history_date) >= (EXTRACT(year FROM CURRENT_DATE) - 1)
        ) hc1
    WHERE Row_Rank = 1
),

-- Joining all of the base data sources together

HACPCQ AS (
    SELECT DISTINCT *
    FROM HAQ
    LEFT JOIN LATERAL (SELECT CP_CL_ID, CP_ID, CP_Cont_ID, MAX(CP_Hist_ID) CP_Hist_ID, MAX(CP_Hist_Date) CP_Hist_Date FROM HCP WHERE CP_CL_ID = HAQ.CL_ID AND CP_Hist_Date :: DATE <= HAQ.Q_End GROUP BY CP_CL_ID, CP_ID, CP_Cont_ID) HCP
    ON TRUE
    LEFT JOIN LATERAL (SELECT Cont_ID, MAX(Con_Hist_ID) Con_Hist_ID, MAX(Con_Hist_Date) Con_Hist_Date FROM HC WHERE Cont_ID = HCP.CP_Cont_ID AND Con_Hist_Date :: DATE <= HAQ.Q_End GROUP BY Cont_ID) HC
    ON TRUE
)


    SELECT *
    FROM HACPCQ a
;
execute original_query;
explain analyze verbose execute original_query;

PREPARE




q_start
q_end
q_rank
cl_id
cl_hist_id
cl_hist_date
cp_cl_id
cp_id
cp_cont_id
cp_hist_id
cp_hist_date
cont_id
con_hist_id
con_hist_date


2023-01-01
2023-03-31
5
102
202
2023-02-25
102
302
402
202
2023-02-25
402
202
2023-02-25

2023-04-01
2023-06-30
4
102
202
2023-02-25
102
302
402
202
2023-02-25
402
202
2023-02-25

2023-07-01
2023-09-30
3
102
202
2023-02-25
102
302
402
202
2023-02-25
402
202
2023-02-25

2023-10-01
2023-12-31
2
102
202
2023-02-25
102
302
402
202
2023-02-25
402
202
2023-02-25

2024-01-01
2024-03-31
1
101
201
2024-01-23
101
301
401
201
2024-01-23
401
201
2024-01-23

2024-01-01
2024-03-31
1
102
202
2023-02-25
102
302
402
202
2023-02-25
402
202
2023-02-25



SELECT 6




QUERY PLAN


Unique  (cost=267052.69..267090.19 rows=200 width=60) (actual time=44.771..44.787 rows=6 loops=1)

  Output: (((generate_series((date_trunc('year'::text, (CURRENT_DATE)::timestamp with time zone) - '1 year'::interval), date_trunc('quarter'::text, (CURRENT_DATE)::timestamp with time zone), '3 mons'::interval)))::date), (((((((generate_series((date_trunc('year'::text, (CURRENT_DATE)::timestamp with time zone) - '1 year'::interval), date_trunc('quarter'::text, (CURRENT_DATE)::timestamp with time zone), '3 mons'::interval)))::date) + '3 mons'::interval) - '1 day'::interval))::date), (rank() OVER (?)), ha1.id, (max(ha1.history_id)), (max(ha1.history_date)), hcp1.client_id, hcp1.cp_id, hcp1.contact_id, (max(hcp1.history_id)), (max(hcp1.history_date)), hc1.id, (max(hc1.history_id)), (max(hc1.history_date))

  ->  Sort  (cost=267052.69..267055.19 rows=1000 width=60) (actual time=44.770..44.775 rows=6 loops=1)

        Output: (((generate_series((date_trunc('year'::text, (CURRENT_DATE)::timestamp with time zone) - '1 year'::interval), date_trunc('quarter'::text, (CURRENT_DATE)::timestamp with time zone), '3 mons'::interval)))::date), (((((((generate_series((date_trunc('year'::text, (CURRENT_DATE)::timestamp with time zone) - '1 year'::interval), date_trunc('quarter'::text, (CURRENT_DATE)::timestamp with time zone), '3 mons'::interval)))::date) + '3 mons'::interval) - '1 day'::interval))::date), (rank() OVER (?)), ha1.id, (max(ha1.history_id)), (max(ha1.history_date)), hcp1.client_id, hcp1.cp_id, hcp1.contact_id, (max(hcp1.history_id)), (max(hcp1.history_date)), hc1.id, (max(hc1.history_id)), (max(hc1.history_date))

        Sort Key: (((generate_series((date_trunc('year'::text, (CURRENT_DATE)::timestamp with time zone) - '1 year'::interval), date_trunc('quarter'::text, (CURRENT_DATE)::timestamp with time zone), '3 mons'::interval)))::date), (((((((generate_series((date_trunc('year'::text, (CURRENT_DATE)::timestamp with time zone) - '1 year'::interval), date_trunc('quarter'::text, (CURRENT_DATE)::timestamp with time zone), '3 mons'::interval)))::date) + '3 mons'::interval) - '1 day'::interval))::date), (rank() OVER (?)), ha1.id, (max(ha1.history_id)), (max(ha1.history_date)), hcp1.client_id, hcp1.cp_id, hcp1.contact_id, (max(hcp1.history_id)), (max(hcp1.history_date)), hc1.id, (max(hc1.history_id)), (max(hc1.history_date))

        Sort Method: quicksort  Memory: 25kB

        ->  Nested Loop Left Join  (cost=323.38..267002.86 rows=1000 width=60) (actual time=44.537..44.763 rows=6 loops=1)

              Output: (((generate_series((date_trunc('year'::text, (CURRENT_DATE)::timestamp with time zone) - '1 year'::interval), date_trunc('quarter'::text, (CURRENT_DATE)::timestamp with time zone), '3 mons'::interval)))::date), (((((((generate_series((date_trunc('year'::text, (CURRENT_DATE)::timestamp with time zone) - '1 year'::interval), date_trunc('quarter'::text, (CURRENT_DATE)::timestamp with time zone), '3 mons'::interval)))::date) + '3 mons'::interval) - '1 day'::interval))::date), (rank() OVER (?)), ha1.id, (max(ha1.history_id)), (max(ha1.history_date)), hcp1.client_id, hcp1.cp_id, hcp1.contact_id, (max(hcp1.history_id)), (max(hcp1.history_date)), hc1.id, (max(hc1.history_id)), (max(hc1.history_date))

              ->  Nested Loop Left Join  (cost=263.64..207101.58 rows=1000 width=48) (actual time=44.471..44.619 rows=6 loops=1)

                    Output: (((generate_series((date_trunc('year'::text, (CURRENT_DATE)::timestamp with time zone) - '1 year'::interval), date_trunc('quarter'::text, (CURRENT_DATE)::timestamp with time zone), '3 mons'::interval)))::date), (((((((generate_series((date_trunc('year'::text, (CURRENT_DATE)::timestamp with time zone) - '1 year'::interval), date_trunc('quarter'::text, (CURRENT_DATE)::timestamp with time zone), '3 mons'::interval)))::date) + '3 mons'::interval) - '1 day'::interval))::date), (rank() OVER (?)), ha1.id, (max(ha1.history_id)), (max(ha1.history_date)), hcp1.client_id, hcp1.cp_id, hcp1.contact_id, (max(hcp1.history_id)), (max(hcp1.history_date))

                    ->  Nested Loop  (cost=178.66..122031.79 rows=1000 width=28) (actual time=44.385..44.464 rows=6 loops=1)

                          Output: (((generate_series((date_trunc('year'::text, (CURRENT_DATE)::timestamp with time zone) - '1 year'::interval), date_trunc('quarter'::text, (CURRENT_DATE)::timestamp with time zone), '3 mons'::interval)))::date), (((((((generate_series((date_trunc('year'::text, (CURRENT_DATE)::timestamp with time zone) - '1 year'::interval), date_trunc('quarter'::text, (CURRENT_DATE)::timestamp with time zone), '3 mons'::interval)))::date) + '3 mons'::interval) - '1 day'::interval))::date), (rank() OVER (?)), ha1.id, (max(ha1.history_id)), (max(ha1.history_date))

                          ->  WindowAgg  (cost=87.36..112.36 rows=1000 width=16) (actual time=44.299..44.316 rows=5 loops=1)

                                Output: (((generate_series((date_trunc('year'::text, (CURRENT_DATE)::timestamp with time zone) - '1 year'::interval), date_trunc('quarter'::text, (CURRENT_DATE)::timestamp with time zone), '3 mons'::interval)))::date), ((((((generate_series((date_trunc('year'::text, (CURRENT_DATE)::timestamp with time zone) - '1 year'::interval), date_trunc('quarter'::text, (CURRENT_DATE)::timestamp with time zone), '3 mons'::interval)))::date) + '3 mons'::interval) - '1 day'::interval))::date, rank() OVER (?)

                                ->  Sort  (cost=87.36..89.86 rows=1000 width=4) (actual time=44.286..44.288 rows=5 loops=1)

                                      Output: (((generate_series((date_trunc('year'::text, (CURRENT_DATE)::timestamp with time zone) - '1 year'::interval), date_trunc('quarter'::text, (CURRENT_DATE)::timestamp with time zone), '3 mons'::interval)))::date)

                                      Sort Key: (((generate_series((date_trunc('year'::text, (CURRENT_DATE)::timestamp with time zone) - '1 year'::interval), date_trunc('quarter'::text, (CURRENT_DATE)::timestamp with time zone), '3 mons'::interval)))::date) DESC

                                      Sort Method: quicksort  Memory: 25kB

                                      ->  Result  (cost=0.00..37.53 rows=1000 width=4) (actual time=44.277..44.282 rows=5 loops=1)

                                            Output: ((generate_series((date_trunc('year'::text, (CURRENT_DATE)::timestamp with time zone) - '1 year'::interval), date_trunc('quarter'::text, (CURRENT_DATE)::timestamp with time zone), '3 mons'::interval)))::date

                                            ->  ProjectSet  (cost=0.00..5.04 rows=1000 width=8) (actual time=44.272..44.276 rows=5 loops=1)

                                                  Output: generate_series((date_trunc('year'::text, (CURRENT_DATE)::timestamp with time zone) - '1 year'::interval), date_trunc('quarter'::text, (CURRENT_DATE)::timestamp with time zone), '3 mons'::interval)

                                                  ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=44.231..44.231 rows=1 loops=1)

                          ->  GroupAggregate  (cost=91.29..121.91 rows=1 width=12) (actual time=0.027..0.027 rows=1 loops=5)

                                Output: ha1.id, max(ha1.history_id), max(ha1.history_date)

                                Group Key: ha1.id

                                ->  Subquery Scan on ha1  (cost=91.29..121.89 rows=1 width=12) (actual time=0.019..0.021 rows=1 loops=5)

                                      Output: ha1.id, ha1.history_id, ha1.history_date, ha1.row_rank, (EXTRACT(year FROM ha.history_date)), (EXTRACT(quarter FROM ha.history_date))

                                      Filter: ((ha1.history_date <= (((((((generate_series((date_trunc('year'::text, (CURRENT_DATE)::timestamp with time zone) - '1 year'::interval), date_trunc('quarter'::text, (CURRENT_DATE)::timestamp with time zone), '3 mons'::interval)))::date) + '3 mons'::interval) - '1 day'::interval))::date)) AND (ha1.row_rank = 1))

                                      Rows Removed by Filter: 1

                                      ->  WindowAgg  (cost=91.29..111.69 rows=680 width=84) (actual time=0.017..0.020 rows=2 loops=5)

                                            Output: ha.id, ha.history_id, ha.history_date, row_number() OVER (?), (EXTRACT(year FROM ha.history_date)), (EXTRACT(quarter FROM ha.history_date))

                                            Run Condition: (row_number() OVER (?) <= 1)

                                            ->  Sort  (cost=91.29..92.99 rows=680 width=76) (actual time=0.015..0.015 rows=2 loops=5)

                                                  Output: ha.id, ha.history_date, (EXTRACT(year FROM ha.history_date)), (EXTRACT(quarter FROM ha.history_date)), ha.history_id

                                                  Sort Key: ha.id, (EXTRACT(year FROM ha.history_date)), (EXTRACT(quarter FROM ha.history_date)), ha.history_date DESC

                                                  Sort Method: quicksort  Memory: 25kB

                                                  ->  Seq Scan on public.historicalclient ha  (cost=0.00..59.30 rows=680 width=76) (actual time=0.008..0.010 rows=2 loops=5)

                                                        Output: ha.id, ha.history_date, EXTRACT(year FROM ha.history_date), EXTRACT(quarter FROM ha.history_date), ha.history_id

                                                        Filter: (EXTRACT(year FROM ha.history_date) >= (EXTRACT(year FROM CURRENT_DATE) - '1'::numeric))

                                                        Rows Removed by Filter: 1

                    ->  GroupAggregate  (cost=84.98..85.06 rows=1 width=20) (actual time=0.024..0.024 rows=1 loops=6)

                          Output: hcp1.client_id, hcp1.cp_id, hcp1.contact_id, max(hcp1.history_id), max(hcp1.history_date)

                          Group Key: hcp1.cp_id, hcp1.contact_id

                          ->  Incremental Sort  (cost=84.98..85.03 rows=2 width=20) (actual time=0.021..0.021 rows=1 loops=6)

                                Output: hcp1.cp_id, hcp1.contact_id, hcp1.client_id, hcp1.history_id, hcp1.history_date

                                Sort Key: hcp1.cp_id, hcp1.contact_id

                                Presorted Key: hcp1.cp_id

                                Full-sort Groups: 6  Sort Method: quicksort  Average Memory: 25kB  Peak Memory: 25kB

                                ->  Subquery Scan on hcp1  (cost=58.71..84.97 rows=1 width=20) (actual time=0.014..0.015 rows=1 loops=6)

                                      Output: hcp1.cp_id, hcp1.contact_id, hcp1.client_id, hcp1.history_id, hcp1.history_date

                                      Filter: (hcp1.current AND (hcp1.history_date <= (((((((generate_series((date_trunc('year'::text, (CURRENT_DATE)::timestamp with time zone) - '1 year'::interval), date_trunc('quarter'::text, (CURRENT_DATE)::timestamp with time zone), '3 mons'::interval)))::date) + '3 mons'::interval) - '1 day'::interval))::date)) AND (hcp1.row_rank = 1) AND (hcp1.client_id = ha1.id))

                                      Rows Removed by Filter: 1

                                      ->  WindowAgg  (cost=58.71..75.30 rows=553 width=93) (actual time=0.011..0.014 rows=2 loops=6)

                                            Output: hcp.id, hcp.client_id, hcp.contact_id, hcp.history_id, hcp.history_date, hcp.current, row_number() OVER (?), (EXTRACT(year FROM hcp.history_date)), (EXTRACT(quarter FROM hcp.history_date))

                                            Run Condition: (row_number() OVER (?) <= 1)

                                            ->  Sort  (cost=58.71..60.09 rows=553 width=85) (actual time=0.009..0.009 rows=2 loops=6)

                                                  Output: hcp.id, hcp.history_date, (EXTRACT(year FROM hcp.history_date)), (EXTRACT(quarter FROM hcp.history_date)), hcp.client_id, hcp.contact_id, hcp.history_id, hcp.current

                                                  Sort Key: hcp.id, (EXTRACT(year FROM hcp.history_date)), (EXTRACT(quarter FROM hcp.history_date)), hcp.history_date DESC

                                                  Sort Method: quicksort  Memory: 25kB

                                                  ->  Seq Scan on public.contactposition hcp  (cost=0.00..33.52 rows=553 width=85) (actual time=0.004..0.005 rows=2 loops=6)

                                                        Output: hcp.id, hcp.history_date, EXTRACT(year FROM hcp.history_date), EXTRACT(quarter FROM hcp.history_date), hcp.client_id, hcp.contact_id, hcp.history_id, hcp.current

                                                        Filter: (hcp.history_date >= '2023-01-10'::date)

                                                        Rows Removed by Filter: 1

              ->  GroupAggregate  (cost=59.74..59.89 rows=1 width=12) (actual time=0.022..0.023 rows=1 loops=6)

                    Output: hc1.id, max(hc1.history_id), max(hc1.history_date)

                    ->  Subquery Scan on hc1  (cost=59.74..59.88 rows=1 width=12) (actual time=0.019..0.020 rows=1 loops=6)

                          Output: hc1.id, hc1.history_id, hc1.history_date, hc1.row_rank, (EXTRACT(year FROM cp.history_date)), (EXTRACT(quarter FROM cp.history_date))

                          Filter: ((hc1.history_date <= (((((((generate_series((date_trunc('year'::text, (CURRENT_DATE)::timestamp with time zone) - '1 year'::interval), date_trunc('quarter'::text, (CURRENT_DATE)::timestamp with time zone), '3 mons'::interval)))::date) + '3 mons'::interval) - '1 day'::interval))::date)) AND (hc1.row_rank = 1))

                          ->  WindowAgg  (cost=59.74..59.83 rows=3 width=84) (actual time=0.018..0.019 rows=1 loops=6)

                                Output: hc.id, cp.history_id, cp.history_date, row_number() OVER (?), (EXTRACT(year FROM cp.history_date)), (EXTRACT(quarter FROM cp.history_date))

                                Run Condition: (row_number() OVER (?) <= 1)

                                ->  Sort  (cost=59.74..59.75 rows=3 width=76) (actual time=0.015..0.015 rows=1 loops=6)

                                      Output: hc.id, cp.history_date, (EXTRACT(year FROM cp.history_date)), (EXTRACT(quarter FROM cp.history_date)), cp.history_id

                                      Sort Key: (EXTRACT(year FROM cp.history_date)), (EXTRACT(quarter FROM cp.history_date)), cp.history_date DESC

                                      Sort Method: quicksort  Memory: 25kB

                                      ->  Nested Loop  (cost=0.15..59.72 rows=3 width=76) (actual time=0.011..0.013 rows=1 loops=6)

                                            Output: hc.id, cp.history_date, EXTRACT(year FROM cp.history_date), EXTRACT(quarter FROM cp.history_date), cp.history_id

                                            ->  Index Only Scan using historicalcontact_pkey on public.historicalcontact hc  (cost=0.15..8.17 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=6)

                                                  Output: hc.id

                                                  Index Cond: (hc.id = hcp1.contact_id)

                                                  Heap Fetches: 6

                                            ->  Seq Scan on public.contactposition cp  (cost=0.00..51.50 rows=3 width=12) (actual time=0.003..0.004 rows=1 loops=6)

                                                  Output: cp.id, cp.current, cp.history_id, cp.contact_id, cp.client_id, cp.history_date

                                                  Filter: ((cp.contact_id = hcp1.contact_id) AND (EXTRACT(year FROM cp.history_date) >= (EXTRACT(year FROM CURRENT_DATE) - '1'::numeric)))

                                                  Rows Removed by Filter: 2

Planning Time: 0.003 ms

JIT:

  Functions: 55

  Options: Inlining false, Optimization false, Expressions true, Deforming true

  Timing: Generation 3.803 ms, Inlining 0.000 ms, Optimization 1.167 ms, Emission 43.168 ms, Total 48.138 ms

Execution Time: 48.836 ms



EXPLAIN
id	current	history_id	contact_id	client_id	history_date
301	t	201	401	101	2024-01-23
302	t	202	402	102	2023-02-25
303	t	203	403	103	2021-01-23
q_start	q_end	q_rank	cl_id	cl_hist_id	cl_hist_date	cp_id	cp_cl_id	cp_cont_id	cp_hist_id	cp_hist_date	current	cont_id	con_hist_id	con_hist_date
2023-01-01	2023-03-31	5	102	202	2023-02-25	302	102	402	202	2023-02-25	t	402	202	2023-02-25
2023-07-01	2023-09-30	3	102	202	2023-02-25	302	102	402	202	2023-02-25	t	402	202	2023-02-25
2023-10-01	2023-12-31	2	102	202	2023-02-25	302	102	402	202	2023-02-25	t	402	202	2023-02-25
2023-04-01	2023-06-30	4	102	202	2023-02-25	302	102	402	202	2023-02-25	t	402	202	2023-02-25
2024-01-01	2024-03-31	1	101	201	2024-01-23	301	101	401	201	2024-01-23	t	401	201	2024-01-23
2024-01-01	2024-03-31	1	102	202	2023-02-25	302	102	402	202	2023-02-25	t	402	202	2023-02-25