explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VjII

Settings
# exclusive inclusive rows x rows loops node
1. 66.229 5,227.570 ↓ 1.9 42,712 1

GroupAggregate (cost=165,299.29..165,963.31 rows=22,134 width=70) (actual time=5,106.808..5,227.570 rows=42,712 loops=1)

  • Group Key: (to_char(bo.created_at, 'YYYY-MM-DD'::text)), users.city_id, bom.dictionary_currency_id
2. 343.893 5,161.341 ↓ 6.0 133,899 1

Sort (cost=165,299.29..165,354.63 rows=22,134 width=43) (actual time=5,106.793..5,161.341 rows=133,899 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,392kB
3. 176.158 4,817.448 ↓ 6.0 133,899 1

Nested Loop (cost=104,106.43..163,701.89 rows=22,134 width=43) (actual time=3,577.699..4,817.448 rows=133,899 loops=1)

4. 210.565 3,836.144 ↓ 4.0 134,191 1

Hash Join (cost=104,106.00..114,434.16 rows=33,566 width=20) (actual time=3,577.662..3,836.144 rows=134,191 loops=1)

  • Hash Cond: (users.id = a.user_id)
5. 48.925 48.925 ↑ 1.0 246,727 1

Seq Scan on users (cost=0.00..9,067.27 rows=246,727 width=8) (actual time=0.014..48.925 rows=246,727 loops=1)

6. 38.001 3,576.654 ↓ 4.0 134,191 1

Hash (cost=103,686.42..103,686.42 rows=33,566 width=20) (actual time=3,576.654..3,576.654 rows=134,191 loops=1)

  • Buckets: 65,536 (originally 65536) Batches: 4 (originally 1) Memory Usage: 3,585kB
7. 105.365 3,538.653 ↓ 4.0 134,191 1

Hash Join (cost=63,154.15..103,686.42 rows=33,566 width=20) (actual time=1,241.719..3,538.653 rows=134,191 loops=1)

  • Hash Cond: (bo.account_id = a.id)
8. 222.436 3,314.599 ↓ 4.0 134,191 1

Hash Join (cost=53,608.32..92,605.48 rows=33,566 width=28) (actual time=1,122.155..3,314.599 rows=134,191 loops=1)

  • Hash Cond: (bo.user_action_id = ua.id)
9. 77.907 2,788.231 ↓ 1.7 347,910 1

Nested Loop (cost=1,687.20..36,112.94 rows=200,921 width=36) (actual time=817.867..2,788.231 rows=347,910 loops=1)

10. 120.066 872.476 ↓ 765.8 153,154 1

HashAggregate (cost=1,686.64..1,688.64 rows=200 width=4) (actual time=817.820..872.476 rows=153,154 loops=1)

  • Group Key: _descendants.account_id
11. 752.410 752.410 ↓ 638.1 153,154 1

CTE Scan on _descendants (cost=1,665.61..1,683.64 rows=240 width=4) (actual time=0.065..752.410 rows=153,154 loops=1)

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

CTE _descendants

13. 76.542 597.268 ↓ 212.4 153,155 1

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

14. 0.031 0.031 ↑ 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.030..0.031 rows=1 loops=1)

  • Index Cond: (id = 3,796)
15. 40.698 520.695 ↓ 24.4 1,760 87

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

16. 20.532 20.532 ↓ 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.236 rows=1,760 loops=87)

17. 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=28) (actual time=0.002..0.003 rows=1 loops=153,155)

  • Index Cond: (parent_id = _descendants_1.id)
18. 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.006..0.012 rows=2 loops=153,154)

  • Index Cond: ((balance_operation_type_id = 9) AND (account_id = _descendants.account_id))
19. 52.229 303.932 ↓ 1.0 233,605 1

Hash (cost=48,124.37..48,124.37 rows=231,420 width=8) (actual time=303.932..303.932 rows=233,605 loops=1)

  • Buckets: 131,072 Batches: 4 Memory Usage: 3,313kB
20. 230.795 251.703 ↓ 1.0 233,605 1

Bitmap Heap Scan on user_actions ua (cost=4,366.89..48,124.37 rows=231,420 width=8) (actual time=29.030..251.703 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
21. 20.908 20.908 ↑ 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=20.908..20.908 rows=243,628 loops=1)

  • Index Cond: (user_action_type_id = ANY ('{2,3,21}'::integer[]))
22. 63.935 118.689 ↑ 1.0 252,615 1

Hash (cost=5,401.15..5,401.15 rows=252,615 width=8) (actual time=118.689..118.689 rows=252,615 loops=1)

  • Buckets: 131,072 Batches: 4 Memory Usage: 3,511kB
23. 54.754 54.754 ↑ 1.0 252,615 1

Seq Scan on accounts a (cost=0.00..5,401.15 rows=252,615 width=8) (actual time=0.009..54.754 rows=252,615 loops=1)

24. 805.146 805.146 ↑ 1.0 1 134,191

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.006..0.006 rows=1 loops=134,191)

  • Index Cond: (balance_operation_id = bo.id)
Planning time : 3.131 ms
Execution time : 5,235.493 ms