explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 419Ru

Settings
# exclusive inclusive rows x rows loops node
1. 84.877 27,581.450 ↓ 19.4 31,848 1

GroupAggregate (cost=39,082.31..39,127.41 rows=1,640 width=70) (actual time=27,377.156..27,581.450 rows=31,848 loops=1)

  • Group Key: (to_char(bo.created_at, 'YYYY-MM-DD'::text)), users.city_id, bom.dictionary_currency_id
2.          

CTE _descendants

3. 135.750 1,225.821 ↓ 212.4 153,155 1

Recursive Union (cost=0.42..1,665.61 rows=721 width=64) (actual time=0.743..1,225.821 rows=153,155 loops=1)

4. 0.744 0.744 ↑ 1.0 1 1

Index Scan using linear_hierarchy_relations_pkey on linear_hierarchy_relations (cost=0.42..8.44 rows=1 width=64) (actual time=0.740..0.744 rows=1 loops=1)

  • Index Cond: (id = 3,796)
5. 141.339 1,089.327 ↓ 24.4 1,760 87

Nested Loop (cost=0.42..164.28 rows=72 width=64) (actual time=0.015..12.521 rows=1,760 loops=87)

6. 29.058 29.058 ↓ 176.0 1,760 87

WorkTable Scan on _descendants _descendants_1 (cost=0.00..0.20 rows=10 width=40) (actual time=0.000..0.334 rows=1,760 loops=87)

7. 918.930 918.930 ↑ 7.0 1 153,155

Index Scan using linear_hierarchy_relations_parent_id_index on linear_hierarchy_relations linear_hierarchy_relations_1 (cost=0.42..16.30 rows=7 width=28) (actual time=0.004..0.006 rows=1 loops=153,155)

  • Index Cond: (parent_id = _descendants_1.id)
8. 763.605 27,496.573 ↓ 178.3 292,441 1

Sort (cost=37,416.70..37,420.80 rows=1,640 width=43) (actual time=27,377.142..27,496.573 rows=292,441 loops=1)

  • Sort Key: (to_char(bo.created_at, 'YYYY-MM-DD'::text)), users.city_id, bom.dictionary_currency_id
  • Sort Method: external merge Disk: 10,136kB
9. 595.778 26,732.968 ↓ 178.3 292,441 1

Nested Loop (cost=2.14..37,329.13 rows=1,640 width=43) (actual time=3.361..26,732.968 rows=292,441 loops=1)

10. 0.000 21,600.677 ↓ 165.4 504,057 1

Nested Loop (cost=1.70..33,389.76 rows=3,048 width=20) (actual time=2.826..21,600.677 rows=504,057 loops=1)

11. 253.903 8,674.345 ↓ 107.0 925,124 1

Nested Loop (cost=1.27..2,558.31 rows=8,649 width=12) (actual time=2.310..8,674.345 rows=925,124 loops=1)

  • Join Filter: (_descendants.account_id = ua.account_id)
12. 125.558 3,825.822 ↓ 638.1 153,154 1

Nested Loop (cost=0.84..1,999.69 rows=240 width=12) (actual time=1.317..3,825.822 rows=153,154 loops=1)

13. 90.023 2,628.186 ↓ 638.1 153,154 1

Nested Loop (cost=0.42..1,867.03 rows=240 width=12) (actual time=1.284..2,628.186 rows=153,154 loops=1)

14. 1,466.085 1,466.085 ↓ 638.1 153,154 1

CTE Scan on _descendants (cost=0.00..18.03 rows=240 width=4) (actual time=1.027..1,466.085 rows=153,154 loops=1)

  • Filter: (array_length(_materialized_path, 1) > 1)
  • Rows Removed by Filter: 1
15. 1,072.078 1,072.078 ↑ 1.0 1 153,154

Index Scan using accounts_pkey on accounts a (cost=0.42..7.70 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=153,154)

  • Index Cond: (id = _descendants.account_id)
16. 1,072.078 1,072.078 ↑ 1.0 1 153,154

Index Scan using users_pkey on users (cost=0.42..0.55 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=153,154)

  • Index Cond: (id = a.user_id)
17. 4,594.620 4,594.620 ↑ 6.0 6 153,154

Index Scan using user_actions_account_id_index on user_actions ua (cost=0.43..1.88 rows=36 width=16) (actual time=0.009..0.030 rows=6 loops=153,154)

  • Index Cond: (account_id = a.id)
  • Filter: ((status)::text = 'ready'::text)
  • Rows Removed by Filter: 0
18. 12,951.736 12,951.736 ↑ 3.0 1 925,124

Index Scan using balance_operations_user_action_id_index on balance_operations bo (cost=0.43..3.53 rows=3 width=24) (actual time=0.012..0.014 rows=1 loops=925,124)

  • Index Cond: (user_action_id = ua.id)
  • Filter: ((account_id = 104,089) AND (balance_operation_type_id <> ALL ('{9,2,11,16}'::integer[])))
  • Rows Removed by Filter: 14
19. 4,536.513 4,536.513 ↑ 1.0 1 504,057

Index Scan using balance_operations_money_balance_operation_id_index on balance_operations_money bom (cost=0.43..1.28 rows=1 width=15) (actual time=0.009..0.009 rows=1 loops=504,057)

  • Index Cond: (balance_operation_id = bo.id)
  • Filter: (amount > '0'::numeric)
  • Rows Removed by Filter: 0
Planning time : 8.564 ms
Execution time : 27,589.566 ms