explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jR3i

Settings
# 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

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 = 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
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: 15936
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. 1,626.097 3,436.400 ↓ 2.0 170,464 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

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 = 2020))
  • Rows Removed by Filter: 1996134
13. 0.013 0.013 ↑ 2.5 2 201,004

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: 131072 (originally 2048) Batches: 4 (originally 1) Memory Usage: 101377kB
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: 1024 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: 1024 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: 1024 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: 35993kB
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