explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UwcW

Settings
# exclusive inclusive rows x rows loops node
1. 484.295 36,651.434 ↓ 1.4 193,369 1

WindowAgg (cost=899,917.75..901,191.31 rows=141,506 width=2,152) (actual time=36,134.021..36,651.434 rows=193,369 loops=1)

2.          

CTE ffm_applications_filtered

3. 0.000 1,610.149 ↓ 147.6 285,734 1

Gather (cost=1,000.00..290,088.49 rows=1,936 width=2,199) (actual time=1.915..1,610.149 rows=285,734 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
4. 2,061.008 2,061.008 ↓ 125.4 142,867 2

Parallel Seq Scan on ffm_applications (cost=0.00..288,894.89 rows=1,139 width=2,199) (actual time=0.110..2,061.008 rows=142,867 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: 1119352
5.          

CTE issuer_groups_filtered

6. 0.212 0.212 ↓ 1.1 15 1

Seq Scan on issuer_groups (cost=0.00..5.56 rows=14 width=60) (actual time=0.055..0.212 rows=15 loops=1)

  • Filter: ((state)::text = 'FL'::text)
  • Rows Removed by Filter: 428
7.          

CTE insurance_full_plans_filtered

8. 6.458 6.458 ↑ 1.0 872 1

Seq Scan on insurance_full_plans (cost=0.00..557.83 rows=872 width=242) (actual time=0.255..6.458 rows=872 loops=1)

  • Filter: ((state)::text = 'FL'::text)
  • Rows Removed by Filter: 15936
9.          

CTE managed_applications_filtered

10. 0.000 21,845.447 ↓ 2.4 340,602 1

Gather (cost=1,000.09..513,363.32 rows=142,873 width=7,746) (actual time=2.322..21,845.447 rows=340,602 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
11. 21,079.669 22,820.219 ↓ 2.0 170,301 2

Nested Loop (cost=0.09..498,076.02 rows=84,043 width=7,746) (actual time=2.134..22,820.219 rows=170,301 loops=2)

12. 1,740.343 1,740.343 ↓ 1.1 100,400 2

Parallel Seq Scan on managed_applications ma (cost=0.00..322,916.27 rows=90,236 width=1,634) (actual time=2.083..1,740.343 rows=100,400 loops=2)

  • Filter: (((state)::text = 'FL'::text) AND (plan_year = 2020))
  • Rows Removed by Filter: 1995862
13. 0.207 0.207 ↑ 2.5 2 200,799

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.115..0.207 rows=2 loops=200,799)

  • Index Cond: (managed_application_id = ma.id)
14.          

CTE all_app_data

15. 1,952.561 30,579.493 ↑ 7.2 193,370 1

Hash Join (cost=18.39..12,025.44 rows=1,383,011 width=82,945) (actual time=4,267.956..30,579.493 rows=193,370 loops=1)

  • Hash Cond: (ma_1.managed_app_id = f.managed_application_id)
16. 24,361.739 24,361.739 ↓ 2.4 340,602 1

CTE Scan on managed_applications_filtered ma_1 (cost=0.00..857.24 rows=142,873 width=65,263) (actual time=2.331..24,361.739 rows=340,602 loops=1)

17. 912.980 4,265.193 ↓ 147.6 285,734 1

Hash (cost=11.62..11.62 rows=1,936 width=17,678) (actual time=4,265.193..4,265.193 rows=285,734 loops=1)

  • Buckets: 131072 (originally 2048) Batches: 4 (originally 1) Memory Usage: 101377kB
18. 3,352.213 3,352.213 ↓ 147.6 285,734 1

CTE Scan on ffm_applications_filtered f (cost=0.00..11.62 rows=1,936 width=17,678) (actual time=1.931..3,352.213 rows=285,734 loops=1)

19.          

CTE all_insurance_data

20. 844.797 33,320.544 ↓ 1.4 193,369 1

Hash Join (cost=33.36..10,129.34 rows=141,506 width=91,455) (actual time=4,278.050..33,320.544 rows=193,369 loops=1)

  • Hash Cond: (app.ffm_join_key = ifp.id)
21. 32,465.701 32,465.701 ↑ 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,267.970..32,465.701 rows=193,370 loops=1)

22. 0.980 10.046 ↓ 41.5 831 1

Hash (cost=33.29..33.29 rows=20 width=8,510) (actual time=10.046..10.046 rows=831 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 349kB
23. 0.895 9.066 ↓ 41.5 831 1

Hash Join (cost=27.34..33.29 rows=20 width=8,510) (actual time=1.017..9.066 rows=831 loops=1)

  • Hash Cond: (ifp.insurance_full_issuer_id = ifi.id)
24. 7.430 7.430 ↑ 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.260..7.430 rows=872 loops=1)

25. 0.032 0.741 ↓ 1.1 21 1

Hash (cost=27.28..27.28 rows=19 width=2,808) (actual time=0.741..0.741 rows=21 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
26. 0.277 0.709 ↓ 1.1 21 1

Hash Join (cost=0.13..27.28 rows=19 width=2,808) (actual time=0.261..0.709 rows=21 loops=1)

  • Hash Cond: (ifi.issuer_group_id = igf.id)
27. 0.198 0.198 ↑ 1.0 823 1

Seq Scan on insurance_full_issuers ifi (cost=0.00..26.47 rows=823 width=1,740) (actual time=0.009..0.198 rows=823 loops=1)

28. 0.009 0.234 ↓ 1.1 15 1

Hash (cost=0.08..0.08 rows=14 width=1,068) (actual time=0.234..0.234 rows=15 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
29. 0.225 0.225 ↓ 1.1 15 1

CTE Scan on issuer_groups_filtered igf (cost=0.00..0.08 rows=14 width=1,068) (actual time=0.056..0.225 rows=15 loops=1)

30. 225.676 36,167.139 ↓ 1.4 193,369 1

Sort (cost=73,747.77..73,818.53 rows=141,506 width=1,608) (actual time=36,133.949..36,167.139 rows=193,369 loops=1)

  • Sort Key: all_insurance_data.managed_app_id
  • Sort Method: quicksort Memory: 35993kB
31. 35,941.463 35,941.463 ↓ 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,278.074..35,941.463 rows=193,369 loops=1)

Planning time : 7.013 ms
Execution time : 37,321.298 ms