explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Z77Z

Settings
# exclusive inclusive rows x rows loops node
1. 452.140 43,171.729 ↓ 1.4 193,369 1

WindowAgg (cost=893,257.83..894,531.39 rows=141,506 width=1,636) (actual time=42,702.730..43,171.729 rows=193,369 loops=1)

2.          

CTE ffm_applications_filtered

3. 0.000 1,503.091 ↓ 147.6 285,733 1

Gather (cost=1,000.00..290,088.49 rows=1,936 width=28) (actual time=0.395..1,503.091 rows=285,733 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
4. 1,562.160 1,562.160 ↓ 125.4 142,866 2

Parallel Seq Scan on ffm_applications (cost=0.00..288,894.89 rows=1,139 width=28) (actual time=0.120..1,562.160 rows=142,866 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: 1119587
5.          

CTE issuer_groups_filtered

6. 0.138 0.138 ↓ 1.1 15 1

Seq Scan on issuer_groups (cost=0.00..5.56 rows=14 width=36) (actual time=0.046..0.138 rows=15 loops=1)

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

CTE insurance_full_plans_filtered

8. 6.121 6.121 ↑ 1.0 872 1

Seq Scan on insurance_full_plans (cost=0.00..557.83 rows=872 width=52) (actual time=0.279..6.121 rows=872 loops=1)

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

CTE managed_applications_filtered

10. 930.504 40,128.523 ↓ 2.4 340,828 1

Hash Join (cost=330,283.27..509,858.25 rows=142,873 width=12) (actual time=14,763.978..40,128.523 rows=340,828 loops=1)

  • Hash Cond: (mp.managed_application_id = ma.id)
11. 24,436.541 24,436.541 ↑ 1.0 3,860,389 1

Seq Scan on managed_people mp (cost=0.00..176,249.49 rows=3,862,498 width=12) (actual time=0.559..24,436.541 rows=3,860,389 loops=1)

12. 65.046 14,761.478 ↓ 1.3 200,940 1

Hash (cost=329,746.36..329,746.36 rows=153,401 width=4) (actual time=14,761.478..14,761.478 rows=200,940 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 9113kB
13. 14,696.432 14,696.432 ↓ 1.3 200,940 1

Seq Scan on managed_applications ma (cost=0.00..329,746.36 rows=153,401 width=4) (actual time=0.043..14,696.432 rows=200,940 loops=1)

  • Filter: (((state)::text = 'FL'::text) AND (plan_year = 2020))
  • Rows Removed by Filter: 3992094
14.          

CTE all_app_data

15. 88.400 42,423.076 ↑ 7.2 193,370 1

Merge Join (cost=3,336.60..8,870.58 rows=1,383,011 width=44) (actual time=42,223.019..42,423.076 rows=193,370 loops=1)

  • Merge Cond: (f.managed_application_id = ma_1.managed_app_id)
16. 224.367 1,915.722 ↓ 147.6 285,733 1

Sort (cost=32.75..33.72 rows=1,936 width=28) (actual time=1,857.173..1,915.722 rows=285,733 loops=1)

  • Sort Key: f.managed_application_id
  • Sort Method: quicksort Memory: 34611kB
17. 1,691.355 1,691.355 ↓ 147.6 285,733 1

CTE Scan on ffm_applications_filtered f (cost=0.00..11.62 rows=1,936 width=28) (actual time=0.398..1,691.355 rows=285,733 loops=1)

18. 176.488 40,418.954 ↓ 2.4 341,031 1

Sort (cost=3,303.85..3,375.29 rows=142,873 width=12) (actual time=40,365.837..40,418.954 rows=341,031 loops=1)

  • Sort Key: ma_1.managed_app_id
  • Sort Method: quicksort Memory: 28265kB
19. 40,242.466 40,242.466 ↓ 2.4 340,828 1

CTE Scan on managed_applications_filtered ma_1 (cost=0.00..857.24 rows=142,873 width=12) (actual time=14,763.983..40,242.466 rows=340,828 loops=1)

20.          

CTE all_insurance_data

21. 67.563 42,565.376 ↓ 1.4 193,369 1

Hash Join (cost=33.36..10,129.34 rows=141,506 width=1,608) (actual time=42,230.591..42,565.376 rows=193,369 loops=1)

  • Hash Cond: (app.ffm_join_key = ifp.full_plans_id)
22. 42,490.266 42,490.266 ↑ 7.2 193,370 1

CTE Scan on all_app_data app (cost=0.00..8,298.07 rows=1,383,011 width=32) (actual time=42,223.022..42,490.266 rows=193,370 loops=1)

23. 0.211 7.547 ↓ 41.5 831 1

Hash (cost=33.29..33.29 rows=20 width=1,584) (actual time=7.547..7.547 rows=831 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 87kB
24. 0.302 7.336 ↓ 41.5 831 1

Hash Join (cost=27.34..33.29 rows=20 width=1,584) (actual time=0.850..7.336 rows=831 loops=1)

  • Hash Cond: (ifp.insurance_full_issuer_id = ifi.id)
25. 6.487 6.487 ↑ 1.0 872 1

CTE Scan on insurance_full_plans_filtered ifp (cost=0.00..5.23 rows=872 width=1,556) (actual time=0.291..6.487 rows=872 loops=1)

26. 0.005 0.547 ↓ 1.1 21 1

Hash (cost=27.28..27.28 rows=19 width=36) (actual time=0.547..0.547 rows=21 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
27. 0.156 0.542 ↓ 1.1 21 1

Hash Join (cost=0.13..27.28 rows=19 width=36) (actual time=0.195..0.542 rows=21 loops=1)

  • Hash Cond: (ifi.issuer_group_id = igf.groups_id)
28. 0.235 0.235 ↑ 1.0 823 1

Seq Scan on insurance_full_issuers ifi (cost=0.00..26.47 rows=823 width=8) (actual time=0.014..0.235 rows=823 loops=1)

29. 0.005 0.151 ↓ 1.1 15 1

Hash (cost=0.08..0.08 rows=14 width=36) (actual time=0.151..0.151 rows=15 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
30. 0.146 0.146 ↓ 1.1 15 1

CTE Scan on issuer_groups_filtered igf (cost=0.00..0.08 rows=14 width=36) (actual time=0.048..0.146 rows=15 loops=1)

31. 59.055 42,719.589 ↓ 1.4 193,369 1

Sort (cost=73,747.77..73,818.53 rows=141,506 width=1,608) (actual time=42,702.687..42,719.589 rows=193,369 loops=1)

  • Sort Key: all_insurance_data.managed_app_id
  • Sort Method: quicksort Memory: 35993kB
32. 42,660.534 42,660.534 ↓ 1.4 193,369 1

CTE Scan on all_insurance_data (cost=0.00..849.04 rows=141,506 width=1,608) (actual time=42,230.594..42,660.534 rows=193,369 loops=1)

Planning time : 2.411 ms
Execution time : 43,206.729 ms