explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yw5F

Settings
# exclusive inclusive rows x rows loops node
1. 56.416 6,864.442 ↓ 2,239.1 42,543 1

GroupAggregate (cost=94,222.87..94,223.44 rows=19 width=70) (actual time=6,760.806..6,864.442 rows=42,543 loops=1)

  • Group Key: (to_char(bo.created_at, 'YYYY-MM-DD'::text)), users.city_id, bom.dictionary_currency_id
2. 433.590 6,808.026 ↓ 6,971.7 132,462 1

Sort (cost=94,222.87..94,222.92 rows=19 width=43) (actual time=6,760.791..6,808.026 rows=132,462 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: 4,352kB
3. 267.885 6,374.436 ↓ 6,971.7 132,462 1

Nested Loop (cost=54,412.34..94,222.47 rows=19 width=43) (actual time=1,126.284..6,374.436 rows=132,462 loops=1)

4. 141.216 5,444.241 ↓ 6,971.7 132,462 1

Nested Loop (cost=54,411.92..94,211.92 rows=19 width=19) (actual time=1,126.256..5,444.241 rows=132,462 loops=1)

  • Join Filter: (ua.account_id = a.id)
5. 120.593 4,773.177 ↓ 6,971.7 132,462 1

Nested Loop (cost=54,411.50..94,202.70 rows=19 width=35) (actual time=1,126.216..4,773.177 rows=132,462 loops=1)

6. 280.052 3,457.798 ↓ 4,741.2 132,754 1

Hash Join (cost=54,411.07..94,161.64 rows=28 width=36) (actual time=1,126.198..3,457.798 rows=132,754 loops=1)

  • Hash Cond: ((bo.account_id = ua.account_id) AND (bo.user_action_id = ua.id))
7. 160.098 2,867.545 ↓ 1.7 347,910 1

Nested Loop (cost=1,685.40..36,111.14 rows=200,921 width=36) (actual time=815.657..2,867.545 rows=347,910 loops=1)

8. 117.917 869.599 ↓ 765.8 153,154 1

HashAggregate (cost=1,684.84..1,686.84 rows=200 width=4) (actual time=815.610..869.599 rows=153,154 loops=1)

  • Group Key: _descendants.account_id
9. 751.682 751.682 ↓ 638.1 153,154 1

CTE Scan on _descendants (cost=1,663.81..1,681.84 rows=240 width=4) (actual time=0.107..751.682 rows=153,154 loops=1)

  • Filter: (array_length(_materialized_path, 1) > 1)
  • Rows Removed by Filter: 1
10.          

CTE _descendants

11. 72.501 607.255 ↓ 212.4 153,155 1

Recursive Union (cost=0.42..1,663.81 rows=721 width=44) (actual time=0.053..607.255 rows=153,155 loops=1)

12. 0.052 0.052 ↑ 1.0 1 1

Index Scan using linear_hierarchy_relations_pkey on linear_hierarchy_relations (cost=0.42..8.44 rows=1 width=44) (actual time=0.050..0.052 rows=1 loops=1)

  • Index Cond: (id = 3,796)
13. 53.835 534.702 ↓ 24.4 1,760 87

Nested Loop (cost=0.42..164.10 rows=72 width=44) (actual time=0.007..6.146 rows=1,760 loops=87)

14. 21.402 21.402 ↓ 176.0 1,760 87

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

15. 459.465 459.465 ↑ 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=12) (actual time=0.002..0.003 rows=1 loops=153,155)

  • Index Cond: (parent_id = _descendants_1.id)
16. 1,837.848 1,837.848 ↑ 26.5 2 153,154

Index Scan using balance_operations_balance_operation_type_id_account_id_idx on balance_operations bo (cost=0.56..171.59 rows=53 width=32) (actual time=0.007..0.012 rows=2 loops=153,154)

  • Index Cond: ((balance_operation_type_id = 9) AND (account_id = _descendants.account_id))
17. 59.847 310.201 ↓ 1.0 233,591 1

Hash (cost=48,124.37..48,124.37 rows=231,420 width=16) (actual time=310.201..310.201 rows=233,591 loops=1)

  • Buckets: 131,072 Batches: 4 Memory Usage: 3,786kB
18. 231.263 250.354 ↓ 1.0 233,605 1

Bitmap Heap Scan on user_actions ua (cost=4,366.89..48,124.37 rows=231,420 width=16) (actual time=27.380..250.354 rows=233,605 loops=1)

  • Recheck Cond: (user_action_type_id = ANY ('{2,3,21}'::integer[]))
  • Filter: ((status)::text = 'ready'::text)
  • Rows Removed by Filter: 10,022
  • Heap Blocks: exact=34,673
19. 19.091 19.091 ↑ 1.0 243,628 1

Bitmap Index Scan on user_actions_action_type_id_index (cost=0.00..4,309.03 rows=244,768 width=0) (actual time=19.091..19.091 rows=243,628 loops=1)

  • Index Cond: (user_action_type_id = ANY ('{2,3,21}'::integer[]))
20. 1,194.786 1,194.786 ↑ 1.0 1 132,754

Index Scan using balance_operations_money_balance_operation_id_index on balance_operations_money bom (cost=0.43..1.46 rows=1 width=15) (actual time=0.009..0.009 rows=1 loops=132,754)

  • Index Cond: (balance_operation_id = bo.id)
21. 529.848 529.848 ↑ 1.0 1 132,462

Index Scan using accounts_pkey on accounts a (cost=0.42..0.47 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=132,462)

  • Index Cond: (id = bo.account_id)
22. 662.310 662.310 ↑ 1.0 1 132,462

Index Scan using users_pkey on users (cost=0.42..0.55 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=132,462)

  • Index Cond: (id = a.user_id)
Planning time : 4.114 ms
Execution time : 6,870.803 ms