explain.depesz.com

PostgreSQL's explain analyze made readable

Result: issN

Settings
# exclusive inclusive rows x rows loops node
1. 0.932 3,880.866 ↑ 403.1 17 1

GroupAggregate (cost=4,147,490.47..4,148,244.19 rows=6,852 width=1,356) (actual time=3,880.116..3,880.866 rows=17 loops=1)

  • Group Key: a.id_person, (((((a.first_name)::text || ' '::text) || CASE WHEN (a.middle_name IS NOT NULL) THEN ((a.middle_name)::text || ' '::text) ELSE ''::text END) || (a.last_name)::text)), b.id_mapping, c.code, d.code, e.code, f.code, g.code, b.ext_uuid, b.id_channel, ((active_ids.id_person IS NOT NULL))
  • Functions: 205
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 34.873 ms, Inlining 39.342 ms, Optimization 2349.008 ms, Emission 1479.257 ms, Total 3902.481 ms
2. 0.055 3,879.934 ↑ 263.5 26 1

Sort (cost=4,147,490.47..4,147,507.60 rows=6,852 width=1,356) (actual time=3,879.932..3,879.934 rows=26 loops=1)

  • Sort Key: a.id_person, (((((a.first_name)::text || ' '::text) || CASE WHEN (a.middle_name IS NOT NULL) THEN ((a.middle_name)::text || ' '::text) ELSE ''::text END) || (a.last_name)::text)), b.id_mapping, c.code, d.code, e.code, f.code, g.code, b.ext_uuid, b.id_channel, ((active_ids.id_person IS NOT NULL))
  • Sort Method: quicksort Memory: 43kB
3. 0.018 3,879.879 ↑ 263.5 26 1

Hash Left Join (cost=4,144,566.60..4,144,737.92 rows=6,852 width=1,356) (actual time=3,879.804..3,879.879 rows=26 loops=1)

  • Hash Cond: (a.id_person = active_ids.id_person)
4. 0.025 3,879.495 ↑ 263.5 26 1

Hash Right Join (cost=249.97..334.40 rows=6,852 width=1,323) (actual time=3,879.427..3,879.495 rows=26 loops=1)

  • Hash Cond: (b_1.id_person = a.id_person)
5. 0.017 0.153 ↑ 106.2 16 1

Hash Join (cost=61.30..104.93 rows=1,700 width=36) (actual time=0.099..0.153 rows=16 loops=1)

  • Hash Cond: (e_1.id_country = f_1.id_country)
6. 0.011 0.131 ↑ 106.2 16 1

Hash Join (cost=46.35..85.43 rows=1,700 width=786) (actual time=0.086..0.131 rows=16 loops=1)

  • Hash Cond: (c_1.id_state = e_1.id_state)
7. 0.010 0.107 ↑ 106.2 16 1

Hash Join (cost=31.85..66.38 rows=1,700 width=748) (actual time=0.065..0.107 rows=16 loops=1)

  • Hash Cond: (a_1.id_type = d_1.id_address_type)
8. 0.012 0.089 ↑ 106.2 16 1

Hash Join (cost=12.63..42.65 rows=1,700 width=700) (actual time=0.051..0.089 rows=16 loops=1)

  • Hash Cond: (a_1.id_address = c_1.id_address)
9. 0.023 0.062 ↑ 106.2 16 1

Nested Loop (cost=0.15..25.62 rows=1,700 width=36) (actual time=0.027..0.062 rows=16 loops=1)

10. 0.006 0.006 ↑ 1.0 33 1

Seq Scan on people b_1 (cost=0.00..1.33 rows=33 width=20) (actual time=0.002..0.006 rows=33 loops=1)

11. 0.033 0.033 ↓ 0.0 0 33

Index Scan using person_addresses_pkey on person_addresses a_1 (cost=0.15..0.66 rows=8 width=20) (actual time=0.001..0.001 rows=0 loops=33)

  • Index Cond: (id_person = b_1.id_person)
12. 0.006 0.015 ↑ 6.9 16 1

Hash (cost=11.10..11.10 rows=110 width=668) (actual time=0.015..0.015 rows=16 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
13. 0.009 0.009 ↑ 6.9 16 1

Seq Scan on addresses c_1 (cost=0.00..11.10 rows=110 width=668) (actual time=0.007..0.009 rows=16 loops=1)

14. 0.002 0.008 ↑ 205.0 2 1

Hash (cost=14.10..14.10 rows=410 width=52) (actual time=0.008..0.008 rows=2 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
15. 0.006 0.006 ↑ 205.0 2 1

Seq Scan on address_types d_1 (cost=0.00..14.10 rows=410 width=52) (actual time=0.005..0.006 rows=2 loops=1)

16. 0.004 0.013 ↑ 8.3 24 1

Hash (cost=12.00..12.00 rows=200 width=42) (actual time=0.013..0.013 rows=24 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
17. 0.009 0.009 ↑ 8.3 24 1

Seq Scan on states e_1 (cost=0.00..12.00 rows=200 width=42) (actual time=0.006..0.009 rows=24 loops=1)

18. 0.002 0.005 ↑ 220.0 1 1

Hash (cost=12.20..12.20 rows=220 width=16) (actual time=0.005..0.005 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
19. 0.003 0.003 ↑ 220.0 1 1

Seq Scan on countries f_1 (cost=0.00..12.20 rows=220 width=16) (actual time=0.003..0.003 rows=1 loops=1)

20. 0.016 3,879.317 ↑ 5.1 26 1

Hash (cost=187.00..187.00 rows=133 width=1,291) (actual time=3,879.317..3,879.317 rows=26 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 17kB
21. 0.023 3,879.301 ↑ 5.1 26 1

Hash Right Join (cost=165.38..187.00 rows=133 width=1,291) (actual time=3,879.260..3,879.301 rows=26 loops=1)

  • Hash Cond: (a_2.id_owner = a.id_person)
22. 0.019 0.143 ↑ 15.8 24 1

Hash Left Join (cost=76.39..95.26 rows=380 width=36) (actual time=0.115..0.143 rows=24 loops=1)

  • Hash Cond: (a_2.id_type = f_2.id_account_type)
23. 0.013 0.113 ↑ 15.8 24 1

Hash Left Join (cost=59.42..77.27 rows=380 width=572) (actual time=0.095..0.113 rows=24 loops=1)

  • Hash Cond: (a_2.id_bank = e_2.id_bank)
24. 0.010 0.077 ↑ 15.8 24 1

Hash Left Join (cost=44.02..60.85 rows=380 width=282) (actual time=0.063..0.077 rows=24 loops=1)

  • Hash Cond: (a_2.id_owner = d_2.id_person)
25. 0.011 0.054 ↑ 15.8 24 1

Hash Join (cost=42.27..58.09 rows=380 width=266) (actual time=0.043..0.054 rows=24 loops=1)

  • Hash Cond: (a_2.id_account = c_2.id_account)
26. 0.014 0.031 ↑ 15.8 24 1

Hash Join (cost=16.52..31.33 rows=380 width=202) (actual time=0.024..0.031 rows=24 loops=1)

  • Hash Cond: (a_2.id_currency = b_2.id_currency)
27. 0.008 0.008 ↑ 15.8 24 1

Seq Scan on bank_accounts a_2 (cost=0.00..13.80 rows=380 width=182) (actual time=0.006..0.008 rows=24 loops=1)

28. 0.003 0.009 ↑ 96.7 3 1

Hash (cost=12.90..12.90 rows=290 width=24) (actual time=0.009..0.009 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
29. 0.006 0.006 ↑ 96.7 3 1

Seq Scan on currencies b_2 (cost=0.00..12.90 rows=290 width=24) (actual time=0.005..0.006 rows=3 loops=1)

30. 0.004 0.012 ↑ 53.8 13 1

Hash (cost=17.00..17.00 rows=700 width=68) (actual time=0.012..0.012 rows=13 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
31. 0.008 0.008 ↑ 53.8 13 1

Seq Scan on accounts c_2 (cost=0.00..17.00 rows=700 width=68) (actual time=0.007..0.008 rows=13 loops=1)

32. 0.005 0.013 ↑ 1.0 33 1

Hash (cost=1.33..1.33 rows=33 width=20) (actual time=0.013..0.013 rows=33 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
33. 0.008 0.008 ↑ 1.0 33 1

Seq Scan on people d_2 (cost=0.00..1.33 rows=33 width=20) (actual time=0.005..0.008 rows=33 loops=1)

34. 0.011 0.023 ↑ 3.8 63 1

Hash (cost=12.40..12.40 rows=240 width=294) (actual time=0.023..0.023 rows=63 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
35. 0.012 0.012 ↑ 3.8 63 1

Seq Scan on banks e_2 (cost=0.00..12.40 rows=240 width=294) (actual time=0.007..0.012 rows=63 loops=1)

36. 0.003 0.011 ↑ 44.3 7 1

Hash (cost=13.10..13.10 rows=310 width=52) (actual time=0.011..0.011 rows=7 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
37. 0.008 0.008 ↑ 44.3 7 1

Seq Scan on bank_account_types f_2 (cost=0.00..13.10 rows=310 width=52) (actual time=0.006..0.008 rows=7 loops=1)

38. 0.012 3,879.135 ↑ 4.1 17 1

Hash (cost=88.11..88.11 rows=70 width=1,259) (actual time=3,879.135..3,879.135 rows=17 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
39. 0.012 3,879.123 ↑ 4.1 17 1

Hash Join (cost=72.95..88.11 rows=70 width=1,259) (actual time=3,879.114..3,879.123 rows=17 loops=1)

  • Hash Cond: (a.id_person = b.id_person)
40. 0.020 0.188 ↑ 4.1 17 1

Hash Right Join (cost=71.21..86.18 rows=70 width=1,235) (actual time=0.182..0.188 rows=17 loops=1)

  • Hash Cond: (d.id_id_type = a.id_tax_id_type)
41. 0.002 0.002 ↑ 103.3 3 1

Seq Scan on id_types d (cost=0.00..13.10 rows=310 width=46) (actual time=0.002..0.002 rows=3 loops=1)

42. 0.009 0.166 ↑ 4.1 17 1

Hash (cost=70.34..70.34 rows=70 width=1,193) (actual time=0.165..0.166 rows=17 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
43. 0.016 0.157 ↑ 4.1 17 1

Hash Right Join (cost=55.38..70.34 rows=70 width=1,193) (actual time=0.151..0.157 rows=17 loops=1)

  • Hash Cond: (c.id_id_type = a.id_id_type)
44. 0.012 0.012 ↑ 103.3 3 1

Seq Scan on id_types c (cost=0.00..13.10 rows=310 width=46) (actual time=0.012..0.012 rows=3 loops=1)

45. 0.026 0.129 ↑ 4.1 17 1

Hash (cost=54.50..54.50 rows=70 width=1,151) (actual time=0.129..0.129 rows=17 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
46. 0.013 0.103 ↑ 4.1 17 1

Hash Right Join (cost=40.77..54.50 rows=70 width=1,151) (actual time=0.096..0.103 rows=17 loops=1)

  • Hash Cond: (g.id_country = a.id_citizenship)
47. 0.003 0.003 ↑ 220.0 1 1

Seq Scan on countries g (cost=0.00..12.20 rows=220 width=16) (actual time=0.002..0.003 rows=1 loops=1)

48. 0.010 0.087 ↑ 4.1 17 1

Hash (cost=39.90..39.90 rows=70 width=1,139) (actual time=0.087..0.087 rows=17 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
49. 0.013 0.077 ↑ 4.1 17 1

Hash Right Join (cost=26.17..39.90 rows=70 width=1,139) (actual time=0.072..0.077 rows=17 loops=1)

  • Hash Cond: (f.id_country = a.id_birth_country)
50. 0.006 0.006 ↑ 220.0 1 1

Seq Scan on countries f (cost=0.00..12.20 rows=220 width=16) (actual time=0.006..0.006 rows=1 loops=1)

51. 0.010 0.058 ↑ 4.1 17 1

Hash (cost=25.30..25.30 rows=70 width=1,127) (actual time=0.058..0.058 rows=17 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
52. 0.016 0.048 ↑ 4.1 17 1

Hash Right Join (cost=11.57..25.30 rows=70 width=1,127) (actual time=0.042..0.048 rows=17 loops=1)

  • Hash Cond: (e.id_marital_status = a.id_marital_status)
53. 0.005 0.005 ↑ 44.0 5 1

Seq Scan on marital_status e (cost=0.00..12.20 rows=220 width=20) (actual time=0.005..0.005 rows=5 loops=1)

54. 0.007 0.027 ↑ 4.1 17 1

Hash (cost=10.70..10.70 rows=70 width=1,111) (actual time=0.027..0.027 rows=17 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
55. 0.020 0.020 ↑ 4.1 17 1

Seq Scan on human_people a (cost=0.00..10.70 rows=70 width=1,111) (actual time=0.018..0.020 rows=17 loops=1)

56. 0.104 3,878.923 ↑ 1.0 33 1

Hash (cost=1.33..1.33 rows=33 width=28) (actual time=3,878.923..3,878.923 rows=33 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
57. 3,878.819 3,878.819 ↑ 1.0 33 1

Seq Scan on people b (cost=0.00..1.33 rows=33 width=28) (actual time=3,878.812..3,878.819 rows=33 loops=1)

58. 0.007 0.366 ↑ 7.1 28 1

Hash (cost=4,144,314.13..4,144,314.13 rows=200 width=4) (actual time=0.365..0.366 rows=28 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
59. 0.005 0.359 ↑ 7.1 28 1

Unique (cost=4,144,215.29..4,144,312.13 rows=200 width=4) (actual time=0.353..0.359 rows=28 loops=1)

60.          

CTE active_ids

61. 0.009 0.009 ↑ 53.3 13 1

Seq Scan on accounts a_5 (cost=0.00..18.75 rows=693 width=4) (actual time=0.008..0.009 rows=13 loops=1)

  • Filter: (status <> ALL ('{CANCELLED,CLOSED}'::accountstatus[]))
62. 0.014 0.354 ↑ 691.8 28 1

Sort (cost=4,144,196.54..4,144,244.96 rows=19,369 width=4) (actual time=0.352..0.354 rows=28 loops=1)

  • Sort Key: active_ids.id_person
  • Sort Method: quicksort Memory: 26kB
63. 0.190 0.340 ↑ 691.8 28 1

HashAggregate (cost=4,142,429.94..4,142,623.63 rows=19,369 width=4) (actual time=0.172..0.340 rows=28 loops=1)

  • Group Key: active_ids.id_person
64. 0.004 0.150 ↑ 691.8 28 1

Append (cost=0.00..4,142,381.52 rows=19,369 width=4) (actual time=0.011..0.150 rows=28 loops=1)

65. 0.014 0.014 ↑ 53.3 13 1

CTE Scan on active_ids (cost=0.00..13.86 rows=693 width=4) (actual time=0.010..0.014 rows=13 loops=1)

66. 0.008 0.025 ↑ 870.1 9 1

Merge Join (cost=46.71..215.73 rows=7,831 width=4) (actual time=0.019..0.025 rows=9 loops=1)

  • Merge Cond: (b_3.id_consortium = a_3.id_person)
67. 0.010 0.010 ↑ 251.1 9 1

Index Only Scan using consortium_members_pkey on consortium_members b_3 (cost=0.15..46.06 rows=2,260 width=8) (actual time=0.007..0.010 rows=9 loops=1)

  • Heap Fetches: 9
68. 0.005 0.007 ↑ 53.3 13 1

Sort (cost=46.56..48.29 rows=693 width=4) (actual time=0.006..0.007 rows=13 loops=1)

  • Sort Key: a_3.id_person
  • Sort Method: quicksort Memory: 25kB
69. 0.002 0.002 ↑ 53.3 13 1

CTE Scan on active_ids a_3 (cost=0.00..13.86 rows=693 width=4) (actual time=0.001..0.002 rows=13 loops=1)

70. 0.005 0.016 ↑ 1,305.0 3 1

Merge Join (cost=46.71..137.21 rows=3,915 width=4) (actual time=0.014..0.016 rows=3 loops=1)

  • Merge Cond: (b_4.id_legal_person = a_4.id_person)
71. 0.005 0.005 ↑ 376.7 3 1

Index Only Scan using agents_pkey on agents b_4 (cost=0.15..29.10 rows=1,130 width=8) (actual time=0.004..0.005 rows=3 loops=1)

  • Heap Fetches: 3
72. 0.004 0.006 ↑ 53.3 13 1

Sort (cost=46.56..48.29 rows=693 width=4) (actual time=0.005..0.006 rows=13 loops=1)

  • Sort Key: a_4.id_person
  • Sort Method: quicksort Memory: 25kB
73. 0.002 0.002 ↑ 53.3 13 1

CTE Scan on active_ids a_4 (cost=0.00..13.86 rows=693 width=4) (actual time=0.000..0.002 rows=13 loops=1)

74. 0.004 0.091 ↑ 2,310.0 3 1

Nested Loop (cost=4,079,778.31..4,141,724.19 rows=6,930 width=4) (actual time=0.061..0.091 rows=3 loops=1)

75. 0.008 0.054 ↑ 231.0 3 1

Hash Join (cost=4,079,778.31..4,141,585.59 rows=693 width=32) (actual time=0.051..0.054 rows=3 loops=1)

  • Hash Cond: (tree.id_legal_person = c_3.id_person)
76. 0.002 0.041 ↑ 66.7 3 1

Unique (cost=4,079,755.78..4,141,536.38 rows=200 width=72) (actual time=0.039..0.041 rows=3 loops=1)

77.          

CTE tree

78. 0.009 0.029 ↑ 2,471,224.0 5 1

Recursive Union (cost=0.00..1,477,477.08 rows=12,356,120 width=68) (actual time=0.012..0.029 rows=5 loops=1)

79. 0.008 0.008 ↑ 326.0 5 1

Seq Scan on shareholders (cost=0.00..26.30 rows=1,630 width=68) (actual time=0.007..0.008 rows=5 loops=1)

80. 0.005 0.012 ↓ 0.0 0 1

Nested Loop (cost=0.15..123,032.84 rows=1,235,449 width=68) (actual time=0.012..0.012 rows=0 loops=1)

81. 0.002 0.002 ↑ 3,260.0 5 1

WorkTable Scan on tree tree_1 (cost=0.00..326.00 rows=16,300 width=68) (actual time=0.002..0.002 rows=5 loops=1)

82. 0.005 0.005 ↓ 0.0 0 5

Index Scan using shareholders_pkey on shareholders a_6 (cost=0.15..3.93 rows=76 width=22) (actual time=0.001..0.001 rows=0 loops=5)

  • Index Cond: (id_legal_person = ANY (tree_1.shareholders))
  • Filter: (id_person <> ALL (tree_1.shareholders))
83. 0.006 0.039 ↑ 2,471,224.0 5 1

Sort (cost=2,602,278.71..2,633,169.01 rows=12,356,120 width=72) (actual time=0.039..0.039 rows=5 loops=1)

  • Sort Key: tree.id_legal_person, (array_length(tree.ownership, 1)) DESC
  • Sort Method: quicksort Memory: 25kB
84. 0.033 0.033 ↑ 2,471,224.0 5 1

CTE Scan on tree (cost=0.00..278,012.70 rows=12,356,120 width=72) (actual time=0.015..0.033 rows=5 loops=1)

85. 0.003 0.005 ↑ 53.3 13 1

Hash (cost=13.86..13.86 rows=693 width=4) (actual time=0.005..0.005 rows=13 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
86. 0.002 0.002 ↑ 53.3 13 1

CTE Scan on active_ids c_3 (cost=0.00..13.86 rows=693 width=4) (actual time=0.000..0.002 rows=13 loops=1)

87. 0.033 0.033 ↑ 10.0 1 3

Function Scan on unnest b_5 (cost=0.00..0.10 rows=10 width=4) (actual time=0.011..0.011 rows=1 loops=3)

Execution time : 3,917.921 ms