explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pP2f

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

HashAggregate (cost=2,591,913,940.70..2,591,913,942.70 rows=200 width=4) (actual rows= loops=)

  • Group Key: inserted_sp_logged_actions.contract_id
2.          

CTE selected_contracts

3. 0.000 0.000 ↓ 0.0

Hash Join (cost=1.12..96,179.95 rows=603,827 width=465) (actual rows= loops=)

  • Hash Cond: (contracts.contract_status_id = cs.id)
4. 0.000 0.000 ↓ 0.0

Seq Scan on contracts (cost=0.00..90,769.54 rows=1,207,654 width=465) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Hash (cost=1.08..1.08 rows=3 width=2) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Seq Scan on contract_statuses cs (cost=0.00..1.08 rows=3 width=2) (actual rows= loops=)

  • Filter: (code = ANY ('{V,B,O}'::text[]))
7.          

CTE selected_periods

8. 0.000 0.000 ↓ 0.0

WindowAgg (cost=1.05..1.18 rows=3 width=106) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Sort (cost=1.05..1.06 rows=3 width=86) (actual rows= loops=)

  • Sort Key: tp.code
10. 0.000 0.000 ↓ 0.0

Seq Scan on threshold_periods tp (cost=0.00..1.03 rows=3 width=86) (actual rows= loops=)

11.          

CTE sum_by_periods

12. 0.000 0.000 ↓ 0.0

HashAggregate (cost=18,000,351.88..18,000,369.88 rows=1,200 width=77) (actual rows= loops=)

  • Group Key: c.id, c.iop_status, sp.id, sp.weight, sp.num_of_days
13.          

Initplan (for HashAggregate)

14. 0.000 0.000 ↓ 0.0

Aggregate (cost=0.07..0.08 rows=1 width=8) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

CTE Scan on selected_periods (cost=0.00..0.06 rows=3 width=8) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Aggregate (cost=0.07..0.08 rows=1 width=8) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

CTE Scan on selected_periods selected_periods_1 (cost=0.00..0.06 rows=3 width=8) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Hash Join (cost=17,934,257.82..17,966,612.88 rows=1,499,504 width=25) (actual rows= loops=)

  • Hash Cond: (c.id = a.contract_id)
19. 0.000 0.000 ↓ 0.0

CTE Scan on selected_contracts c (cost=0.00..12,076.54 rows=603,827 width=5) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Hash (cost=17,928,700.77..17,928,700.77 rows=444,564 width=24) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Nested Loop (cost=543,733.25..17,928,700.77 rows=444,564 width=24) (actual rows= loops=)

  • Join Filter: ((t.created_at >= sp.from_dt) AND (t.created_at <= sp.to_dt))
22. 0.000 0.000 ↓ 0.0

CTE Scan on selected_periods sp (cost=0.00..0.06 rows=3 width=24) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Materialize (cost=543,733.25..17,846,386.11 rows=1,333,692 width=24) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Hash Join (cost=543,733.25..17,831,902.65 rows=1,333,692 width=24) (actual rows= loops=)

  • Hash Cond: (t.account_id = a.id)
25. 0.000 0.000 ↓ 0.0

Index Scan using idx_account_transactions_account_id on account_transactions t (cost=0.57..17,239,750.84 rows=2,677,378 width=32) (actual rows= loops=)

  • Index Cond: ((created_at >= $2) AND (created_at <= $3))
  • Filter: (account_transaction_type_id = ANY ('{1,2}'::integer[]))
26. 0.000 0.000 ↓ 0.0

Hash (cost=523,635.70..523,635.70 rows=1,224,958 width=8) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Seq Scan on accounts a (cost=0.00..523,635.70 rows=1,224,958 width=8) (actual rows= loops=)

  • Filter: (account_type_id = 4)
28.          

CTE grouped_orange_list_sum

29. 0.000 0.000 ↓ 0.0

HashAggregate (cost=206,007.78..206,012.78 rows=400 width=37) (actual rows= loops=)

  • Group Key: c_1.id, c_1.iop_status
30. 0.000 0.000 ↓ 0.0

Merge Right Join (cost=70,140.70..124,491.13 rows=3,622,962 width=75) (actual rows= loops=)

  • Merge Cond: (sbp.contract_id = c_1.id)
31. 0.000 0.000 ↓ 0.0

Sort (cost=85.37..88.37 rows=1,200 width=74) (actual rows= loops=)

  • Sort Key: sbp.contract_id
32. 0.000 0.000 ↓ 0.0

CTE Scan on sum_by_periods sbp (cost=0.00..24.00 rows=1,200 width=74) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Sort (cost=70,055.33..71,564.90 rows=603,827 width=5) (actual rows= loops=)

  • Sort Key: c_1.id
34. 0.000 0.000 ↓ 0.0

CTE Scan on selected_contracts c_1 (cost=0.00..12,076.54 rows=603,827 width=5) (actual rows= loops=)

35.          

CTE grey_list_limits

36. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=145,186.86..149,182.13 rows=200 width=12) (actual rows= loops=)

  • Group Key: c_2.id
37. 0.000 0.000 ↓ 0.0

Merge Join (cost=145,186.86..148,906.65 rows=54,696 width=12) (actual rows= loops=)

  • Merge Cond: (d.contract_id = c_2.id)
38. 0.000 0.000 ↓ 0.0

Sort (cost=75,131.53..75,208.38 rows=30,741 width=12) (actual rows= loops=)

  • Sort Key: d.contract_id
39. 0.000 0.000 ↓ 0.0

Hash Join (cost=5.13..72,840.12 rows=30,741 width=12) (actual rows= loops=)

  • Hash Cond: (tc.travel_card_type_id = tct.id)
40. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.56..72,750.96 rows=30,741 width=8) (actual rows= loops=)

41. 0.000 0.000 ↓ 0.0

Hash Join (cost=1.12..6,984.56 rows=30,741 width=8) (actual rows= loops=)

  • Hash Cond: (tc.travel_card_status_id = travel_card_statuses.id)
42. 0.000 0.000 ↓ 0.0

Seq Scan on travel_cards tc (cost=0.00..6,489.65 rows=122,965 width=10) (actual rows= loops=)

43. 0.000 0.000 ↓ 0.0

Hash (cost=1.10..1.10 rows=2 width=2) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

Seq Scan on travel_card_statuses (cost=0.00..1.10 rows=2 width=2) (actual rows= loops=)

  • Filter: (code = ANY ('{new,active}'::text[]))
45. 0.000 0.000 ↓ 0.0

Index Scan using devices_pkey on devices d (cost=0.43..2.14 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (id = tc.device_id)
46. 0.000 0.000 ↓ 0.0

Hash (cost=2.70..2.70 rows=70 width=12) (actual rows= loops=)

47. 0.000 0.000 ↓ 0.0

Seq Scan on travel_card_types tct (cost=0.00..2.70 rows=70 width=12) (actual rows= loops=)

48. 0.000 0.000 ↓ 0.0

Sort (cost=70,055.33..71,564.90 rows=603,827 width=4) (actual rows= loops=)

  • Sort Key: c_2.id
49. 0.000 0.000 ↓ 0.0

CTE Scan on selected_contracts c_2 (cost=0.00..12,076.54 rows=603,827 width=4) (actual rows= loops=)

50.          

CTE result_limits

51. 0.000 0.000 ↓ 0.0

Hash Join (cost=544,949.76..1,096,965.49 rows=8,263,511 width=52) (actual rows= loops=)

  • Hash Cond: (c_3.id = a_1.contract_id)
52. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=20.08..84,567.96 rows=1,207,654 width=125) (actual rows= loops=)

  • Hash Cond: (c_3.id = gols.contract_id)
53. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=7.08..40,777.50 rows=603,827 width=92) (actual rows= loops=)

  • Hash Cond: (c_3.id = gll.contract_id)
54. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.58..18,127.49 rows=603,827 width=84) (actual rows= loops=)

55. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.58..12.68 rows=1 width=80) (actual rows= loops=)

56. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.43..9.51 rows=1 width=60) (actual rows= loops=)

57. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.29..6.33 rows=1 width=40) (actual rows= loops=)

58. 0.000 0.000 ↓ 0.0

Index Scan using idx_system_parameters_code_uniq on system_parameters gray_minv (cost=0.14..3.16 rows=1 width=20) (actual rows= loops=)

  • Index Cond: (code = 'gray_minV'::text)
59. 0.000 0.000 ↓ 0.0

Index Scan using idx_system_parameters_code_uniq on system_parameters oral_minv (cost=0.14..3.16 rows=1 width=20) (actual rows= loops=)

  • Index Cond: (code = 'oral_minv'::text)
60. 0.000 0.000 ↓ 0.0

Index Scan using idx_system_parameters_code_uniq on system_parameters oral_k0 (cost=0.14..3.16 rows=1 width=20) (actual rows= loops=)

  • Index Cond: (code = 'oral_k0'::text)
61. 0.000 0.000 ↓ 0.0

Index Scan using idx_system_parameters_code_uniq on system_parameters oral_iopl (cost=0.14..3.16 rows=1 width=20) (actual rows= loops=)

  • Index Cond: (code = 'oral_iopl'::text)
62. 0.000 0.000 ↓ 0.0

CTE Scan on selected_contracts c_3 (cost=0.00..12,076.54 rows=603,827 width=4) (actual rows= loops=)

63. 0.000 0.000 ↓ 0.0

Hash (cost=4.00..4.00 rows=200 width=12) (actual rows= loops=)

64. 0.000 0.000 ↓ 0.0

CTE Scan on grey_list_limits gll (cost=0.00..4.00 rows=200 width=12) (actual rows= loops=)

65. 0.000 0.000 ↓ 0.0

Hash (cost=8.00..8.00 rows=400 width=37) (actual rows= loops=)

66. 0.000 0.000 ↓ 0.0

CTE Scan on grouped_orange_list_sum gols (cost=0.00..8.00 rows=400 width=37) (actual rows= loops=)

67. 0.000 0.000 ↓ 0.0

Hash (cost=523,635.70..523,635.70 rows=1,224,958 width=12) (actual rows= loops=)

68. 0.000 0.000 ↓ 0.0

Seq Scan on accounts a_1 (cost=0.00..523,635.70 rows=1,224,958 width=12) (actual rows= loops=)

  • Filter: (account_type_id = 4)
69.          

CTE contracts_block_type

70. 0.000 0.000 ↓ 0.0

Unique (cost=70,055.89..764,936.71 rows=200 width=24) (actual rows= loops=)

71. 0.000 0.000 ↓ 0.0

Merge Join (cost=70,055.89..727,511.84 rows=14,969,947 width=24) (actual rows= loops=)

  • Merge Cond: (csh.contract_id = c_4.id)
72. 0.000 0.000 ↓ 0.0

Index Scan using idx_contract_status_histories_contract_id on contract_status_histories csh (cost=0.56..408,175.52 rows=9,892,714 width=24) (actual rows= loops=)

73. 0.000 0.000 ↓ 0.0

Sort (cost=70,055.33..71,564.90 rows=603,827 width=4) (actual rows= loops=)

  • Sort Key: c_4.id
74. 0.000 0.000 ↓ 0.0

CTE Scan on selected_contracts c_4 (cost=0.00..12,076.54 rows=603,827 width=4) (actual rows= loops=)

75.          

CTE old_and_new_contract_status

76. 0.000 0.000 ↓ 0.0

Merge Join (cost=1,366,323.45..687,488,588.62 rows=24,948,655,283 width=16) (actual rows= loops=)

  • Merge Cond: (c_5.id = rl.contract_id)
77.          

Initplan (for Merge Join)

78. 0.000 0.000 ↓ 0.0

Seq Scan on block_types bt (cost=0.00..1.06 rows=1 width=4) (actual rows= loops=)

  • Filter: (code = 'automatic'::text)
79. 0.000 0.000 ↓ 0.0

Seq Scan on contract_statuses cs_1 (cost=0.00..1.07 rows=1 width=2) (actual rows= loops=)

  • Filter: (code = 'B'::text)
80. 0.000 0.000 ↓ 0.0

Seq Scan on contract_statuses cs_2 (cost=0.00..1.07 rows=1 width=2) (actual rows= loops=)

  • Filter: (code = 'B'::text)
81. 0.000 0.000 ↓ 0.0

Seq Scan on contract_statuses cs_3 (cost=0.00..1.07 rows=1 width=2) (actual rows= loops=)

  • Filter: (code = 'O'::text)
82. 0.000 0.000 ↓ 0.0

Seq Scan on contract_statuses cs_4 (cost=0.00..1.07 rows=1 width=2) (actual rows= loops=)

  • Filter: (code = 'V'::text)
83. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=70,066.98..82,143.52 rows=603,827 width=10) (actual rows= loops=)

  • Merge Cond: (c_5.id = cbt.contract_id)
84. 0.000 0.000 ↓ 0.0

Sort (cost=70,055.33..71,564.90 rows=603,827 width=6) (actual rows= loops=)

  • Sort Key: c_5.id
85. 0.000 0.000 ↓ 0.0

CTE Scan on selected_contracts c_5 (cost=0.00..12,076.54 rows=603,827 width=6) (actual rows= loops=)

86. 0.000 0.000 ↓ 0.0

Sort (cost=11.64..12.14 rows=200 width=8) (actual rows= loops=)

  • Sort Key: cbt.contract_id
87. 0.000 0.000 ↓ 0.0

CTE Scan on contracts_block_type cbt (cost=0.00..4.00 rows=200 width=8) (actual rows= loops=)

88. 0.000 0.000 ↓ 0.0

Materialize (cost=1,296,251.11..1,337,568.67 rows=8,263,511 width=52) (actual rows= loops=)

89. 0.000 0.000 ↓ 0.0

Sort (cost=1,296,251.11..1,316,909.89 rows=8,263,511 width=52) (actual rows= loops=)

  • Sort Key: rl.contract_id
90. 0.000 0.000 ↓ 0.0

CTE Scan on result_limits rl (cost=0.00..165,270.22 rows=8,263,511 width=52) (actual rows= loops=)

91.          

CTE contract_for_update

92. 0.000 0.000 ↓ 0.0

LockRows (cost=111,762.22..1,262,738,146.85 rows=24,823,912,007 width=42) (actual rows= loops=)

93. 0.000 0.000 ↓ 0.0

Hash Join (cost=111,762.22..1,014,499,026.78 rows=24,823,912,007 width=42) (actual rows= loops=)

  • Hash Cond: (oancs.contract_id = contracts_1.id)
94. 0.000 0.000 ↓ 0.0

CTE Scan on old_and_new_contract_status oancs (cost=0.00..561,344,743.87 rows=24,823,912,007 width=36) (actual rows= loops=)

  • Filter: (new_status_id <> old_status_id)
95. 0.000 0.000 ↓ 0.0

Hash (cost=90,769.54..90,769.54 rows=1,207,654 width=10) (actual rows= loops=)

96. 0.000 0.000 ↓ 0.0

Seq Scan on contracts contracts_1 (cost=0.00..90,769.54 rows=1,207,654 width=10) (actual rows= loops=)

97.          

CTE updated_contracts

98. 0.000 0.000 ↓ 0.0

Update on contracts contracts_2 (cost=178,999.93..584,137,689.12 rows=744,717,360 width=555) (actual rows= loops=)

99.          

Initplan (for Update)

100. 0.000 0.000 ↓ 0.0

Seq Scan on sp_logged_action_types (cost=0.00..2.94 rows=1 width=2) (actual rows= loops=)

  • Filter: (code = 'b'::text)
101. 0.000 0.000 ↓ 0.0

Seq Scan on sp_logged_action_types sp_logged_action_types_1 (cost=0.00..2.94 rows=1 width=2) (actual rows= loops=)

  • Filter: (code = 'o'::text)
102. 0.000 0.000 ↓ 0.0

Seq Scan on sp_logged_action_types sp_logged_action_types_2 (cost=0.00..2.94 rows=1 width=2) (actual rows= loops=)

  • Filter: (code = 'v'::text)
103. 0.000 0.000 ↓ 0.0

Seq Scan on status_reasons (cost=0.00..1.59 rows=1 width=2) (actual rows= loops=)

  • Filter: (code = '120'::text)
104. 0.000 0.000 ↓ 0.0

Seq Scan on status_reasons status_reasons_1 (cost=0.00..1.59 rows=1 width=2) (actual rows= loops=)

  • Filter: (code = '130'::text)
105. 0.000 0.000 ↓ 0.0

Seq Scan on status_reasons status_reasons_2 (cost=0.00..1.59 rows=1 width=2) (actual rows= loops=)

  • Filter: (code = '141'::text)
106. 0.000 0.000 ↓ 0.0

Hash Join (cost=178,986.35..584,137,675.55 rows=744,717,360 width=555) (actual rows= loops=)

  • Hash Cond: (contract_for_update.contract_id = contracts_2.id)
107. 0.000 0.000 ↓ 0.0

Hash Join (cost=1.14..563,021,840.40 rows=744,717,360 width=74) (actual rows= loops=)

  • Hash Cond: (contract_for_update.new_status_id = contract_statuses.id)
108. 0.000 0.000 ↓ 0.0

CTE Scan on contract_for_update (cost=0.00..496,478,240.14 rows=24,823,912,007 width=36) (actual rows= loops=)

109. 0.000 0.000 ↓ 0.0

Hash (cost=1.06..1.06 rows=6 width=40) (actual rows= loops=)

110. 0.000 0.000 ↓ 0.0

Seq Scan on contract_statuses (cost=0.00..1.06 rows=6 width=40) (actual rows= loops=)

111. 0.000 0.000 ↓ 0.0

Hash (cost=90,769.54..90,769.54 rows=1,207,654 width=469) (actual rows= loops=)

112. 0.000 0.000 ↓ 0.0

Seq Scan on contracts contracts_2 (cost=0.00..90,769.54 rows=1,207,654 width=469) (actual rows= loops=)

113.          

CTE inserted_sp_logged_actions

114. 0.000 0.000 ↓ 0.0

Insert on sp_logged_actions (cost=0.00..20,479,727.40 rows=744,717,360 width=246) (actual rows= loops=)

115. 0.000 0.000 ↓ 0.0

CTE Scan on updated_contracts (cost=0.00..20,479,727.40 rows=744,717,360 width=246) (actual rows= loops=)

116. 0.000 0.000 ↓ 0.0

CTE Scan on inserted_sp_logged_actions (cost=0.00..14,894,347.20 rows=744,717,360 width=4) (actual rows= loops=)