explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2zQI : manav

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Unique (cost=935,158,093.62..935,167,625.62 rows=4,766 width=13) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

Sort (cost=935,158,093.62..935,160,476.62 rows=4,766 width=13) (actual rows= loops=)

  • Sort Key: ((SubPlan 1)), enn.enrt_id, (CASE WHEN ((SubPlan 2) > 1) THEN 2 ELSE 1 END)
3. 0.000 0.000 ↓ 0.0

Nested Loop Anti Join (cost=421,759,439.50..935,099,859.95 rows=4,766 width=13) (actual rows= loops=)

  • Join Filter: ((app.logical_appln_id)::text = (ap.logical_appln_id)::text)
4. 0.000 0.000 ↓ 0.0

Hash Anti Join (cost=283,119,145.93..696,708,176.44 rows=4,767 width=13) (actual rows= loops=)

  • Hash Cond: ((ap.logical_appln_id)::text = (al1.logical_appln_id)::text)
5. 0.000 0.000 ↓ 0.0

Nested Loop Anti Join (cost=209,355,331.80..622,907,476.64 rows=6,408 width=13) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Nested Loop (cost=209,354,982.80..569,474,428.35 rows=6,833 width=19) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Nested Loop Anti Join (cost=209,354,895.30..561,797,629.73 rows=59,805 width=19) (actual rows= loops=)

  • Join Filter: ((SubPlan 3) = 'P'::text)
8. 0.000 0.000 ↓ 0.0

Hash Join (cost=209,354,895.30..242,454,300.07 rows=60,106 width=19) (actual rows= loops=)

  • Hash Cond: ((app_1.logical_appln_id)::text = (ap.logical_appln_id)::text)
9. 0.000 0.000 ↓ 0.0

Merge Join (cost=76,148.14..28,451,229.66 rows=6,259,027 width=13) (actual rows= loops=)

  • Merge Cond: (app_1.appln_id = enn.appln_id)
10. 0.000 0.000 ↓ 0.0

Index Only Scan using ix_t_appln on t_appln app_1 (cost=87.00..5,347,781.82 rows=10,200,663 width=13) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Index Only Scan using ix_t_enrt_app_cd on t_enrt enn (cost=112.00..11,745,372.72 rows=6,259,027 width=12) (actual rows= loops=)

  • Filter: (enrt_type_cd = ANY ('{440,441,444}'::numeric[]))
12. 0.000 0.000 ↓ 0.0

Hash (cost=209,275,172.16..209,275,172.16 rows=3,575 width=13) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Nested Loop (cost=174.00..209,275,172.16 rows=3,575 width=13) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Nested Loop (cost=87.00..208,958,654.63 rows=3,575 width=12) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Seq Scan on t_prsn_enrt pe (cost=0.00..208,442,823.00 rows=5,226 width=12) (actual rows= loops=)

  • Filter: ((ind_enrt_status_cd = ANY ('{430,7595}'::numeric[])) AND ((retro_in IS NULL) OR ((retro_in)::text = 'N'::text)) AND ((active_in)::text = 'Y'::text) AND ((date_part('day'::text, (( (...)
16. 0.000 0.000 ↓ 0.0

Index Scan using pk_t_enrt on t_enrt en (cost=87.00..98.20 rows=1 width=12) (actual rows= loops=)

  • Index Cond: (enrt_id = pe.enrt_id)
  • Filter: (enrt_type_cd = ANY ('{440,441,444}'::numeric[]))
17. 0.000 0.000 ↓ 0.0

Index Only Scan using ix_t_appln on t_appln ap (cost=87.00..88.04 rows=1 width=13) (actual rows= loops=)

  • Index Cond: (appln_id = en.appln_id)
18. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..2.50 rows=1 width=0) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Seq Scan on dual (cost=0.00..1.50 rows=1 width=0) (actual rows= loops=)

20.          

SubPlan (for Nested Loop Anti Join)

21. 0.000 0.000 ↓ 0.0

Aggregate (cost=5,310.50..5,311.50 rows=1 width=2) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Nested Loop (cost=689.00..5,298.50 rows=23 width=2) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Nested Loop (cost=575.50..5,018.22 rows=1 width=13) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Nested Loop (cost=462.50..4,903.66 rows=1 width=7) (actual rows= loops=)

  • Join Filter: ((app_2.appln_id = mbrsh_1.appln_id) AND (prsnenrt_1.prsn_mbrsh_id = mbrsh_1.prsn_mbrsh_id))
25. 0.000 0.000 ↓ 0.0

Nested Loop (cost=375.00..4,813.44 rows=1 width=31) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Nested Loop (cost=261.50..4,687.00 rows=1 width=24) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Nested Loop (cost=174.00..1,883.37 rows=10 width=18) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Index Scan using idx_t_appln on t_appln app_2 (cost=87.00..187.27 rows=16 width=6) (actual rows= loops=)

  • Index Cond: ((logical_appln_id)::text = (ap.logical_appln_id)::text)
29. 0.000 0.000 ↓ 0.0

Index Scan using ix_t_enrt_appln_id on t_enrt enrt_2 (cost=87.00..105.51 rows=1 width=12) (actual rows= loops=)

  • Index Cond: (appln_id = app_2.appln_id)
  • Filter: (enrt_type_cd = ANY ('{440,441,444}'::numeric[]))
30. 0.000 0.000 ↓ 0.0

Index Scan using fk_t_prsn_enrt_t_enrt on t_prsn_enrt prsnenrt_1 (cost=87.50..279.86 rows=1 width=18) (actual rows= loops=)

  • Index Cond: (enrt_id = enrt_2.enrt_id)
  • Filter: ((ind_enrt_status_cd = ANY ('{430,7595}'::numeric[])) AND ((retro_in IS NULL) OR ((retro_in)::text = 'N'::text)) AND ((date_part('day'::text, ((cov_end_dt + '1 day':: (...)
31. 0.000 0.000 ↓ 0.0

Index Scan using pk_t_prsn_elgt on t_prsn_elgt elgt_1 (cost=113.50..125.93 rows=1 width=19) (actual rows= loops=)

  • Index Cond: (prsn_elgt_id = prsnenrt_1.prsn_elgt_id)
  • Filter: ((prog_type_cd = ANY ('{440,441}'::numeric[])) AND (elgt_status_cd = '2100'::numeric) AND (prsnenrt_1.prsn_mbrsh_id = prsn_mbrsh_id))
32. 0.000 0.000 ↓ 0.0

Index Scan using pk_t_prsn_mbrsh on t_prsn_mbrsh mbrsh_1 (cost=87.50..88.73 rows=1 width=12) (actual rows= loops=)

  • Index Cond: (prsn_mbrsh_id = elgt_1.prsn_mbrsh_id)
33. 0.000 0.000 ↓ 0.0

Index Only Scan using pk_t_hix_asst_unit on t_hix_asst_unit asstunit (cost=113.00..114.05 rows=1 width=6) (actual rows= loops=)

  • Index Cond: (asst_unit_id = elgt_1.asst_unit_id)
34. 0.000 0.000 ↓ 0.0

Index Scan using ix_t_au_prsn_asst_unit_id on t_au_prsn auprsn (cost=113.50..261.78 rows=37 width=8) (actual rows= loops=)

  • Index Cond: (asst_unit_id = elgt_1.asst_unit_id)
  • Filter: ((is_target_in)::text = 'Y'::text)
35. 0.000 0.000 ↓ 0.0

Index Scan using fk_t_prsn_enrt_t_enrt on t_prsn_enrt pee (cost=87.50..127.86 rows=1 width=6) (actual rows= loops=)

  • Index Cond: (enrt_id = enn.enrt_id)
  • Filter: (((retro_in IS NULL) OR ((retro_in)::text = 'N'::text)) AND (ind_enrt_status_cd = ANY ('{430,7595}'::numeric[])) AND ((active_in)::text = 'Y'::text))
36. 0.000 0.000 ↓ 0.0

Nested Loop (cost=349.00..7,819.35 rows=1 width=13) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Index Scan using idx_t_appln on t_appln ap1 (cost=87.00..103.20 rows=16 width=13) (actual rows= loops=)

  • Index Cond: ((logical_appln_id)::text = (ap.logical_appln_id)::text)
38. 0.000 0.000 ↓ 0.0

Nested Loop (cost=262.00..481.76 rows=1 width=12) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Index Scan using ix_t_enrt_appln_id on t_enrt en1 (cost=87.00..90.60 rows=1 width=12) (actual rows= loops=)

  • Index Cond: (appln_id = ap1.appln_id)
  • Filter: (enrt_type_cd = ANY ('{440,441,444}'::numeric[]))
40. 0.000 0.000 ↓ 0.0

Nested Loop (cost=175.00..390.66 rows=1 width=12) (actual rows= loops=)

41. 0.000 0.000 ↓ 0.0

Index Scan using ix_t_prsn_addl_attr_prsn_mbrsh_id on t_prsn_addl_attr paa (cost=87.50..102.29 rows=1 width=6) (actual rows= loops=)

  • Index Cond: (pe.prsn_mbrsh_id = prsn_mbrsh_id)
  • Filter: ((preg_due_dt IS NOT NULL) AND (trunc(preg_due_dt) > trunc((to_timestamp((current_timestamp)::text, 'YYYY-MM-DD'::text))::timestamp without time zone)))
42. 0.000 0.000 ↓ 0.0

Index Scan using fk_t_prsn_enrt_t_enrt on t_prsn_enrt pe1 (cost=87.50..287.86 rows=1 width=6) (actual rows= loops=)

  • Index Cond: (enrt_id = en1.enrt_id)
  • Filter: ((ind_enrt_status_cd = ANY ('{430,7595}'::numeric[])) AND ((retro_in IS NULL) OR ((retro_in)::text = 'N'::text)) AND ((active_in)::text = 'Y'::text) AND ((date_part('day'::text, ((cov_end_dt + '1 da (...)
43. 0.000 0.000 ↓ 0.0

Hash (cost=73,606,521.13..73,606,521.13 rows=157,293 width=7) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

Nested Loop (cost=50,917,356.13..73,606,521.13 rows=157,293 width=7) (actual rows= loops=)

45. 0.000 0.000 ↓ 0.0

Hash Join (cost=50,917,269.13..59,680,369.52 rows=157,293 width=6) (actual rows= loops=)

  • Hash Cond: (r1.prsn_enrt_id = pt1.prsn_enrt_id)
46. 0.000 0.000 ↓ 0.0

Index Only Scan using ma_pe_temp_index_2 on t_rnwl_prsn_enrt r1 (cost=112.00..5,823,295.64 rows=3,815,027 width=6) (actual rows= loops=)

  • Index Cond: ((status_cd = ANY ('{7501,7500}'::numeric[])) AND (rnwl_flow_cd = '7534'::numeric))
47. 0.000 0.000 ↓ 0.0

Hash (cost=50,202,042.13..50,202,042.13 rows=715,115 width=12) (actual rows= loops=)

48. 0.000 0.000 ↓ 0.0

Hash Join (cost=12,716,321.63..50,202,042.13 rows=715,115 width=12) (actual rows= loops=)

  • Hash Cond: (pt1.enrt_id = et1.enrt_id)
49. 0.000 0.000 ↓ 0.0

Seq Scan on t_prsn_enrt pt1 (cost=0.00..34,996,923.00 rows=1,045,235 width=12) (actual rows= loops=)

  • Filter: ((((retro_in)::text = 'N'::text) OR (retro_in IS NULL)) AND (ind_enrt_status_cd = ANY ('{430,7595}'::numeric[])) AND ((active_in)::text = 'Y'::text))
50. 0.000 0.000 ↓ 0.0

Hash (cost=6,426,732.63..6,426,732.63 rows=6,259,027 width=12) (actual rows= loops=)

51. 0.000 0.000 ↓ 0.0

Index Only Scan using ma_pe_temp_index_1 on t_enrt et1 (cost=112.00..6,426,732.63 rows=6,259,027 width=12) (actual rows= loops=)

  • Index Cond: (enrt_type_cd = ANY ('{440,441,444}'::numeric[]))
52. 0.000 0.000 ↓ 0.0

Index Only Scan using ix_t_appln on t_appln al1 (cost=87.00..88.04 rows=1 width=13) (actual rows= loops=)

  • Index Cond: (appln_id = et1.appln_id)
53. 0.000 0.000 ↓ 0.0

Materialize (cost=138,640,293.57..223,514,360.74 rows=1 width=7) (actual rows= loops=)

54. 0.000 0.000 ↓ 0.0

Nested Loop (cost=138,640,293.57..223,514,359.74 rows=1 width=7) (actual rows= loops=)

55. 0.000 0.000 ↓ 0.0

Nested Loop (cost=138,640,180.57..223,514,245.23 rows=1 width=20) (actual rows= loops=)

  • Join Filter: (app.appln_id = mbrsh.appln_id)
56. 0.000 0.000 ↓ 0.0

Merge Join (cost=138,640,093.07..223,514,046.29 rows=2 width=38) (actual rows= loops=)

  • Merge Cond: (elgt.prsn_elgt_id = prsnenrt.prsn_elgt_id)
  • Join Filter: (au.asst_unit_id = elgt.asst_unit_id)
57. 0.000 0.000 ↓ 0.0

Index Scan using pk_t_prsn_elgt on t_prsn_elgt elgt (cost=113.50..40,770,253.10 rows=75,075,984 width=13) (actual rows= loops=)

58. 0.000 0.000 ↓ 0.0

Materialize (cost=138,637,777.69..141,292,714.69 rows=2,654,937 width=37) (actual rows= loops=)

59. 0.000 0.000 ↓ 0.0

Sort (cost=138,637,777.69..139,965,246.19 rows=2,654,937 width=37) (actual rows= loops=)

  • Sort Key: prsnenrt.prsn_elgt_id
60. 0.000 0.000 ↓ 0.0

Nested Loop (cost=47,548,619.71..81,897,800.47 rows=2,654,937 width=37) (actual rows= loops=)

61. 0.000 0.000 ↓ 0.0

Nested Loop (cost=289.00..9,538,350.26 rows=657 width=31) (actual rows= loops=)

62. 0.000 0.000 ↓ 0.0

Nested Loop (cost=202.00..9,480,181.87 rows=657 width=18) (actual rows= loops=)

63. 0.000 0.000 ↓ 0.0

Index Scan using ix_t_prsn_enrt_prsn_mbrsh_id on t_prsn_enrt prsnenrt (cost=115.00..9,384,997.07 rows=960 width=18) (actual rows= loops=)

  • Index Cond: (cov_end_dt = trunc(last_day(trunc((current_timestamp)::timestamp without time zone))))
  • Filter: ((ind_enrt_status_cd = ANY ('{430,7595}'::numeric[])) AND ((retro_in IS NULL) OR ((retro_in)::text = 'N'::text)))
64. 0.000 0.000 ↓ 0.0

Index Scan using pk_t_enrt on t_enrt enrt (cost=87.00..98.65 rows=1 width=12) (actual rows= loops=)

  • Index Cond: (enrt_id = prsnenrt.enrt_id)
  • Filter: (enrt_type_cd = ANY ('{440,441,444}'::numeric[]))
65. 0.000 0.000 ↓ 0.0

Index Only Scan using ix_t_appln on t_appln app (cost=87.00..88.04 rows=1 width=13) (actual rows= loops=)

  • Index Cond: (appln_id = enrt.appln_id)
66. 0.000 0.000 ↓ 0.0

Materialize (cost=47,548,330.71..69,706,533.71 rows=4,041 width=6) (actual rows= loops=)

67. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on t_au_prsn au (cost=47,548,330.71..69,702,492.71 rows=4,041 width=6) (actual rows= loops=)

  • Recheck Cond: (cov_group_tx IS NOT NULL)
  • Filter: (((is_target_in)::text = 'Y'::text) AND ((cov_type_tx_int)::text = 'P'::text))
68. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on idx1607301453240 (cost=0.00..47,548,128.66 rows=13,921,812 width=0) (actual rows= loops=)

  • Index Cond: (cov_group_tx IS NOT NULL)
69. 0.000 0.000 ↓ 0.0

Index Scan using pk_t_prsn_mbrsh on t_prsn_mbrsh mbrsh (cost=87.50..98.47 rows=1 width=12) (actual rows= loops=)

  • Index Cond: (prsn_mbrsh_id = prsnenrt.prsn_mbrsh_id)
70. 0.000 0.000 ↓ 0.0

Index Only Scan using pk_t_hix_asst_unit on t_hix_asst_unit asst (cost=113.00..114.01 rows=1 width=6) (actual rows= loops=)

  • Index Cond: (asst_unit_id = elgt.asst_unit_id)
71.          

SubPlan (for Nested Loop Anti Join)

72. 0.000 0.000 ↓ 0.0

Aggregate (cost=214.27..214.77 rows=1 width=6) (actual rows= loops=)

73. 0.000 0.000 ↓ 0.0

Index Scan using idx_t_appln on t_appln apl (cost=87.00..207.27 rows=14 width=6) (actual rows= loops=)

  • Index Cond: ((logical_appln_id)::text = (ap.logical_appln_id)::text)
  • Filter: (appln_status_cd = ANY ('{3352,3858,1769,1770,2270}'::numeric[]))
74. 0.000 0.000 ↓ 0.0

Aggregate (cost=2,904.29..2,904.79 rows=1 width=6) (actual rows= loops=)

75. 0.000 0.000 ↓ 0.0

Nested Loop Semi Join (cost=349.00..2,903.79 rows=1 width=6) (actual rows= loops=)

76. 0.000 0.000 ↓ 0.0

Nested Loop (cost=261.50..2,782.79 rows=1 width=12) (actual rows= loops=)

77. 0.000 0.000 ↓ 0.0

Nested Loop (cost=174.00..1,883.37 rows=8 width=6) (actual rows= loops=)

78. 0.000 0.000 ↓ 0.0

Index Scan using idx_t_appln on t_appln appln (cost=87.00..187.27 rows=16 width=6) (actual rows= loops=)

  • Index Cond: ((logical_appln_id)::text = (ap.logical_appln_id)::text)
79. 0.000 0.000 ↓ 0.0

Index Scan using ix_t_enrt_appln_id on t_enrt enrt_1 (cost=87.00..105.51 rows=1 width=12) (actual rows= loops=)

  • Index Cond: (appln_id = appln.appln_id)
  • Filter: ((plan_denrt_dt IS NULL) AND (enrt_type_cd = ANY ('{440,441,444}'::numeric[])))
80. 0.000 0.000 ↓ 0.0

Index Scan using fk_t_prsn_enrt_t_enrt on t_prsn_enrt pt (cost=87.50..111.93 rows=1 width=6) (actual rows= loops=)

  • Index Cond: (enrt_id = enrt_1.enrt_id)
  • Filter: (ind_enrt_status_cd = ANY ('{430,7595}'::numeric[]))
81. 0.000 0.000 ↓ 0.0

Index Scan using fk_t_prsn_enrt_t_enrt on t_prsn_enrt penrt (cost=87.50..129.16 rows=1 width=6) (actual rows= loops=)

  • Index Cond: (enrt_id = pt.enrt_id)
  • Filter: ((ind_enrt_status_cd = ANY ('{430,7595}'::numeric[])) AND ((retro_in IS NULL) OR ((retro_in)::text = 'N'::text)) AND ((active_in)::text = 'Y'::text))