explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iKgp

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 143,700.345 147,095.992 ↓ 11.7 758,103 1

Sort (cost=103,098.28..103,259.84 rows=64,625 width=2,719) (actual time=134,396.496..147,095.992 rows=758,103 loops=1)

  • Sort Key: a.id, a0.request_id, a0.document_id, a1.approval_request_id, a1.connection_request_id, i.id, i0.id, r.check_id, r.document_id, f0.id, f1.approval_id, f1.document_id, p.id, p0.approval_id, p0.document_id, g0.approval_id, g0.document_id, a3.id, a4.history_id, a4.document_id, a5.id, a5.form_id, a6.activity_group_id, o.id, o0.id, a10.id, r0.id
  • Sort Method: external merge Disk: 1,052,568kB
  • Buffers: shared hit=571, temp read=273,694 written=273,690
2. 1,076.777 3,395.647 ↓ 11.7 758,103 1

Hash Join (cost=17,775.1..22,170.31 rows=64,625 width=2,719) (actual time=167.418..3,395.647 rows=758,103 loops=1)

  • Buffers: shared hit=571, temp read=2,049 written=2,045
3. 1,093.278 2,316.972 ↓ 10.0 769,898 1

Hash Join (cost=1,279.97..4,752.59 rows=76,723 width=2,719) (actual time=165.456..2,316.972 rows=769,898 loops=1)

  • Buffers: shared hit=536, temp read=2,049 written=2,045
4. 1,005.492 1,219.036 ↓ 6.2 478,771 1

Hash Join (cost=1,199.53..3,617.21 rows=76,723 width=2,414) (actual time=160.784..1,219.036 rows=478,771 loops=1)

  • Buffers: shared hit=512, temp read=2,049 written=2,045
5. 13.038 75.774 ↑ 1.8 11,385 1

Hash Join (cost=610.78..2,110.01 rows=20,165 width=1,314) (actual time=22.859..75.774 rows=11,385 loops=1)

  • Buffers: shared hit=368
6. 12.053 62.216 ↑ 1.8 10,977 1

Hash Join (cost=576.5..1,182.43 rows=20,165 width=1,113) (actual time=22.323..62.216 rows=10,977 loops=1)

  • Buffers: shared hit=365
7. 10.130 35.939 ↑ 1.0 7,635 1

Hash Join (cost=261.72..766.35 rows=7,647 width=892) (actual time=8.062..35.939 rows=7,635 loops=1)

  • Buffers: shared hit=301
8. 12.463 25.391 ↑ 1.0 7,635 1

Hash Join (cost=248.06..647.54 rows=7,647 width=346) (actual time=7.616..25.391 rows=7,635 loops=1)

  • Buffers: shared hit=293
9. 5.395 5.395 ↑ 1.0 7,710 1

Seq Scan on activity_form a6 (cost=0..294.1 rows=7,710 width=282) (actual time=0.006..5.395 rows=7,710 loops=1)

  • Buffers: shared hit=217
10. 3.913 7.533 ↑ 1.0 7,635 1

Hash (cost=152.47..152.47 rows=7,647 width=64) (actual time=7.533..7.533 rows=7,635 loops=1)

  • Buffers: shared hit=76
11. 3.620 3.620 ↑ 1.0 7,635 1

Seq Scan on activity a5 (cost=0..152.47 rows=7,647 width=64) (actual time=0.018..3.62 rows=7,635 loops=1)

  • Buffers: shared hit=76
12. 0.114 0.418 ↑ 1.0 100 1

Hash (cost=12.41..12.41 rows=100 width=546) (actual time=0.418..0.418 rows=100 loops=1)

  • Buffers: shared hit=8
13. 0.089 0.304 ↑ 1.0 100 1

Hash Join (cost=6.25..12.41 rows=100 width=546) (actual time=0.175..0.304 rows=100 loops=1)

  • Buffers: shared hit=8
14. 0.064 0.064 ↑ 1.0 100 1

Seq Scan on activity_group a7 (cost=0..5 rows=100 width=273) (actual time=0.017..0.064 rows=100 loops=1)

  • Buffers: shared hit=4
15. 0.091 0.151 ↑ 1.0 100 1

Hash (cost=5..5 rows=100 width=273) (actual time=0.151..0.151 rows=100 loops=1)

  • Buffers: shared hit=4
16. 0.060 0.060 ↑ 1.0 100 1

Seq Scan on activity_group a8 (cost=0..5 rows=100 width=273) (actual time=0.003..0.06 rows=100 loops=1)

  • Buffers: shared hit=4
17. 3.403 14.224 ↑ 1.0 5,398 1

Hash (cost=247.3..247.3 rows=5,398 width=221) (actual time=14.224..14.224 rows=5,398 loops=1)

  • Buffers: shared hit=64
18. 5.457 10.821 ↑ 1.0 5,398 1

Hash Join (cost=68.1..247.3 rows=5,398 width=221) (actual time=3.438..10.821 rows=5,398 loops=1)

  • Buffers: shared hit=64
19. 1.943 1.943 ↑ 1.0 5,398 1

Seq Scan on order_cost o0 (cost=0..104.98 rows=5,398 width=41) (actual time=0.006..1.943 rows=5,398 loops=1)

  • Buffers: shared hit=51
20. 1.039 3.421 ↑ 1.0 2,047 1

Hash (cost=42.51..42.51 rows=2,047 width=180) (actual time=3.421..3.421 rows=2,047 loops=1)

  • Buffers: shared hit=13
21. 1.607 2.382 ↑ 1.0 2,047 1

Hash Join (cost=1.94..42.51 rows=2,047 width=180) (actual time=0.084..2.382 rows=2,047 loops=1)

  • Buffers: shared hit=13
22. 0.714 0.714 ↑ 1.0 2,047 1

Seq Scan on activity_order a9 (cost=0..32.47 rows=2,047 width=16) (actual time=0.012..0.714 rows=2,047 loops=1)

  • Buffers: shared hit=12
23. 0.046 0.061 ↑ 1.0 42 1

Hash (cost=1.42..1.42 rows=42 width=164) (actual time=0.061..0.061 rows=42 loops=1)

  • Buffers: shared hit=1
24. 0.015 0.015 ↑ 1.0 42 1

Seq Scan on order_technical_params o (cost=0..1.42 rows=42 width=164) (actual time=0.01..0.015 rows=42 loops=1)

  • Buffers: shared hit=1
25. 0.166 0.520 ↑ 1.6 269 1

Hash (cost=29.03..29.03 rows=420 width=201) (actual time=0.52..0.52 rows=269 loops=1)

  • Buffers: shared hit=3
26. 0.138 0.354 ↑ 1.6 269 1

Hash Join (cost=9.05..29.03 rows=420 width=201) (actual time=0.223..0.354 rows=269 loops=1)

  • Buffers: shared hit=3
27. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on report_technical_params r0 (cost=0..14.2 rows=420 width=164) (actual time=0.002..0.002 rows=0 loops=1)

28. 0.112 0.214 ↑ 1.0 269 1

Hash (cost=5.69..5.69 rows=269 width=37) (actual time=0.214..0.214 rows=269 loops=1)

  • Buffers: shared hit=3
29. 0.102 0.102 ↑ 1.0 269 1

Seq Scan on activity_report a10 (cost=0..5.69 rows=269 width=37) (actual time=0.011..0.102 rows=269 loops=1)

  • Buffers: shared hit=3
30. 68.023 137.770 ↓ 7.5 25,687 1

Hash (cost=545.9..545.9 rows=3,428 width=1,100) (actual time=137.77..137.77 rows=25,687 loops=1)

  • Buffers: shared hit=144, temp written=1,260
31. 35.898 69.747 ↓ 7.5 25,687 1

Hash Join (cost=438.92..545.9 rows=3,428 width=1,100) (actual time=29.061..69.747 rows=25,687 loops=1)

  • Buffers: shared hit=144
32. 4.035 8.819 ↓ 1.1 3,672 1

Hash Join (cost=128.92..189.48 rows=3,241 width=192) (actual time=4.019..8.819 rows=3,672 loops=1)

  • Buffers: shared hit=71
33. 0.806 0.806 ↑ 1.0 1,907 1

Seq Scan on approval_request_history_document a4 (cost=0..34.18 rows=1,918 width=32) (actual time=0.007..0.806 rows=1,907 loops=1)

  • Buffers: shared hit=15
34. 2.041 3.978 ↑ 1.0 3,234 1

Hash (cost=88.41..88.41 rows=3,241 width=160) (actual time=3.978..3.978 rows=3,234 loops=1)

  • Buffers: shared hit=56
35. 1.937 1.937 ↑ 1.0 3,234 1

Seq Scan on approval_request_history a3 (cost=0..88.41 rows=3,241 width=160) (actual time=0.013..1.937 rows=3,234 loops=1)

  • Buffers: shared hit=56
36. 3.467 25.030 ↓ 2.1 2,028 1

Hash (cost=298.08..298.08 rows=953 width=908) (actual time=25.03..25.03 rows=2,028 loops=1)

  • Buffers: shared hit=73
37. 2.971 21.563 ↓ 2.1 2,028 1

Hash Join (cost=262.52..298.08 rows=953 width=908) (actual time=17.314..21.563 rows=2,028 loops=1)

  • Buffers: shared hit=73
38. 1.010 2.509 ↓ 1.0 995 1

Hash Join (cost=34.44..56.9 rows=953 width=116) (actual time=1.214..2.509 rows=995 loops=1)

  • Buffers: shared hit=19
39. 0.324 0.324 ↑ 1.0 687 1

Seq Scan on preliminary_approval_resolution_document p0 (cost=0..12.93 rows=693 width=32) (actual time=0.015..0.324 rows=687 loops=1)

  • Buffers: shared hit=6
40. 0.603 1.175 ↑ 1.0 944 1

Hash (cost=22.53..22.53 rows=953 width=84) (actual time=1.175..1.175 rows=944 loops=1)

  • Buffers: shared hit=13
41. 0.572 0.572 ↑ 1.0 944 1

Seq Scan on preliminary_approval p (cost=0..22.53 rows=953 width=84) (actual time=0.013..0.572 rows=944 loops=1)

  • Buffers: shared hit=13
42. 1.759 16.083 ↓ 1.2 1,105 1

Hash (cost=216.82..216.82 rows=901 width=792) (actual time=16.083..16.083 rows=1,105 loops=1)

  • Buffers: shared hit=54
43. 1.112 14.324 ↓ 1.2 1,105 1

Hash Join (cost=122.88..216.82 rows=901 width=792) (actual time=4.681..14.324 rows=1,105 loops=1)

  • Buffers: shared hit=54
44. 1.153 13.174 ↓ 1.2 1,093 1

Hash Join (cost=121.07..209.21 rows=901 width=760) (actual time=4.63..13.174 rows=1,093 loops=1)

  • Buffers: shared hit=53
45. 1.307 11.794 ↓ 1.2 1,071 1

Hash Join (cost=113.53..184.52 rows=901 width=728) (actual time=4.388..11.794 rows=1,071 loops=1)

  • Buffers: shared hit=51
46. 0.834 8.700 ↑ 1.0 897 1

Hash Join (cost=73.81..134.75 rows=901 width=615) (actual time=2.583..8.7 rows=897 loops=1)

  • Buffers: shared hit=40
47. 1.132 7.848 ↑ 1.0 897 1

Hash Join (cost=72.79..130.34 rows=901 width=583) (actual time=2.541..7.848 rows=897 loops=1)

  • Buffers: shared hit=39
48. 0.875 5.799 ↑ 1.0 897 1

Hash Join (cost=41.51..86.68 rows=901 width=519) (actual time=1.606..5.799 rows=897 loops=1)

  • Buffers: shared hit=28
49. 0.996 4.745 ↑ 1.0 897 1

Hash Join (cost=36.5..76.94 rows=901 width=454) (actual time=1.419..4.745 rows=897 loops=1)

  • Buffers: shared hit=26
50. 0.828 3.372 ↑ 1.0 897 1

Hash Join (cost=24.04..57.35 rows=901 width=382) (actual time=1.031..3.372 rows=897 loops=1)

  • Buffers: shared hit=22
51. 1.112 2.490 ↑ 1.0 897 1

Hash Join (cost=21.98..51.44 rows=901 width=350) (actual time=0.966..2.49 rows=897 loops=1)

  • Buffers: shared hit=21
52. 0.434 0.434 ↑ 1.0 897 1

Seq Scan on invest_program_version i0 (cost=0..23.01 rows=901 width=205) (actual time=0.01..0.434 rows=897 loops=1)

  • Buffers: shared hit=14
53. 0.274 0.944 ↑ 1.0 306 1

Hash (cost=18.14..18.14 rows=307 width=145) (actual time=0.944..0.944 rows=306 loops=1)

  • Buffers: shared hit=7
54. 0.312 0.670 ↑ 1.0 306 1

Hash Join (cost=7.79..18.14 rows=307 width=145) (actual time=0.249..0.67 rows=306 loops=1)

  • Buffers: shared hit=7
55. 0.130 0.130 ↑ 1.0 306 1

Seq Scan on statement s (cost=0..7.07 rows=307 width=81) (actual time=0.008..0.13 rows=306 loops=1)

  • Buffers: shared hit=4
56. 0.128 0.228 ↑ 1.0 212 1

Hash (cost=5.13..5.13 rows=213 width=64) (actual time=0.228..0.228 rows=212 loops=1)

  • Buffers: shared hit=3
57. 0.100 0.100 ↑ 1.0 212 1

Seq Scan on final_document f (cost=0..5.13 rows=213 width=64) (actual time=0.01..0.1 rows=212 loops=1)

  • Buffers: shared hit=3
58. 0.035 0.054 ↑ 1.0 47 1

Hash (cost=1.47..1.47 rows=47 width=32) (actual time=0.054..0.054 rows=47 loops=1)

  • Buffers: shared hit=1
59. 0.019 0.019 ↑ 1.0 47 1

Seq Scan on disagreement_protocol d (cost=0..1.47 rows=47 width=32) (actual time=0.006..0.019 rows=47 loops=1)

  • Buffers: shared hit=1
60. 0.200 0.377 ↑ 1.0 373 1

Hash (cost=7.76..7.76 rows=376 width=72) (actual time=0.377..0.377 rows=373 loops=1)

  • Buffers: shared hit=4
61. 0.177 0.177 ↑ 1.0 373 1

Seq Scan on check c (cost=0..7.76 rows=376 width=72) (actual time=0.007..0.177 rows=373 loops=1)

  • Buffers: shared hit=4
62. 0.087 0.179 ↑ 1.0 131 1

Hash (cost=3.34..3.34 rows=134 width=65) (actual time=0.179..0.179 rows=131 loops=1)

  • Buffers: shared hit=2
63. 0.092 0.092 ↑ 1.0 131 1

Seq Scan on getting_result_approval g (cost=0..3.34 rows=134 width=65) (actual time=0.008..0.092 rows=131 loops=1)

  • Buffers: shared hit=2
64. 0.532 0.917 ↑ 1.0 897 1

Hash (cost=20.01..20.01 rows=901 width=64) (actual time=0.917..0.917 rows=897 loops=1)

  • Buffers: shared hit=11
65. 0.385 0.385 ↑ 1.0 897 1

Seq Scan on invest_program_presentation_form i1 (cost=0..20.01 rows=901 width=64) (actual time=0.008..0.385 rows=897 loops=1)

  • Buffers: shared hit=11
66. 0.009 0.018 ↑ 1.0 1 1

Hash (cost=1.01..1.01 rows=1 width=32) (actual time=0.018..0.018 rows=1 loops=1)

  • Buffers: shared hit=1
67. 0.009 0.009 ↑ 1.0 1 1

Seq Scan on activities_verification_document a2 (cost=0..1.01 rows=1 width=32) (actual time=0.009..0.009 rows=1 loops=1)

  • Buffers: shared hit=1
68. 0.442 1.787 ↓ 1.1 610 1

Hash (cost=32.8..32.8 rows=554 width=113) (actual time=1.787..1.787 rows=610 loops=1)

  • Buffers: shared hit=11
69. 0.502 1.345 ↓ 1.1 610 1

Hash Join (cost=20.46..32.8 rows=554 width=113) (actual time=0.663..1.345 rows=610 loops=1)

  • Buffers: shared hit=11
70. 0.202 0.202 ↑ 1.0 393 1

Seq Scan on final_approval_resolution_document f1 (cost=0..6.93 rows=393 width=32) (actual time=0.01..0.202 rows=393 loops=1)

  • Buffers: shared hit=3
71. 0.306 0.641 ↑ 1.0 553 1

Hash (cost=13.54..13.54 rows=554 width=81) (actual time=0.641..0.641 rows=553 loops=1)

  • Buffers: shared hit=8
72. 0.335 0.335 ↑ 1.0 553 1

Seq Scan on final_approval f0 (cost=0..13.54 rows=554 width=81) (actual time=0.012..0.335 rows=553 loops=1)

  • Buffers: shared hit=8
73. 0.119 0.227 ↑ 1.0 243 1

Hash (cost=4.46..4.46 rows=246 width=32) (actual time=0.227..0.227 rows=243 loops=1)

  • Buffers: shared hit=2
74. 0.108 0.108 ↑ 1.0 243 1

Seq Scan on resolution_document r (cost=0..4.46 rows=246 width=32) (actual time=0.01..0.108 rows=243 loops=1)

  • Buffers: shared hit=2
75. 0.020 0.038 ↑ 1.0 36 1

Hash (cost=1.36..1.36 rows=36 width=32) (actual time=0.038..0.038 rows=36 loops=1)

  • Buffers: shared hit=1
76. 0.018 0.018 ↑ 1.0 36 1

Seq Scan on getting_result_approval_resolution_document g0 (cost=0..1.36 rows=36 width=32) (actual time=0.006..0.018 rows=36 loops=1)

  • Buffers: shared hit=1
77. 0.718 4.658 ↓ 1.1 834 1

Hash (cost=71.17..71.17 rows=742 width=305) (actual time=4.658..4.658 rows=834 loops=1)

  • Buffers: shared hit=24
78. 0.680 3.940 ↓ 1.1 834 1

Hash Join (cost=62.78..71.17 rows=742 width=305) (actual time=3.189..3.94 rows=834 loops=1)

  • Buffers: shared hit=24
79. 0.089 0.089 ↑ 1.0 269 1

Seq Scan on approval_request_connection_requests a1 (cost=0..4.69 rows=269 width=32) (actual time=0.008..0.089 rows=269 loops=1)

  • Buffers: shared hit=2
80. 0.582 3.171 ↓ 1.0 771 1

Hash (cost=53.5..53.5 rows=742 width=273) (actual time=3.171..3.171 rows=771 loops=1)

  • Buffers: shared hit=22
81. 0.496 2.589 ↓ 1.0 771 1

Hash Join (cost=49.2..53.5 rows=742 width=273) (actual time=2.055..2.589 rows=771 loops=1)

  • Buffers: shared hit=22
82. 0.059 0.059 ↑ 1.0 97 1

Seq Scan on investment_program_implementation_report i (cost=0..2.97 rows=97 width=76) (actual time=0.009..0.059 rows=97 loops=1)

  • Buffers: shared hit=2
83. 0.553 2.034 ↑ 1.0 711 1

Hash (cost=39.92..39.92 rows=742 width=197) (actual time=2.034..2.034 rows=711 loops=1)

  • Buffers: shared hit=20
84. 0.446 1.481 ↑ 1.0 711 1

Hash Join (cost=35.7..39.92 rows=742 width=197) (actual time=0.997..1.481 rows=711 loops=1)

  • Buffers: shared hit=20
85. 0.059 0.059 ↑ 1.0 136 1

Seq Scan on approval_request_required_documents a0 (cost=0..2.36 rows=136 width=32) (actual time=0.008..0.059 rows=136 loops=1)

  • Buffers: shared hit=1
86. 0.491 0.976 ↑ 1.2 623 1

Hash (cost=26.42..26.42 rows=742 width=165) (actual time=0.976..0.976 rows=623 loops=1)

  • Buffers: shared hit=19
87. 0.485 0.485 ↑ 1.2 623 1

Seq Scan on approval_request a (cost=0..26.42 rows=742 width=165) (actual time=0.016..0.485 rows=623 loops=1)

  • Buffers: shared hit=19
88. 0.199 1.898 ↑ 1.4 450 1

Hash (cost=16,487.45..16,487.45 rows=614 width=16) (actual time=1.898..1.898 rows=450 loops=1)

  • Buffers: shared hit=35
89. 0.553 1.699 ↑ 1.4 450 1

HashAggregate (cost=16,481.31..16,487.45 rows=614 width=16) (actual time=1.522..1.699 rows=450 loops=1)

  • Group Key: i2.approval_request_id
  • Buffers: shared hit=35
90. 0.719 1.146 ↑ 1.3 671 1

Seq Scan on invest_program_version i2 (cost=0..16,479.2 rows=845 width=16) (actual time=0.755..1.146 rows=671 loops=1)

  • Filter: ((i2.ministry_organization_id = 'f59bb956-5e31-4f91-86df-d01f6c36e173'::uuid) OR (alternatives: SubPlan 1 or hashed SubPlan 2) OR (alternatives: SubPlan 3 or hashed SubPlan 4))
  • Buffers: shared hit=35
91.          

SubPlan (for Seq Scan)

92. 0.000 0.000 ↓ 0.0 0 0

Bitmap Heap Scan on preliminary_approval p1 (cost=4.29..9.97 rows=1 width=0) (never executed)

  • Filter: (p1.approval_organization_id = 'f59bb956-5e31-4f91-86df-d01f6c36e173'::uuid)
93. 0.000 0.000 ↓ 0.0 0 0

Bitmap Index Scan on ix_preliminary_approval_invest_program_version_id (cost=0..4.29 rows=2 width=0) (never executed)

  • Index Cond: (i2.id = p1.invest_program_version_id)
94. 0.195 0.195 ↑ 2.0 1 1

Seq Scan on preliminary_approval p1_1 (cost=0..24.91 rows=2 width=16) (actual time=0.06..0.195 rows=1 loops=1)

  • Filter: (p1_1.approval_organization_id = 'f59bb956-5e31-4f91-86df-d01f6c36e173'::uuid)
  • Buffers: shared hit=13
95. 0.000 0.000 ↓ 0.0 0 0

Index Scan using ix_final_approval_version_id on final_approval f2 (cost=0.28..8.29 rows=1 width=0) (never executed)

  • Index Cond: (i2.id = f2.version_id)
  • Filter: (f2.approval_organization_id = 'f59bb956-5e31-4f91-86df-d01f6c36e173'::uuid)
96. 0.232 0.232 ↑ 1.0 375 1

Seq Scan on final_approval f2_1 (cost=0..14.93 rows=376 width=16) (actual time=0.028..0.232 rows=375 loops=1)

  • Filter: (f2_1.approval_organization_id = 'f59bb956-5e31-4f91-86df-d01f6c36e173'::uuid)
  • Buffers: shared hit=8
Planning time : 13.785 ms
Execution time : 147,883.335 ms