explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EYNY

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 7,479.098 270,242.720 ↑ 12.5 3,615,305 1

Unique (cost=14,547,423.95..15,336,092.28 rows=45,066,762 width=180) (actual time=255,546.919..270,242.720 rows=3,615,305 loops=1)

  • rows=424,363 loops=1)
2. 180,018.492 262,763.622 ↑ 1.4 31,467,496 1

Sort (cost=14,547,423.95..14,660,090.85 rows=45,066,762 width=180) (actual time=255,546.918..262,763.622 rows=31,467,496 loops=1)

  • Sort Key: (("PARTY"."ID")::text), (("OBJECTIVE_TRACKER"."POINTS_TARGET")::text), "OBJECTIVE_TRCKR_STTS"."NAME", (("OBJECTIVE_TRACKER"."POINTS_ACHIEVED")::text), (to_char("OBJECTIVE_TRACKER"."EFFECTIVE_TO", 'YYYYMMDD'::text)), (to_char("GOAL_TRACKER"."EFFECTIVE_TO", 'YYYYMMDD'::text))
  • Sort Method: external merge Disk: 2,068,576kB
3. 42,507.125 82,745.130 ↑ 1.4 31,467,496 1

Nested Loop Left Join (cost=1,852,741.95..4,813,226.54 rows=45,066,762 width=180) (actual time=20,880.881..82,745.130 rows=31,467,496 loops=1)

4. 4,763.889 40,238.005 ↑ 1.4 15,733,748 1

Hash Left Join (cost=1,852,741.95..3,348,555.69 rows=22,533,381 width=64) (actual time=20,880.858..40,238.005 rows=15,733,748 loops=1)

  • Hash Cond: ("OBJECTIVE_TRACKER"."OBJECTIVE_TRCKR_STTS_ID" = "OBJECTIVE_TRCKR_STTS"."ID")
5. 6,456.789 35,474.107 ↑ 1.4 15,733,748 1

Hash Join (cost=1,852,740.88..3,205,843.21 rows=22,533,381 width=51) (actual time=20,880.839..35,474.107 rows=15,733,748 loops=1)

  • Hash Cond: ("GOAL_TRACKER"."PARTY_ID" = "PARTY"."ID")
6. 1,959.231 23,140.192 ↓ 1.6 7,230,610 1

Hash Left Join (cost=1,302,903.66..2,390,245.83 rows=4,620,154 width=51) (actual time=14,989.538..23,140.192 rows=7,230,610 loops=1)

  • Hash Cond: ("GOAL_TRACKER"."GOAL_TRACKER_STATUS_ID" = "GOAL_TRACKER_STATUS"."ID")
7. 1,291.580 21,180.903 ↓ 1.1 3,615,305 1

Hash Left Join (cost=1,302,900.98..2,332,046.98 rows=3,198,568 width=58) (actual time=14,989.472..21,180.903 rows=3,615,305 loops=1)

  • Hash Cond: ("OBJECTIVE_TRACKER"."OBJECTIVE_ID" = "OBJECTIVE"."ID")
8. 9,779.343 19,889.309 ↓ 1.1 3,615,305 1

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

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

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

  • Filter: ("POINTS_TARGET" > '0'::numeric)
10. 1,315.677 4,468.830 ↓ 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,468.830..4,468.830 rows=3,546,619 loops=1)

  • Buckets: 4,194,304 Batches: 1 Memory Usage: 282,139kB
11. 35.896 3,153.153 ↓ 1.1 3,546,619 1

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

  • Workers Planned: 2
  • Workers Launched: 2
12. 3,117.257 3,117.257 ↑ 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,994.561..3,117.257 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
13. 0.008 0.014 ↑ 1.0 5 1

Hash (cost=1.05..1.05 rows=5 width=12) (actual time=0.013..0.014 rows=5 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
14. 0.006 0.006 ↑ 1.0 5 1

Seq Scan on "OBJECTIVE" (cost=0.00..1.05 rows=5 width=12) (actual time=0.005..0.006 rows=5 loops=1)

15. 0.007 0.058 ↓ 1.3 17 1

Hash (cost=2.52..2.52 rows=13 width=7) (actual time=0.058..0.058 rows=17 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
16. 0.016 0.051 ↓ 1.3 17 1

Hash Right Join (cost=1.23..2.52 rows=13 width=7) (actual time=0.038..0.051 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"))
17. 0.010 0.010 ↓ 1.3 17 1

Seq Scan on "GOAL_TRACKER_STTS_TR" (cost=0.00..1.21 rows=13 width=12) (actual time=0.005..0.010 rows=17 loops=1)

  • Filter: ("SYSTEM_LANGUAGE_ID" = ANY ('{60025,70025}'::numeric[]))
18. 0.017 0.025 ↑ 1.0 9 1

Hash (cost=1.09..1.09 rows=9 width=12) (actual time=0.025..0.025 rows=9 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
19. 0.008 0.008 ↑ 1.0 9 1

Seq Scan on "GOAL_TRACKER_STATUS" (cost=0.00..1.09 rows=9 width=12) (actual time=0.006..0.008 rows=9 loops=1)

20. 237.604 5,877.126 ↑ 3.0 840,900 1

Hash (cost=517,900.58..517,900.58 rows=2,554,932 width=16) (actual time=5,877.126..5,877.126 rows=840,900 loops=1)

  • Buckets: 4,194,304 Batches: 1 Memory Usage: 73,828kB
21. 393.982 5,639.522 ↑ 3.0 840,900 1

Hash Left Join (cost=416,371.83..517,900.58 rows=2,554,932 width=16) (actual time=4,655.408..5,639.522 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"))
22. 189.596 5,245.530 ↑ 3.0 840,900 1

Nested Loop Left Join (cost=416,370.72..469,355.76 rows=2,554,932 width=35) (actual time=4,655.378..5,245.530 rows=840,900 loops=1)

23. 286.434 5,055.934 ↑ 3.0 420,450 1

Merge Left Join (cost=416,370.72..437,418.03 rows=1,277,466 width=28) (actual time=4,655.353..5,055.934 rows=420,450 loops=1)

  • Merge Cond: ("AGREEMENT_PARTY"."PARTY_ID" = "STATUS_AGREEMENT_PARTY"."PARTY_ID")
24. 232.498 2,616.973 ↓ 1.1 420,444 1

Sort (cost=183,430.64..184,373.30 rows=377,064 width=28) (actual time=2,573.823..2,616.973 rows=420,444 loops=1)

  • Sort Key: "PARTY"."ID
  • Sort Method: quicksort Memory: 45,136kB
25. 123.922 2,384.475 ↓ 1.1 420,444 1

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

  • Hash Cond: ("PERSON"."PERSON_GENDER_ID" = "PERSON_GENDER"."ID")
26. 335.237 2,260.544 ↓ 1.1 420,444 1

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

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

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

28. 257.661 1,864.936 ↓ 1.1 420,444 1

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

  • Buckets: 524,288 Batches: 1 Memory Usage: 24,626kB
  • -> Hash Right Join (cost=111,214.28..117075.72 rows=377,064 width=16) (actual time=1,595.957..1753.771 rows=420,444loops=1)
  • Hash Cond: ("PARTY_REFERENCE"."PARTY_ASSIGNED_TO_ID" = "PARTY"."ID")
29. 13.100 13.100 ↓ 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.100 rows=119,325 loops=1)

30. 137.238 1,594.175 ↓ 1.1 419,307 1

Hash (cost=106,500.98..106,500.98 rows=377,064 width=16) (actual time=1,594.175..1,594.175 rows=419,307 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 24,570kB
31. 248.435 1,456.937 ↓ 1.1 419,307 1

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

  • Hash Cond: ("AGREEMENT_PARTY"."PARTY_ID" = "PARTY"."ID")
32. 403.681 1,011.631 ↓ 1.1 419,307 1

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

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

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

  • Filter: ("TENANT" = '25'::numeric)
34. 300.073 402.295 ↓ 1.1 424,363 1

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

  • Buckets: 524,288 Batches: 1 Memory Usage: 21,084kB
  • -> Hash Join (cost=1.10..34969.84 rows=385,582 width=8) (actual time=0.102..292.156
  • Hash Cond: ("AGREEMENT"."AGREEMENT_TYPE_ID" = "AGREEMENT_TYPE"."ID")
35. 102.210 102.210 ↑ 1.0 963,232 1

Seq Scan on "AGREEMENT" (cost=0.00..30,293.56 rows=963,956 width=15) (actual time=0.004..102.210 rows=963,232 loops=1)

36. 0.005 0.012 ↑ 1.0 2 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
37. 0.007 0.007 ↑ 1.0 2 1

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

  • Filter: (("KEY" = '2'::numeric) OR ("KEY" = '1'::numeric))
  • Rows Removed by Filter: 3
38. 122.027 196.871 ↑ 1.0 521,516 1

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

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

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

40. 0.005 0.009 ↑ 1.0 2 1

Hash (cost=1.02..1.02 rows=2 width=12) (actual time=0.009..0.009 rows=2 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
41. 0.004 0.004 ↑ 1.0 2 1

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

42. 282.780 2,152.527 ↓ 1.3 536,499 1

Sort (cost=232,940.07..233,934.92 rows=397,938 width=8) (actual time=2,081.518..2,152.527 rows=536,499 loops=1)

  • Sort Key: "STATUS_AGREEMENT_PARTY"."PARTY_ID
  • Sort Method: quicksort Memory: 36,321kB
43. 347.277 1,869.747 ↓ 1.3 512,703 1

Hash Join (cost=45,281.42..195,927.49 rows=397,938 width=8) (actual time=472.013..1,869.747 rows=512,703 loops=1)

  • Hash Cond: ("AGREEMENT_VTLTY_STTS"."AGREEMENT_ID" = "STATUS_AGREEMENT_PARTY"."AGREEMENT_ID")
44. 57.407 1,054.077 ↓ 1.3 515,900 1

Nested Loop (cost=0.56..114,858.86 rows=391,488 width=8) (actual time=0.099..1,054.077 rows=515,900 loops=1)

45. 0.011 0.062 ↑ 1.0 4 1

Nested Loop (cost=0.14..13.67 rows=4 width=7) (actual time=0.047..0.062 rows=4 loops=1)

  • Join Filter: ("VITALITY_STATUS"."STATUS_CATEGORY_ID" = "STATUS_CATEGORY"."ID")
  • Rows Removed by Join Filter: 12
46. 0.035 0.035 ↑ 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.027..0.035 rows=16 loops=1)

47. 0.010 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)

48. 0.006 0.006 ↑ 1.0 1 1

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

  • Filter: (("NAME")::text = 'Points Status'::text)
  • Rows Removed by Filter: 3
49. 996.608 996.608 ↑ 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.042..249.152 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
50. 286.583 468.393 ↑ 1.0 942,049 1

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

  • Buckets: 1,048,576 Batches: 1 Memory Usage: 54,191kB
51. 181.810 181.810 ↑ 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.006..181.810 rows=942,049 loops=1)

52. 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)

53. 0.017 0.017 ↑ 1.0 2 1

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

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

Hash (cost=1.04..1.04 rows=4 width=19) (actual time=0.010..0.010 rows=4 loops=1)

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

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

56. 0.006 0.009 ↑ 1.0 3 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
57. 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.003..0.003 rows=3 loops=1)

58. 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)

59. 0.005 0.005 ↑ 1.0 2 1

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

  • Filter: ((("LANGUAGE_TAG")::text = ANY ('{ja,ja_jp}'::text[])) AND ("TENANT" = '25'::numeric))
  • Rows Removed by Filter: 3
Planning time : 5.666 ms
Execution time : 270,653.218 ms