explain.depesz.com

PostgreSQL's explain analyze made readable

Result: va7u : Optimization for: plan #EYNY

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 15,475.919 351,094.435 ↑ 12.5 3,615,305 1

Unique (cost=26,707,140.55..28,284,477.22 rows=45,066,762 width=770) (actual time=332,945.164..351,094.435 rows=3,615,305 loops=1)

  • 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))
  • loops=1)
  • (actual time=0.005..0.006 rows=2 loops=1)
2. 191,759.733 335,618.516 ↑ 1.4 31,467,496 1

Sort (cost=26,707,140.55..26,819,807.45 rows=45,066,762 width=770) (actual time=332,945.162..335,618.516 rows=31,467,496 loops=1)

  • 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
  • Sort Method: quicksort Memory: 9,144,986kB
3. 90,750.194 143,858.783 ↑ 1.4 31,467,496 1

Hash Left Join (cost=1,852,746.57..5,574,223.64 rows=45,066,762 width=770) (actual time=20,534.980..143,858.783 rows=31,467,496 loops=1)

  • Hash Cond: (("OBJECTIVE"."ID" = "OBJECTIVE_TR"."OBJECTIVE_ID") AND ("OBJECTIVE"."TENANT" = "OBJECTIVE_TR"."TENANT") AND ("SYSTEM_LANGUAGE"."ID" = "OBJECTIVE_TR"."SYSTEM_LANGUAGE_ID"))
4. 9,714.022 53,108.578 ↑ 1.4 31,467,496 1

Nested Loop Left Join (cost=1,852,745.29..3,955,874.95 rows=45,066,762 width=203) (actual time=20,534.881..53,108.578 rows=31,467,496 loops=1)

5. 5,481.125 43,394.556 ↑ 1.4 15,733,748 1

Hash Left Join (cost=1,852,745.29..3,392,539.34 rows=22,533,381 width=196) (actual time=20,534.871..43,394.556 rows=15,733,748 loops=1)

  • Hash Cond: ("OBJECTIVE_TRACKER"."OBJECTIVE_TRCKR_STTS_ID" = "OBJECTIVE_TRCKR_STTS"."ID")
6. 7,755.678 37,913.421 ↑ 1.4 15,733,748 1

Hash Join (cost=1,852,744.22..3,249,826.86 rows=22,533,381 width=183) (actual time=20,534.855..37,913.421 rows=15,733,748 loops=1)

  • Hash Cond: ("GOAL_TRACKER"."PARTY_ID" = "PARTY"."ID")
7. 2,314.897 24,178.330 ↓ 1.6 7,230,610 1

Hash Left Join (cost=1,302,905.74..2,434,228.22 rows=4,620,154 width=156) (actual time=14,541.460..24,178.330 rows=7,230,610 loops=1)

  • Hash Cond: ("GOAL_TRACKER"."GOAL_TRACKER_STATUS_ID" = "GOAL_TRACKER_STATUS"."ID")
8. 1,333.324 21,863.366 ↓ 1.1 3,615,305 1

Hash Left Join (cost=1,302,903.06..2,376,029.37 rows=3,198,568 width=113) (actual time=14,541.382..21,863.366 rows=3,615,305 loops=1)

  • Hash Cond: ("OBJECTIVE_TRACKER"."OBJECTIVE_ID" = "OBJECTIVE"."ID")
9. 1,298.605 20,530.032 ↓ 1.1 3,615,305 1

Hash Left Join (cost=1,302,901.95..2,360,515.20 rows=3,198,568 width=81) (actual time=14,541.364..20,530.032 rows=3,615,305 loops=1)

  • Hash Cond: ("GOAL_TRACKER"."GOAL_ID" = "GOAL"."ID")
10. 9,633.725 19,231.407 ↓ 1.1 3,615,305 1

Hash Join (cost=1,302,899.86..2,316,532.80 rows=3,198,568 width=68) (actual time=14,541.316..19,231.407 rows=3,615,305 loops=1)

  • Hash Cond: ("OBJECTIVE_TRACKER"."GOAL_TRACKER_ID" = "GOAL_TRACKER"."ID")
11. 5,451.299 5,451.299 ↑ 1.0 23,148,718 1

Seq Scan on "OBJECTIVE_TRACKER" (cost=0.00..952,796.46 rows=23,175,797 width=45) (actual time=0.015..5,451.299 rows=23,148,718 loops=1)

  • Filter: ("POINTS_TARGET" > '0'::numeric)
12. 1,207.978 4,146.383 ↓ 1.1 3,546,619 1

Hash (cost=1,263,542.51..1,263,542.51 rows=3,148,588 width=37) (actual time=4,146.383..4,146.383 rows=3,546,619 loops=1)

  • Buckets: 4,194,304 Batches: 1 Memory Usage: 282,139kB
13. 0.000 2,938.405 ↓ 1.1 3,546,619 1

Gather (cost=1,000.00..1,263,542.51 rows=3,148,588 width=37) (actual time=1,984.479..2,938.405 rows=3,546,619 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
14. 3,097.883 3,097.883 ↑ 1.1 1,182,206 3 / 3

Parallel Seq Scan on "GOAL_TRACKER" (cost=0.00..947,683.71 rows=1,311,912 width=37) (actual time=1,980.747..3,097.883 rows=1,182,206 loops=3)

  • 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)))
  • Rows Removed by Filter: 6,414,394
15. 0.005 0.020 ↑ 1.0 2 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
16. 0.005 0.015 ↑ 1.0 2 1

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

  • Join Filter: ("GOAL"."GOAL_TYPE_ID" = "GOAL_TYPE"."ID")
17. 0.004 0.004 ↑ 1.0 2 1

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

18. 0.004 0.006 ↑ 1.0 1 2

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

19. 0.002 0.002 ↑ 1.0 1 1

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

20. 0.006 0.010 ↑ 1.0 5 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
21. 0.004 0.004 ↑ 1.0 5 1

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

22. 0.020 0.067 ↓ 1.3 17 1

Hash (cost=2.52..2.52 rows=13 width=57) (actual time=0.067..0.067 rows=17 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
23. 0.021 0.047 ↓ 1.3 17 1

Hash Right Join (cost=1.23..2.52 rows=13 width=57) (actual time=0.034..0.047 rows=17 loops=1)

  • 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.012 0.012 ↓ 1.3 17 1

Seq Scan on "GOAL_TRACKER_STTS_TR" (cost=0.00..1.21 rows=13 width=46) (actual time=0.007..0.012 rows=17 loops=1)

  • Filter: ("SYSTEM_LANGUAGE_ID" = ANY ('{60025,70025}'::numeric[]))
25. 0.009 0.014 ↑ 1.0 9 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
26. 0.005 0.005 ↑ 1.0 9 1

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

27. 283.897 5,979.413 ↑ 3.0 840,900 1

Hash (cost=517,901.83..517,901.83 rows=2,554,932 width=43) (actual time=5,979.413..5,979.413 rows=840,900 loops=1)

  • Buckets: 4,194,304 Batches: 1 Memory Usage: 100,253kB
28. 395.007 5,695.516 ↑ 3.0 840,900 1

Hash Left Join (cost=416,373.08..517,901.83 rows=2,554,932 width=43) (actual time=4,680.281..5,695.516 rows=840,900 loops=1)

  • 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. 189.549 5,300.500 ↑ 3.0 840,900 1

Nested Loop Left Join (cost=416,371.97..469,357.01 rows=2,554,932 width=55) (actual time=4,680.255..5,300.500 rows=840,900 loops=1)

30. 310.561 5,110.951 ↑ 3.0 420,450 1

Merge Left Join (cost=416,371.97..437,419.28 rows=1,277,466 width=48) (actual time=4,680.231..5,110.951 rows=420,450 loops=1)

  • Merge Cond: ("AGREEMENT_PARTY"."PARTY_ID" = "STATUS_AGREEMENT_PARTY"."PARTY_ID")
31. 258.883 2,649.229 ↓ 1.1 420,444 1

Sort (cost=183,430.64..184,373.30 rows=377,064 width=42) (actual time=2,598.821..2,649.229 rows=420,444 loops=1)

  • Sort Key: "PARTY"."ID
  • Sort Method: quicksort Memory: 71,411kB
32. 125.709 2,390.346 ↓ 1.1 420,444 1

Hash Left Join (cost=121,790.06..148,506.13 rows=377,064 width=42) (actual time=1,867.535..2,390.346 rows=420,444 loops=1)

  • Hash Cond: ("PERSON"."PERSON_GENDER_ID" = "PERSON_GENDER"."ID")
33. 338.683 2,264.626 ↓ 1.1 420,444 1

Hash Right Join (cost=121,789.02..145,417.87 rows=377,064 width=31) (actual time=1,867.511..2,264.626 rows=420,444 loops=1)

  • Hash Cond: ("PERSON"."PARTY_ID" = "PARTY"."ID")
34. 60.214 60.214 ↑ 1.0 514,489 1

Seq Scan on "PERSON" (cost=0.00..17,989.28 rows=515,128 width=23) (actual time=0.007..60.214 rows=514,489 loops=1)

35. 110.029 1,865.729 ↓ 1.1 420,444 1

Hash (cost=117,075.72..117,075.72 rows=377,064 width=16) (actual time=1,865.729..1,865.729 rows=420,444 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 24,626kB
36. 1,742.401 1,755.700 ↓ 1.1 420,444 1

Hash Right Join (cost=111,214.28..117,075.72 rows=377,064 width=16) (actual time=1,598.446..1,755.700 rows=420,444 loops=1)

  • Hash Cond: ("PARTY_REFERENCE"."PARTY_ASSIGNED_TO_ID" = "PARTY"."ID")
  • -> Hash (cost=106,500.98..106500.98 rows=377,064 width=16) (actual time=1,596.679..1596.679 rows=419,307
37. 0.000 13.299 ↓ 1.0 119,325 1

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

  • Buckets: 524,288 Batches: 1 Memory Usage: 24,570kB
38. 247.082 1,461.261 ↓ 1.1 419,307 1

Hash Join (cost=62,351.31..106,500.98 rows=377,064 width=16) (actual time=612.319..1,461.261 rows=419,307 loops=1)

  • Hash Cond: ("AGREEMENT_PARTY"."PARTY_ID" = "PARTY"."ID")
39. 400.129 1,013.687 ↓ 1.1 419,307 1

Hash Join (cost=39,789.62..82,949.48 rows=377,064 width=8) (actual time=410.081..1,013.687 rows=419,307 loops=1)

  • Hash Cond: ("AGREEMENT_PARTY"."AGREEMENT_ID" = "AGREEMENT"."ID")
40. 205.407 205.407 ↑ 1.0 942,049 1

Seq Scan on "AGREEMENT_PARTY" (cost=0.00..35,854.25 rows=942,660 width=16) (actual time=0.012..205.407 rows=942,049 loops=1)

  • Filter: ("TENANT" = '25'::numeric)
41. 115.744 408.151 ↓ 1.1 424,363 1

Hash (cost=34,969.84..34,969.84 rows=385,582 width=8) (actual time=408.151..408.151 rows=424,363 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 21,084kB
42. 292.396 292.407 ↓ 1.1 424,363 1

Hash Join (cost=1.10..34,969.84 rows=385,582 width=8) (actual time=0.099..292.407 rows=424,363 loops=1)

  • Hash Cond: ("AGREEMENT"."AGREEMENT_TYPE_ID" = "AGREEMENT_TYPE"."ID")
  • -> Seq Scan on "AGREEMENT" (cost=0.00..30293.56 rows=963,956 width=15)(actual time=0.004..102.500 rows=963,232 loops=1)
43. 0.011 0.011 ↑ 1.0 2 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
44. 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))
  • Rows Removed by Filter: 3
45. 125.227 200.492 ↑ 1.0 521,516 1

Hash (cost=16,013.53..16,013.53 rows=523,853 width=8) (actual time=200.492..200.492 rows=521,516 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 24,977kB
46. 75.265 75.265 ↑ 1.0 521,516 1

Seq Scan on "PARTY" (cost=0.00..16,013.53 rows=523,853 width=8) (actual time=0.009..75.265 rows=521,516 loops=1)

47. 0.008 0.011 ↑ 1.0 2 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
48. 0.003 0.003 ↑ 1.0 2 1

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

49. 293.801 2,151.161 ↓ 1.3 536,499 1

Sort (cost=232,941.33..233,936.17 rows=397,938 width=14) (actual time=2,081.398..2,151.161 rows=536,499 loops=1)

  • Sort Key: "STATUS_AGREEMENT_PARTY"."PARTY_ID
  • Sort Method: quicksort Memory: 36,321kB
50. 345.398 1,857.360 ↓ 1.3 512,703 1

Hash Join (cost=45,281.42..195,928.75 rows=397,938 width=14) (actual time=465.924..1,857.360 rows=512,703 loops=1)

  • Hash Cond: ("AGREEMENT_VTLTY_STTS"."AGREEMENT_ID" = "STATUS_AGREEMENT_PARTY"."AGREEMENT_ID")
51. 59.468 1,049.555 ↓ 1.3 515,900 1

Nested Loop (cost=0.56..114,860.12 rows=391,488 width=14) (actual time=0.099..1,049.555 rows=515,900 loops=1)

52. 0.008 0.075 ↑ 1.0 4 1

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

  • Join Filter: ("VITALITY_STATUS"."STATUS_TYPE_ID" = "STATUS_TYPE"."ID")
  • Rows Removed by Join Filter: 6
53. 0.011 0.059 ↑ 1.0 4 1

Nested Loop (cost=0.14..13.67 rows=4 width=14) (actual time=0.043..0.059 rows=4 loops=1)

  • Join Filter: ("VITALITY_STATUS"."STATUS_CATEGORY_ID" = "STATUS_CATEGORY"."ID")
  • Rows Removed by Join Filter: 12
54. 0.032 0.032 ↑ 1.0 16 1

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

55. 0.008 0.016 ↑ 1.0 1 16

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

56. 0.008 0.008 ↑ 1.0 1 1

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

  • Filter: (("NAME")::text = 'Points Status'::text)
  • Rows Removed by Filter: 3
57. 0.004 0.008 ↑ 2.0 2 4

Materialize (cost=0.00..1.06 rows=4 width=13) (actual time=0.001..0.002 rows=2 loops=4)

58. 0.004 0.004 ↑ 1.0 4 1

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

59. 990.012 990.012 ↑ 1.0 128,975 4

Index Scan using "AGREEMENT_VTLTY_STTS_03N" on "AGREEMENT_VTLTY_STTS" (cost=0.43..27,406.34 rows=130,496 width=15) (actual time=0.041..247.503 rows=128,975 loops=4)

  • Index Cond: (("TENANT" = '25'::numeric) AND ("VITALITY_STATUS_ID" = "VITALITY_STATUS"."ID"))
  • Filter: ((CURRENT_DATE >= "EFFECTIVE_FROM") AND (CURRENT_DATE <= "EFFECTIVE_TO"))
  • Rows Removed by Filter: 336,561
60. 280.317 462.407 ↑ 1.0 942,049 1

Hash (cost=33,497.60..33,497.60 rows=942,660 width=16) (actual time=462.407..462.407 rows=942,049 loops=1)

  • Buckets: 1,048,576 Batches: 1 Memory Usage: 54,191kB
61. 182.090 182.090 ↑ 1.0 942,049 1

Seq Scan on "AGREEMENT_PARTY" "STATUS_AGREEMENT_PARTY" (cost=0.00..33,497.60 rows=942,660 width=16) (actual time=0.007..182.090 rows=942,049 loops=1)

62. 0.000 0.000 ↑ 1.0 2 420,450

Materialize (cost=0.00..1.08 rows=2 width=7) (actual time=0.000..0.000 rows=2 loops=420,450)

63. 0.016 0.016 ↑ 1.0 2 1

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

  • Filter: ((("LANGUAGE_TAG")::text = ANY ('{ja,ja_jp}'::text[])) AND ("TENANT" = '25'::numeric))
  • Rows Removed by Filter: 3
64. 0.005 0.009 ↑ 1.0 4 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
65. 0.004 0.004 ↑ 1.0 4 1

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

66. 0.007 0.010 ↑ 1.0 3 1

Hash (cost=1.03..1.03 rows=3 width=27) (actual time=0.009..0.010 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
67. 0.003 0.003 ↑ 1.0 3 1

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

68. 0.000 0.000 ↑ 1.0 2 15,733,748

Materialize (cost=0.00..1.08 rows=2 width=7) (actual time=0.000..0.000 rows=2 loops=15,733,748)

69. 0.009 0.009 ↑ 1.0 2 1

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

  • Filter: ((("LANGUAGE_TAG")::text = ANY ('{ja,ja_jp}'::text[])) AND ("TENANT" = '25'::numeric))
  • Rows Removed by Filter: 3
70. 0.006 0.011 ↑ 1.0 10 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
71. 0.005 0.005 ↑ 1.0 10 1

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

Planning time : 7.728 ms
Execution time : 351,662.801 ms