explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2hEs

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 1.344 230,795.219 ↓ 11.3 781 1

Nested Loop (cost=19,913,459.11..19,983,854.97 rows=69 width=64) (actual time=230,613.260..230,795.219 rows=781 loops=1)

2.          

CTE cares_active_irn

3. 38.679 230,425.606 ↓ 772.8 13,137 1

Nested Loop (cost=86,980.60..19,908,296.68 rows=17 width=52) (actual time=291.393..230,425.606 rows=13,137 loops=1)

4. 47.802 230,294.968 ↓ 772.8 13,137 1

Nested Loop (cost=86,980.31..19,908,289.66 rows=17 width=44) (actual time=291.366..230,294.968 rows=13,137 loops=1)

5. 72.364 230,142.070 ↓ 772.8 13,137 1

Nested Loop (cost=86,980.02..19,908,282.90 rows=17 width=20) (actual time=291.328..230,142.070 rows=13,137 loops=1)

6. 67.281 703.023 ↓ 10.6 24,471 1

Nested Loop (cost=86,979.73..116,838.65 rows=2,303 width=12) (actual time=119.005..703.023 rows=24,471 loops=1)

7. 76.509 391.032 ↓ 10.1 24,471 1

Hash Join (cost=86,979.45..115,989.70 rows=2,420 width=8) (actual time=118.956..391.032 rows=24,471 loops=1)

  • Hash Cond: (cspie_1.program_eligibility_id = cspe_1.program_eligibility_id)
8. 198.854 208.160 ↑ 1.2 56,028 1

Bitmap Heap Scan on cs_program_individual_eligibility cspie_1 (cost=2,332.02..31,056.82 rows=69,667 width=8) (actual time=12.275..208.160 rows=56,028 loops=1)

  • Recheck Cond: (effective_end_dt = to_date('12/31/9999'::character varying, 'mm/dd/yyyy'::character varying))
  • Filter: ((delete_ind = 'N'::bpchar) AND ((participation_status_cd)::text = 'RE'::text))
  • Rows Removed by Filter: 35110
  • Heap Blocks: exact=17663
9. 9.306 9.306 ↓ 1.0 91,483 1

Bitmap Index Scan on cs_program_individual_eligibility_id_2 (cost=0.00..2,314.60 rows=89,890 width=0) (actual time=9.306..9.306 rows=91,483 loops=1)

  • Index Cond: (effective_end_dt = to_date('12/31/9999'::character varying, 'mm/dd/yyyy'::character varying))
10. 14.465 106.363 ↑ 1.7 24,962 1

Hash (cost=84,110.15..84,110.15 rows=42,982 width=8) (actual time=106.363..106.363 rows=24,962 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 1488kB
11. 84.067 91.898 ↑ 1.7 24,962 1

Bitmap Heap Scan on cs_program_eligibility cspe_1 (cost=2,466.96..84,110.15 rows=42,982 width=8) (actual time=9.564..91.898 rows=24,962 loops=1)

  • Recheck Cond: (effective_end_dt = to_date('12/31/9999'::character varying, 'mm/dd/yyyy'::character varying))
  • Filter: ((delete_ind = 'N'::bpchar) AND ((finalize_cd)::text = 'Y'::text))
  • Rows Removed by Filter: 26758
  • Heap Blocks: exact=10665
12. 7.831 7.831 ↑ 1.1 52,317 1

Bitmap Index Scan on cs_program_eligibility_id_2 (cost=0.00..2,456.22 rows=58,105 width=0) (actual time=7.831..7.831 rows=52,317 loops=1)

  • Index Cond: (effective_end_dt = to_date('12/31/9999'::character varying, 'mm/dd/yyyy'::character varying))
13. 244.710 244.710 ↑ 1.0 1 24,471

Index Scan using cs_individual_membership_pk on cs_individual_membership csim_1 (cost=0.29..0.34 rows=1 width=12) (actual time=0.009..0.010 rows=1 loops=24,471)

  • Index Cond: (individual_membership_id = cspie_1.individual_membership_id)
  • Filter: ((delete_ind = 'N'::bpchar) AND (effective_end_dt = to_date('12/31/9999'::character varying, 'mm/dd/yyyy'::character varying)))
14. 270.279 229,366.683 ↑ 1.0 1 24,471

Index Scan using cs_program_history_fk_01_idx on cs_program_history csp (cost=0.29..8,593.76 rows=1 width=12) (actual time=8.239..9.373 rows=1 loops=24,471)

  • Index Cond: (program_id = cspe_1.program_id)
  • Filter: (((program_status_cd)::text = 'AP'::text) AND (program_history_id = (SubPlan 1)))
  • Rows Removed by Filter: 2
15.          

SubPlan (forIndex Scan)

16. 197.838 229,096.404 ↑ 1.0 1 32,973

Aggregate (cost=2,864.40..2,864.41 rows=1 width=4) (actual time=6.948..6.948 rows=1 loops=32,973)

17. 228,898.566 228,898.566 ↓ 1.5 3 32,973

Seq Scan on cs_program_history csph1 (cost=0.00..2,864.39 rows=2 width=4) (actual time=3.446..6.942 rows=3 loops=32,973)

  • Filter: ((benefits_case_id = csp.benefits_case_id) AND (delete_ind = 'N'::bpchar) AND ((program_cd)::text = 'MA'::text) AND (effective_begin_dt <= to_date('12/31/2018'::character varying, 'mm/dd/yyyy'::character varying)))
  • Rows Removed by Filter: 62122
18. 105.096 105.096 ↑ 1.0 1 13,137

Index Scan using cl_individual_pk on cl_individual cli_1 (cost=0.29..0.39 rows=1 width=28) (actual time=0.008..0.008 rows=1 loops=13,137)

  • Index Cond: (individual_id = csim_1.individual_id)
19. 91.959 91.959 ↑ 1.0 1 13,137

Index Scan using cs_benefits_case_pk on cs_benefits_case cbc_1 (cost=0.29..0.40 rows=1 width=12) (actual time=0.006..0.007 rows=1 loops=13,137)

  • Index Cond: (benefits_case_id = csim_1.benefits_case_id)
20.          

CTE mmis_active_irn

21. 4.276 4.276 ↑ 1.0 9,113 1

Seq Scan on t_mmis_recon_ibnd tmri (cost=0.00..243.13 rows=9,113 width=264) (actual time=0.008..4.276 rows=9,113 loops=1)

22. 4.199 230,790.751 ↓ 11.3 781 1

Nested Loop (cost=4,919.01..75,286.70 rows=69 width=56) (actual time=230,613.248..230,790.751 rows=781 loops=1)

23. 9.851 230,775.147 ↓ 5.8 2,281 1

Nested Loop (cost=4,918.72..75,068.13 rows=391 width=56) (actual time=230,613.226..230,775.147 rows=2,281 loops=1)

24. 30.355 230,723.400 ↑ 2.1 5,237 1

Hash Join (cost=4,918.30..34,016.76 rows=11,241 width=48) (actual time=230,612.637..230,723.400 rows=5,237 loops=1)

  • Hash Cond: (cspie.individual_membership_id = csim.individual_membership_id)
25. 83.522 95.212 ↑ 1.2 56,028 1

Bitmap Heap Scan on cs_program_individual_eligibility cspie (cost=2,332.02..31,056.82 rows=69,667 width=8) (actual time=14.674..95.212 rows=56,028 loops=1)

  • Recheck Cond: (effective_end_dt = to_date('12/31/9999'::character varying, 'mm/dd/yyyy'::character varying))
  • Filter: ((delete_ind = 'N'::bpchar) AND ((participation_status_cd)::text = 'RE'::text))
  • Rows Removed by Filter: 35110
  • Heap Blocks: exact=17663
26. 11.690 11.690 ↓ 1.0 91,483 1

Bitmap Index Scan on cs_program_individual_eligibility_id_2 (cost=0.00..2,314.60 rows=89,890 width=0) (actual time=11.690..11.690 rows=91,483 loops=1)

  • Index Cond: (effective_end_dt = to_date('12/31/9999'::character varying, 'mm/dd/yyyy'::character varying))
27. 1.359 230,597.833 ↑ 2.0 2,324 1

Hash (cost=2,528.27..2,528.27 rows=4,640 width=48) (actual time=230,597.833..230,597.833 rows=2,324 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 192kB
28. 14.158 230,596.474 ↑ 2.0 2,324 1

Hash Join (cost=1,463.98..2,528.27 rows=4,640 width=48) (actual time=230,545.970..230,596.474 rows=2,324 loops=1)

  • Hash Cond: (csim.individual_id = cli.individual_id)
29. 36.609 36.609 ↓ 1.0 28,072 1

Seq Scan on cs_individual_membership csim (cost=0.00..915.25 rows=27,372 width=12) (actual time=0.013..36.609 rows=28,072 loops=1)

  • Filter: ((delete_ind = 'N'::bpchar) AND (effective_end_dt = to_date('12/31/9999'::character varying, 'mm/dd/yyyy'::character varying)))
  • Rows Removed by Filter: 701
30. 1.307 230,545.707 ↑ 2.0 2,240 1

Hash (cost=1,407.01..1,407.01 rows=4,558 width=40) (actual time=230,545.707..230,545.707 rows=2,240 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 169kB
31. 2.913 230,544.400 ↑ 2.0 2,240 1

Hash Join (cost=1,047.60..1,407.01 rows=4,558 width=40) (actual time=230,521.713..230,544.400 rows=2,240 loops=1)

  • Hash Cond: ((mai.irn)::text = (cli.irn)::text)
32. 6.863 230,513.106 ↑ 1.9 2,337 1

Hash Anti Join (cost=0.55..257.43 rows=4,556 width=36) (actual time=230,493.165..230,513.106 rows=2,337 loops=1)

  • Hash Cond: ((mai.irn)::text = (cai.irn)::text)
33. 13.320 13.320 ↑ 1.0 9,113 1

CTE Scan on mmis_active_irn mai (cost=0.00..182.26 rows=9,113 width=36) (actual time=0.014..13.320 rows=9,113 loops=1)

34. 24.195 230,492.923 ↓ 772.8 13,137 1

Hash (cost=0.34..0.34 rows=17 width=36) (actual time=230,492.923..230,492.923 rows=13,137 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 667kB
35. 230,468.728 230,468.728 ↓ 772.8 13,137 1

CTE Scan on cares_active_irn cai (cost=0.00..0.34 rows=17 width=36) (actual time=291.398..230,468.728 rows=13,137 loops=1)

36. 14.676 28.381 ↑ 1.0 26,867 1

Hash (cost=710.91..710.91 rows=26,891 width=13) (actual time=28.381..28.381 rows=26,867 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1515kB
37. 13.705 13.705 ↓ 1.0 26,903 1

Seq Scan on cl_individual cli (cost=0.00..710.91 rows=26,891 width=13) (actual time=0.007..13.705 rows=26,903 loops=1)

38. 41.896 41.896 ↓ 0.0 0 5,237

Index Scan using cs_program_eligibility_pk on cs_program_eligibility cspe (cost=0.43..3.64 rows=1 width=16) (actual time=0.008..0.008 rows=0 loops=5,237)

  • Index Cond: (program_eligibility_id = cspie.program_eligibility_id)
  • Filter: ((delete_ind = 'N'::bpchar) AND ((finalize_cd)::text = 'Y'::text) AND (effective_end_dt = to_date('12/31/9999'::character varying, 'mm/dd/yyyy'::character varying)))
  • Rows Removed by Filter: 1
39. 11.405 11.405 ↓ 0.0 0 2,281

Index Scan using cs_program_history_fk_01_idx on cs_program_history csph (cost=0.29..0.55 rows=1 width=4) (actual time=0.005..0.005 rows=0 loops=2,281)

  • Index Cond: (program_id = cspe.program_id)
  • Filter: (((program_status_cd)::text = 'DC'::text) AND (effective_begin_dt <= to_date('12/31/2018'::character varying, 'mm/dd/yyyy'::character varying)) AND (effective_end_dt = to_date('12/31/9999'::character varying, 'mm/dd/yyyy'::character varying)))
  • Rows Removed by Filter: 2
40. 3.124 3.124 ↑ 1.0 1 781

Index Scan using cs_benefits_case_pk on cs_benefits_case cbc (cost=0.29..0.40 rows=1 width=12) (actual time=0.003..0.004 rows=1 loops=781)

  • Index Cond: (benefits_case_id = csim.benefits_case_id)