#	exclusive	inclusive	rows x	rows	loops	node
1.	598.869	18,226.728	↓ 1.4	193,369	1	→ WindowAgg (cost=899,925.99..901,199.54 rows=141,506 width=2,152) (actual time=17,588.960..18,226.728 rows=193,369 loops=1)
2.						CTE ffm_applications_filtered
3.	0.000	1,521.448	↓ 147.6	285,735	1	→ Gather (cost=1,000.00..290,088.49 rows=1,936 width=2,199) (actual time=1.937..1,521.448 rows=285,735 loops=1) Workers Planned: 1 Workers Launched: 1
4.	1,969.812	1,969.812	↓ 125.4	142,868	2 / 2	→ Parallel Seq Scan on ffm_applications (cost=0.00..288,894.89 rows=1,139 width=2,199) (actual time=0.171..1,969.812 rows=142,868 loops=2) Filter: (((status)::text = 'submitted'::text) AND (plan_year = 2,020) AND (timezone('pst'::text, timezone('utc'::text, initial_submitted_at)) >= '2019-11-01 00:00:00'::timestamp without time zone) AND (timezone('pst'::text, timezone('utc'::text, initial_submitted_at)) <= '2019-11-23 00:00:00'::timestamp without time zone)) Rows Removed by Filter: 1,119,684
5.						CTE issuer_groups_filtered
6.	0.177	0.177	↓ 1.1	15	1	→ Seq Scan on issuer_groups (cost=0.00..5.56 rows=14 width=60) (actual time=0.053..0.177 rows=15 loops=1) Filter: ((state)::text = 'FL'::text) Rows Removed by Filter: 428
7.						CTE insurance_full_plans_filtered
8.	6.603	6.603	↑ 1.0	872	1	→ Seq Scan on insurance_full_plans (cost=0.00..557.83 rows=872 width=242) (actual time=0.245..6.603 rows=872 loops=1) Filter: ((state)::text = 'FL'::text) Rows Removed by Filter: 15,936
9.						CTE managed_applications_filtered
10.	0.000	2,647.056	↓ 2.4	340,928	1	→ Gather (cost=1,000.09..513,371.55 rows=142,873 width=7,746) (actual time=2.457..2,647.056 rows=340,928 loops=1) Workers Planned: 1 Workers Launched: 1
11.	319.584	3,436.400	↓ 2.0	170,464	2 / 2	→ Nested Loop (cost=0.09..498,084.25 rows=84,043 width=7,746) (actual time=0.140..3,436.400 rows=170,464 loops=2)
12.	1,810.290	1,810.290	↓ 1.1	100,502	2 / 2	→ Parallel Seq Scan on managed_applications ma (cost=0.00..322,916.27 rows=90,236 width=1,634) (actual time=0.089..1,810.290 rows=100,502 loops=2) Filter: (((state)::text = 'FL'::text) AND (plan_year = 2,020)) Rows Removed by Filter: 1,996,134
13.	1,306.526	1,306.526	↑ 2.5	2	201,004 / 2	→ Index Scan using index_managed_people_on_managed_application_id on managed_people mp (cost=0.09..1.93 rows=5 width=6,104) (actual time=0.009..0.013 rows=2 loops=201,004) Index Cond: (managed_application_id = ma.id)
14.						CTE all_app_data
15.	2,452.381	12,073.551	↑ 7.2	193,370	1	→ Hash Join (cost=18.39..12,025.44 rows=1,383,011 width=82,945) (actual time=4,197.628..12,073.551 rows=193,370 loops=1) Hash Cond: (ma_1.managed_app_id = f.managed_application_id)
16.	5,427.162	5,427.162	↓ 2.4	340,928	1	→ CTE Scan on managed_applications_filtered ma_1 (cost=0.00..857.24 rows=142,873 width=65,263) (actual time=2.468..5,427.162 rows=340,928 loops=1)
17.	924.828	4,194.008	↓ 147.6	285,735	1	→ Hash (cost=11.62..11.62 rows=1,936 width=17,678) (actual time=4,194.007..4,194.008 rows=285,735 loops=1) Buckets: 131,072 (originally 2048) Batches: 4 (originally 1) Memory Usage: 101,377kB
18.	3,269.180	3,269.180	↓ 147.6	285,735	1	→ CTE Scan on ffm_applications_filtered f (cost=0.00..11.62 rows=1,936 width=17,678) (actual time=1.948..3,269.180 rows=285,735 loops=1)
19.						CTE all_insurance_data
20.	858.134	14,828.305	↓ 1.4	193,369	1	→ Hash Join (cost=33.36..10,129.34 rows=141,506 width=91,455) (actual time=4,207.840..14,828.305 rows=193,369 loops=1) Hash Cond: (app.ffm_join_key = ifp.id)
21.	13,960.003	13,960.003	↑ 7.2	193,370	1	→ CTE Scan on all_app_data app (cost=0.00..8,298.07 rows=1,383,011 width=82,945) (actual time=4,197.642..13,960.003 rows=193,370 loops=1)
22.	1.008	10.168	↓ 41.5	831	1	→ Hash (cost=33.29..33.29 rows=20 width=8,510) (actual time=10.168..10.168 rows=831 loops=1) Buckets: 1,024 Batches: 1 Memory Usage: 349kB
23.	0.859	9.160	↓ 41.5	831	1	→ Hash Join (cost=27.34..33.29 rows=20 width=8,510) (actual time=0.956..9.160 rows=831 loops=1) Hash Cond: (ifp.insurance_full_issuer_id = ifi.id)
24.	7.607	7.607	↑ 1.0	872	1	→ CTE Scan on insurance_full_plans_filtered ifp (cost=0.00..5.23 rows=872 width=5,702) (actual time=0.249..7.607 rows=872 loops=1)
25.	0.028	0.694	↓ 1.1	21	1	→ Hash (cost=27.28..27.28 rows=19 width=2,808) (actual time=0.694..0.694 rows=21 loops=1) Buckets: 1,024 Batches: 1 Memory Usage: 14kB
26.	0.280	0.666	↓ 1.1	21	1	→ Hash Join (cost=0.13..27.28 rows=19 width=2,808) (actual time=0.229..0.666 rows=21 loops=1) Hash Cond: (ifi.issuer_group_id = igf.id)
27.	0.182	0.182	↑ 1.0	823	1	→ Seq Scan on insurance_full_issuers ifi (cost=0.00..26.47 rows=823 width=1,740) (actual time=0.007..0.182 rows=823 loops=1)
28.	0.009	0.204	↓ 1.1	15	1	→ Hash (cost=0.08..0.08 rows=14 width=1,068) (actual time=0.204..0.204 rows=15 loops=1) Buckets: 1,024 Batches: 1 Memory Usage: 10kB
29.	0.195	0.195	↓ 1.1	15	1	→ CTE Scan on issuer_groups_filtered igf (cost=0.00..0.08 rows=14 width=1,068) (actual time=0.055..0.195 rows=15 loops=1)
30.	175.273	17,627.859	↓ 1.4	193,369	1	→ Sort (cost=73,747.77..73,818.53 rows=141,506 width=1,608) (actual time=17,588.907..17,627.859 rows=193,369 loops=1) Sort Key: all_insurance_data.managed_app_id Sort Method: quicksort Memory: 35,993kB
31.	17,452.586	17,452.586	↓ 1.4	193,369	1	→ CTE Scan on all_insurance_data (cost=0.00..849.04 rows=141,506 width=1,608) (actual time=4,207.862..17,452.586 rows=193,369 loops=1)

Planning time	:	5.285 ms
Execution time	:	18,826.137 ms

WindowAgg  (cost=899925.99..901199.54 rows=141506 width=2152) (actual time=17588.960..18226.728 rows=193369 loops=1)
  CTE ffm_applications_filtered
    ->  Gather  (cost=1000.00..290088.49 rows=1936 width=2199) (actual time=1.937..1521.448 rows=285735 loops=1)
          Workers Planned: 1
          Workers Launched: 1
          ->  Parallel Seq Scan on ffm_applications  (cost=0.00..288894.89 rows=1139 width=2199) (actual time=0.171..1969.812 rows=142868 loops=2)
                Filter: (((status)::text = 'submitted'::text) AND (plan_year = 2020) AND (timezone('pst'::text, timezone('utc'::text, initial_submitted_at)) >= '2019-11-01 00:00:00'::timestamp without time zone) AND (timezone('pst'::text, timezone('utc'::text, initial_submitted_at)) <= '2019-11-23 00:00:00'::timestamp without time zone))
                Rows Removed by Filter: 1119684
  CTE issuer_groups_filtered
    ->  Seq Scan on issuer_groups  (cost=0.00..5.56 rows=14 width=60) (actual time=0.053..0.177 rows=15 loops=1)
          Filter: ((state)::text = 'FL'::text)
          Rows Removed by Filter: 428
  CTE insurance_full_plans_filtered
    ->  Seq Scan on insurance_full_plans  (cost=0.00..557.83 rows=872 width=242) (actual time=0.245..6.603 rows=872 loops=1)
          Filter: ((state)::text = 'FL'::text)
          Rows Removed by Filter: 15936
  CTE managed_applications_filtered
    ->  Gather  (cost=1000.09..513371.55 rows=142873 width=7746) (actual time=2.457..2647.056 rows=340928 loops=1)
          Workers Planned: 1
          Workers Launched: 1
          ->  Nested Loop  (cost=0.09..498084.25 rows=84043 width=7746) (actual time=0.140..3436.400 rows=170464 loops=2)
                ->  Parallel Seq Scan on managed_applications ma  (cost=0.00..322916.27 rows=90236 width=1634) (actual time=0.089..1810.290 rows=100502 loops=2)
                      Filter: (((state)::text = 'FL'::text) AND (plan_year = 2020))
                      Rows Removed by Filter: 1996134
                ->  Index Scan using index_managed_people_on_managed_application_id on managed_people mp  (cost=0.09..1.93 rows=5 width=6104) (actual time=0.009..0.013 rows=2 loops=201004)
                      Index Cond: (managed_application_id = ma.id)
  CTE all_app_data
    ->  Hash Join  (cost=18.39..12025.44 rows=1383011 width=82945) (actual time=4197.628..12073.551 rows=193370 loops=1)
          Hash Cond: (ma_1.managed_app_id = f.managed_application_id)
          ->  CTE Scan on managed_applications_filtered ma_1  (cost=0.00..857.24 rows=142873 width=65263) (actual time=2.468..5427.162 rows=340928 loops=1)
          ->  Hash  (cost=11.62..11.62 rows=1936 width=17678) (actual time=4194.007..4194.008 rows=285735 loops=1)
                Buckets: 131072 (originally 2048)  Batches: 4 (originally 1)  Memory Usage: 101377kB
                ->  CTE Scan on ffm_applications_filtered f  (cost=0.00..11.62 rows=1936 width=17678) (actual time=1.948..3269.180 rows=285735 loops=1)
  CTE all_insurance_data
    ->  Hash Join  (cost=33.36..10129.34 rows=141506 width=91455) (actual time=4207.840..14828.305 rows=193369 loops=1)
          Hash Cond: (app.ffm_join_key = ifp.id)
          ->  CTE Scan on all_app_data app  (cost=0.00..8298.07 rows=1383011 width=82945) (actual time=4197.642..13960.003 rows=193370 loops=1)
          ->  Hash  (cost=33.29..33.29 rows=20 width=8510) (actual time=10.168..10.168 rows=831 loops=1)
                Buckets: 1024  Batches: 1  Memory Usage: 349kB
                ->  Hash Join  (cost=27.34..33.29 rows=20 width=8510) (actual time=0.956..9.160 rows=831 loops=1)
                      Hash Cond: (ifp.insurance_full_issuer_id = ifi.id)
                      ->  CTE Scan on insurance_full_plans_filtered ifp  (cost=0.00..5.23 rows=872 width=5702) (actual time=0.249..7.607 rows=872 loops=1)
                      ->  Hash  (cost=27.28..27.28 rows=19 width=2808) (actual time=0.694..0.694 rows=21 loops=1)
                            Buckets: 1024  Batches: 1  Memory Usage: 14kB
                            ->  Hash Join  (cost=0.13..27.28 rows=19 width=2808) (actual time=0.229..0.666 rows=21 loops=1)
                                  Hash Cond: (ifi.issuer_group_id = igf.id)
                                  ->  Seq Scan on insurance_full_issuers ifi  (cost=0.00..26.47 rows=823 width=1740) (actual time=0.007..0.182 rows=823 loops=1)
                                  ->  Hash  (cost=0.08..0.08 rows=14 width=1068) (actual time=0.204..0.204 rows=15 loops=1)
                                        Buckets: 1024  Batches: 1  Memory Usage: 10kB
                                        ->  CTE Scan on issuer_groups_filtered igf  (cost=0.00..0.08 rows=14 width=1068) (actual time=0.055..0.195 rows=15 loops=1)
  ->  Sort  (cost=73747.77..73818.53 rows=141506 width=1608) (actual time=17588.907..17627.859 rows=193369 loops=1)
        Sort Key: all_insurance_data.managed_app_id
        Sort Method: quicksort  Memory: 35993kB
        ->  CTE Scan on all_insurance_data  (cost=0.00..849.04 rows=141506 width=1608) (actual time=4207.862..17452.586 rows=193369 loops=1)
Planning time: 5.285 ms
Execution time: 18826.137 ms

Per node type stats

node type	count	sum of times	% of query
CTE Scan	6	40,116.733 ms	220.1 %
Gather	2	0.000 ms	0.0 %
Hash	4	925.873 ms	5.1 %
Hash Join	4	3,311.654 ms	18.2 %
Index Scan	1	1,306.526 ms	7.2 %
Nested Loop	1	319.584 ms	1.8 %
Parallel Seq Scan	2	3,780.102 ms	20.7 %
Seq Scan	3	6.962 ms	0.0 %
Sort	1	175.273 ms	1.0 %
WindowAgg	1	598.869 ms	3.3 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
ffm_applications	1	1,969.812 ms	10.8 %
Parallel Seq Scan	1	1,969.812 ms	100.0 %
insurance_full_issuers	1	0.182 ms	0.0 %
Seq Scan	1	0.182 ms	100.0 %
insurance_full_plans	1	6.603 ms	0.0 %
Seq Scan	1	6.603 ms	100.0 %
issuer_groups	1	0.177 ms	0.0 %
Seq Scan	1	0.177 ms	100.0 %
managed_applications	1	1,810.290 ms	9.9 %
Parallel Seq Scan	1	1,810.290 ms	100.0 %
managed_people	1	1,306.526 ms	7.2 %
Index Scan	1	1,306.526 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jR3i

Color mode:

Visible columns:

Per node type stats

Per table stats