explain.depesz.com

PostgreSQL's explain analyze made readable

Result: x9tc

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

Unique (cost=73,606,042.77..75,183,231.32 rows=45,062,530 width=770) (actual rows= loops=)

  • WHEN ("OBJECTIVE_TR"."NAME" IS NULL) THEN "OBJECTIVE"."NAME" ELSE "OBJECTIVE_TR"."NAME" END), (("OBJECTIVE_TRACKER"."POINTS_TARGET")::text), "OBJECTIVE_TRCKR_STTS"."NAME", (CASE WHEN ("GOAL_TRACKER_STTS_TR"."NAME" IS NULL) THEN "GOAL_TRACKER_STATUS"."NAME" ELSE "GOAL_TRACKER_STTS_TR"."NAME" END), (("OBJECTIVE_TRACKER"."POINTS_ACHIEVED")::text), (to_char("OBJECTIVE_TRACKER"."EFFECTIVE_TO", 'YYYYMMDD'::text))
2. 0.000 0.000 ↓ 0.0

Sort (cost=73,606,042.77..73,718,699.09 rows=45,062,530 width=770) (actual rows= loops=)

  • Sort Key: (to_char("GOAL_TRACKER"."EFFECTIVE_TO", 'YYYYMMDD'::text)), (("PARTY"."ID")::text), (CASE WHEN ("PERSON_GENDER_TR"."NAME" IS NULL) THEN "PERSON_GENDER"."CODE" ELSE "PERSON_GENDER_TR"."NAME" END), (to_char("PERSON"."BORN_ON", 'YYYYMMDD'::text)), "STATUS_TYPE"."NAME", (to_char("OBJECTIVE_TRACKER"."EFFECTIVE_FROM", 'YYYYMMDD'::text)), "GOAL_TYPE"."NAME", (CASE
3. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=2,042,184.06..6,268,440.89 rows=45,062,530 width=770) (actual rows= loops=)

  • Hash Cond: (("OBJECTIVE"."ID" = "OBJECTIVE_TR"."OBJECTIVE_ID") AND ("OBJECTIVE"."TENANT" = "OBJECTIVE_TR"."TENANT") AND ("SYSTEM_LANGUAGE"."ID" = "OBJECTIVE_TR"."SYSTEM_LANGUAGE_ID"))
4. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=2,042,182.78..4,650,244.17 rows=45,062,530 width=203) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=2,042,182.78..4,086,961.46 rows=22,531,265 width=196) (actual rows= loops=)

  • Hash Cond: ("OBJECTIVE_TRACKER"."OBJECTIVE_TRCKR_STTS_ID" = "OBJECTIVE_TRCKR_STTS"."ID")
6. 0.000 0.000 ↓ 0.0

Hash Join (cost=2,042,181.71..3,944,262.38 rows=22,531,265 width=183) (actual rows= loops=)

  • Hash Cond: ("GOAL_TRACKER"."PARTY_ID" = "PARTY"."ID")
7. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=1,411,884.94..2,818,226.62 rows=4,620,154 width=156) (actual rows= loops=)

  • Hash Cond: ("GOAL_TRACKER"."GOAL_TRACKER_STATUS_ID" = "GOAL_TRACKER_STATUS"."ID")
8. 0.000 0.000 ↓ 0.0

Gather (cost=1,411,882.26..2,760,027.77 rows=3,198,568 width=113) (actual rows= loops=)

  • Workers Planned: 2
9. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=1,410,882.26..2,439,170.97 rows=1,332,737 width=113) (actual rows= loops=)

  • Hash Cond: ("OBJECTIVE_TRACKER"."OBJECTIVE_ID" = "OBJECTIVE"."ID")
10. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=1,410,881.15..2,432,706.09 rows=1,332,737 width=81) (actual rows= loops=)

  • Hash Cond: ("GOAL_TRACKER"."GOAL_ID" = "GOAL"."ID")
11. 0.000 0.000 ↓ 0.0

Hash Join (cost=1,410,879.06..2,414,378.86 rows=1,332,737 width=68) (actual rows= loops=)

  • Hash Cond: ("OBJECTIVE_TRACKER"."GOAL_TRACKER_ID" = "GOAL_TRACKER"."ID")
12. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on "OBJECTIVE_TRACKER" (cost=0.00..783,806.28 rows=9,656,582 width=45) (actual rows= loops=)

  • Filter: ("POINTS_TARGET" > '0'::numeric)
13. 0.000 0.000 ↓ 0.0

Hash (cost=1,346,922.71..1,346,922.71 rows=3,148,588 width=37) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Seq Scan on "GOAL_TRACKER" (cost=0.00..1,346,922.71 rows=3,148,588 width=37) (actual rows= loops=)

  • Filter: (("EFFECTIVE_FROM" >= date_trunc('MONTH'::text, (CURRENT_DATE - '1 mon'::interval month))) AND ("EFFECTIVE_FROM" <= date_trunc('day'::text, (CURRENT_DATE)::timestamp with time zone)))
15. 0.000 0.000 ↓ 0.0

Hash (cost=2.06..2.06 rows=2 width=27) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.00..2.06 rows=2 width=27) (actual rows= loops=)

  • Join Filter: ("GOAL"."GOAL_TYPE_ID" = "GOAL_TYPE"."ID")
17. 0.000 0.000 ↓ 0.0

Seq Scan on "GOAL" (cost=0.00..1.02 rows=2 width=14) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..1.01 rows=1 width=27) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Seq Scan on "GOAL_TYPE" (cost=0.00..1.01 rows=1 width=27) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Hash (cost=1.05..1.05 rows=5 width=39) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Seq Scan on "OBJECTIVE" (cost=0.00..1.05 rows=5 width=39) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Hash (cost=2.52..2.52 rows=13 width=57) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=1.23..2.52 rows=13 width=57) (actual rows= loops=)

  • Hash Cond: (("GOAL_TRACKER_STTS_TR"."GOAL_TRACKER_STATUS_ID" = "GOAL_TRACKER_STATUS"."ID") AND ("GOAL_TRACKER_STTS_TR"."TENANT" = "GOAL_TRACKER_STATUS"."TENANT"))
24. 0.000 0.000 ↓ 0.0

Seq Scan on "GOAL_TRACKER_STTS_TR" (cost=0.00..1.21 rows=13 width=46) (actual rows= loops=)

  • Filter: ("SYSTEM_LANGUAGE_ID" = ANY ('{60025,70025}'::numeric[]))
25. 0.000 0.000 ↓ 0.0

Hash (cost=1.09..1.09 rows=9 width=28) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Seq Scan on "GOAL_TRACKER_STATUS" (cost=0.00..1.09 rows=9 width=28) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Hash (cost=575,909.12..575,909.12 rows=2,554,692 width=43) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=431,202.68..575,909.12 rows=2,554,692 width=43) (actual rows= loops=)

  • Hash Cond: (("PERSON_GENDER"."ID" = "PERSON_GENDER_TR"."PERSON_GENDER_ID") AND ("PERSON_GENDER"."TENANT" = "PERSON_GENDER_TR"."TENANT") AND ("SYSTEM_LANGUAGE_PI"."ID" = "PERSON_GENDER_TR"."SYSTEM_LANGUAGE_ID"))
29. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=431,201.57..527,368.86 rows=2,554,692 width=55) (actual rows= loops=)

  • Merge Cond: ("AGREEMENT_PARTY"."PARTY_ID" = "STATUS_AGREEMENT_PARTY"."PARTY_ID")
30. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=178,431.89..233,398.88 rows=754,034 width=49) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

Gather Merge (cost=178,431.89..223,972.37 rows=377,017 width=42) (actual rows= loops=)

  • Workers Planned: 2
32. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=177,431.87..179,455.23 rows=157,090 width=42) (actual rows= loops=)

  • Merge Cond: ("PARTY"."ID" = "PARTY_REFERENCE"."PARTY_ASSIGNED_TO_ID")
33. 0.000 0.000 ↓ 0.0

Sort (cost=160,769.22..161,161.95 rows=157,090 width=42) (actual rows= loops=)

  • Sort Key: "PARTY"."ID
34. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=93,351.82..142,377.89 rows=157,090 width=42) (actual rows= loops=)

  • Hash Cond: ("PERSON"."PERSON_GENDER_ID" = "PERSON_GENDER"."ID")
35. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=93,350.77..141,090.67 rows=157,090 width=31) (actual rows= loops=)

  • Hash Cond: ("PARTY"."ID" = "PERSON"."PARTY_ID")
36. 0.000 0.000 ↓ 0.0

Hash Join (cost=65,903.39..106,954.46 rows=157,090 width=16) (actual rows= loops=)

  • Hash Cond: ("AGREEMENT_PARTY"."PARTY_ID" = "PARTY"."ID")
37. 0.000 0.000 ↓ 0.0

Hash Join (cost=41,294.70..78,658.41 rows=157,090 width=8) (actual rows= loops=)

  • Hash Cond: ("AGREEMENT_PARTY"."AGREEMENT_ID" = "AGREEMENT"."ID")
38. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on "AGREEMENT_PARTY" (cost=0.00..28,977.07 rows=392,726 width=16) (actual rows= loops=)

  • Filter: ("TENANT" = '25'::numeric)
39. 0.000 0.000 ↓ 0.0

Hash (cost=34,968.15..34,968.15 rows=385,564 width=8) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

Hash Join (cost=1.10..34,968.15 rows=385,564 width=8) (actual rows= loops=)

  • Hash Cond: ("AGREEMENT"."AGREEMENT_TYPE_ID" = "AGREEMENT_TYPE"."ID")
41. 0.000 0.000 ↓ 0.0

Seq Scan on "AGREEMENT" (cost=0.00..30,292.09 rows=963,909 width=15) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Hash (cost=1.07..1.07 rows=2 width=7) (actual rows= loops=)

43. 0.000 0.000 ↓ 0.0

Seq Scan on "AGREEMENT_TYPE" (cost=0.00..1.07 rows=2 width=7) (actual rows= loops=)

  • Filter: (("KEY" = '2'::numeric) OR ("KEY" = '1'::numeric))
44. 0.000 0.000 ↓ 0.0

Hash (cost=16,013.53..16,013.53 rows=523,853 width=8) (actual rows= loops=)

45. 0.000 0.000 ↓ 0.0

Seq Scan on "PARTY" (cost=0.00..16,013.53 rows=523,853 width=8) (actual rows= loops=)

46. 0.000 0.000 ↓ 0.0

Hash (cost=17,989.28..17,989.28 rows=515,128 width=23) (actual rows= loops=)

47. 0.000 0.000 ↓ 0.0

Seq Scan on "PERSON" (cost=0.00..17,989.28 rows=515,128 width=23) (actual rows= loops=)

48. 0.000 0.000 ↓ 0.0

Hash (cost=1.02..1.02 rows=2 width=18) (actual rows= loops=)

49. 0.000 0.000 ↓ 0.0

Seq Scan on "PERSON_GENDER" (cost=0.00..1.02 rows=2 width=18) (actual rows= loops=)

50. 0.000 0.000 ↓ 0.0

Materialize (cost=16,656.74..17,253.33 rows=119,317 width=16) (actual rows= loops=)

51. 0.000 0.000 ↓ 0.0

Sort (cost=16,656.74..16,955.03 rows=119,317 width=16) (actual rows= loops=)

  • Sort Key: "PARTY_REFERENCE"."PARTY_ASSIGNED_TO_ID
52. 0.000 0.000 ↓ 0.0

Seq Scan on "PARTY_REFERENCE" (cost=0.00..4,555.17 rows=119,317 width=16) (actual rows= loops=)

53. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..1.08 rows=2 width=7) (actual rows= loops=)

54. 0.000 0.000 ↓ 0.0

Seq Scan on "SYSTEM_LANGUAGE" "SYSTEM_LANGUAGE_PI" (cost=0.00..1.07 rows=2 width=7) (actual rows= loops=)

  • Filter: ((("LANGUAGE_TAG")::text = ANY ('{ja,ja_jp}'::text[])) AND ("TENANT" = '25'::numeric))
55. 0.000 0.000 ↓ 0.0

Materialize (cost=252,769.67..254,759.36 rows=397,938 width=14) (actual rows= loops=)

56. 0.000 0.000 ↓ 0.0

Sort (cost=252,769.67..253,764.52 rows=397,938 width=14) (actual rows= loops=)

  • Sort Key: "STATUS_AGREEMENT_PARTY"."PARTY_ID
57. 0.000 0.000 ↓ 0.0

Hash Join (cost=49,878.76..208,953.09 rows=397,938 width=14) (actual rows= loops=)

  • Hash Cond: ("AGREEMENT_VTLTY_STTS"."AGREEMENT_ID" = "STATUS_AGREEMENT_PARTY"."AGREEMENT_ID")
58. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.56..114,860.12 rows=391,488 width=14) (actual rows= loops=)

59. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.14..14.92 rows=4 width=13) (actual rows= loops=)

  • Join Filter: ("VITALITY_STATUS"."STATUS_TYPE_ID" = "STATUS_TYPE"."ID")
60. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.14..13.67 rows=4 width=14) (actual rows= loops=)

  • Join Filter: ("VITALITY_STATUS"."STATUS_CATEGORY_ID" = "STATUS_CATEGORY"."ID")
61. 0.000 0.000 ↓ 0.0

Index Scan using "VITALITY_STATUS_PK" on "VITALITY_STATUS" (cost=0.14..12.38 rows=16 width=21) (actual rows= loops=)

62. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..1.05 rows=1 width=7) (actual rows= loops=)

63. 0.000 0.000 ↓ 0.0

Seq Scan on "STATUS_CATEGORY" (cost=0.00..1.05 rows=1 width=7) (actual rows= loops=)

  • Filter: (("NAME")::text = 'Points Status'::text)
64. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..1.06 rows=4 width=13) (actual rows= loops=)

65. 0.000 0.000 ↓ 0.0

Seq Scan on "STATUS_TYPE" (cost=0.00..1.04 rows=4 width=13) (actual rows= loops=)

66. 0.000 0.000 ↓ 0.0

Index Scan using "AGREEMENT_VTLTY_STTS_03N" on "AGREEMENT_VTLTY_STTS" (cost=0.43..27,406.34 rows=130,496 width=15) (actual rows= loops=)

  • Index Cond: (("TENANT" = '25'::numeric) AND ("VITALITY_STATUS_ID" = "VITALITY_STATUS"."ID"))
  • Filter: ((CURRENT_DATE >= "EFFECTIVE_FROM") AND (CURRENT_DATE <= "EFFECTIVE_TO"))
67. 0.000 0.000 ↓ 0.0

Hash (cost=33,493.42..33,493.42 rows=942,542 width=16) (actual rows= loops=)

68. 0.000 0.000 ↓ 0.0

Seq Scan on "AGREEMENT_PARTY" "STATUS_AGREEMENT_PARTY" (cost=0.00..33,493.42 rows=942,542 width=16) (actual rows= loops=)

69. 0.000 0.000 ↓ 0.0

Hash (cost=1.04..1.04 rows=4 width=26) (actual rows= loops=)

70. 0.000 0.000 ↓ 0.0

Seq Scan on "PERSON_GENDER_TR" (cost=0.00..1.04 rows=4 width=26) (actual rows= loops=)

71. 0.000 0.000 ↓ 0.0

Hash (cost=1.03..1.03 rows=3 width=27) (actual rows= loops=)

72. 0.000 0.000 ↓ 0.0

Seq Scan on "OBJECTIVE_TRCKR_STTS" (cost=0.00..1.03 rows=3 width=27) (actual rows= loops=)

73. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..1.08 rows=2 width=7) (actual rows= loops=)

74. 0.000 0.000 ↓ 0.0

Seq Scan on "SYSTEM_LANGUAGE" (cost=0.00..1.07 rows=2 width=7) (actual rows= loops=)

  • Filter: ((("LANGUAGE_TAG")::text = ANY ('{ja,ja_jp}'::text[])) AND ("TENANT" = '25'::numeric))
75. 0.000 0.000 ↓ 0.0

Hash (cost=1.10..1.10 rows=10 width=54) (actual rows= loops=)

76. 0.000 0.000 ↓ 0.0

Seq Scan on "OBJECTIVE_TR" (cost=0.00..1.10 rows=10 width=54) (actual rows= loops=)