explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PTcz

Settings
# exclusive inclusive rows x rows loops node
1. 89.931 4,153.635 ↓ 4,741.2 132,754 1

Nested Loop (cost=60,743.69..106,835.10 rows=28 width=343) (actual time=1,312.028..4,153.635 rows=132,754 loops=1)

2. 383.176 3,665.442 ↓ 4,741.2 132,754 1

Hash Join (cost=60,743.27..106,821.85 rows=28 width=279) (actual time=1,311.957..3,665.442 rows=132,754 loops=1)

  • Hash Cond: ((bo.account_id = ua.account_id) AND (bo.user_action_id = ua.id))
3. 104.250 2,893.624 ↓ 1.7 348,008 1

Nested Loop (cost=1,689.60..36,115.35 rows=200,921 width=40) (actual time=897.640..2,893.624 rows=348,008 loops=1)

4. 123.933 951.514 ↓ 765.8 153,155 1

HashAggregate (cost=1,689.04..1,691.04 rows=200 width=4) (actual time=897.603..951.514 rows=153,155 loops=1)

  • Group Key: _descendants.account_id
5. 827.581 827.581 ↓ 212.4 153,155 1

CTE Scan on _descendants (cost=1,665.61..1,680.03 rows=721 width=4) (actual time=0.049..827.581 rows=153,155 loops=1)

6.          

CTE _descendants

7. 84.580 663.523 ↓ 212.4 153,155 1

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

8. 0.045 0.045 ↑ 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.044..0.045 rows=1 loops=1)

  • Index Cond: (id = 3,796)
9. 97.248 578.898 ↓ 24.4 1,760 87

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

10. 22.185 22.185 ↓ 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.255 rows=1,760 loops=87)

11. 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)
12. 1,837.860 1,837.860 ↑ 26.5 2 153,155

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

  • Index Cond: ((balance_operation_type_id = 9) AND (account_id = _descendants.account_id))
13. 130.249 388.642 ↓ 1.0 233,591 1

Hash (cost=48,124.37..48,124.37 rows=231,420 width=239) (actual time=388.641..388.642 rows=233,591 loops=1)

  • Buckets: 16,384 (originally 16384) Batches: 32 (originally 16) Memory Usage: 3,969kB
14. 235.593 258.393 ↓ 1.0 233,605 1

Bitmap Heap Scan on user_actions ua (cost=4,366.89..48,124.37 rows=231,420 width=239) (actual time=30.688..258.393 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
15. 22.800 22.800 ↑ 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=22.800..22.800 rows=243,628 loops=1)

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

Index Scan using accounts_pkey on accounts a (cost=0.42..0.47 rows=1 width=68) (actual time=0.003..0.003 rows=1 loops=132,754)

  • Index Cond: (id = bo.account_id)
Planning time : 2.456 ms
Execution time : 4,163.354 ms