explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VONs

Settings
# exclusive inclusive rows x rows loops node
1. 82.780 10,573.120 ↑ 3.9 31,848 1

GroupAggregate (cost=492,556.86..495,965.18 rows=123,939 width=70) (actual time=10,368.254..10,573.120 rows=31,848 loops=1)

  • Group Key: (to_char(bo.created_at, 'YYYY-MM-DD'::text)), users.city_id, bom.dictionary_currency_id
2. 812.261 10,490.340 ↓ 2.4 292,441 1

Sort (cost=492,556.86..492,866.70 rows=123,939 width=43) (actual time=10,368.240..10,490.340 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,160kB
3. 363.417 9,678.079 ↓ 2.4 292,441 1

Hash Join (cost=269,702.33..478,257.07 rows=123,939 width=43) (actual time=8,126.312..9,678.079 rows=292,441 loops=1)

  • Hash Cond: (a.user_id = users.id)
4. 178.242 9,184.934 ↓ 2.4 292,441 1

Hash Join (cost=256,586.98..462,088.51 rows=123,939 width=19) (actual time=7,996.511..9,184.934 rows=292,441 loops=1)

  • Hash Cond: (ua.account_id = a.id)
5. 249.015 8,915.044 ↓ 2.4 292,441 1

Hash Join (cost=247,041.14..449,532.02 rows=124,058 width=27) (actual time=7,904.792..8,915.044 rows=292,441 loops=1)

  • Hash Cond: (bo.user_action_id = ua.id)
6. 45.676 3,820.332 ↓ 1.2 315,410 1

Gather (cost=217,358.93..410,710.17 rows=262,682 width=23) (actual time=3,058.841..3,820.332 rows=315,410 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
7. 1,506.961 3,774.656 ↑ 1.0 105,137 3 / 3

Parallel Hash Join (cost=216,358.93..383,441.97 rows=109,451 width=23) (actual time=3,061.894..3,774.656 rows=105,137 loops=3)

  • Hash Cond: (bom.balance_operation_id = bo.id)
8. 1,264.533 1,264.533 ↑ 1.2 2,652,712 3 / 3

Parallel Seq Scan on balance_operations_money bom (cost=0.00..124,850.74 rows=3,312,115 width=15) (actual time=0.034..1,264.533 rows=2,652,712 loops=3)

  • Filter: (amount > '0'::numeric)
  • Rows Removed by Filter: 594,568
9. 65.507 1,003.162 ↑ 1.1 178,056 3 / 3

Parallel Hash (cost=212,624.20..212,624.20 rows=203,418 width=24) (actual time=1,003.162..1,003.162 rows=178,056 loops=3)

  • Buckets: 65,536 Batches: 8 Memory Usage: 4,224kB
10. 915.293 937.655 ↑ 1.1 178,056 3 / 3

Parallel Bitmap Heap Scan on balance_operations bo (cost=9,475.31..212,624.20 rows=203,418 width=24) (actual time=80.323..937.655 rows=178,056 loops=3)

  • Recheck Cond: (account_id = 104,089)
  • Rows Removed by Index Recheck: 2,540,661
  • Filter: (balance_operation_type_id <> ALL ('{9,2,11,16}'::integer[]))
  • Rows Removed by Filter: 608
  • Heap Blocks: exact=16,072 lossy=20,230
11. 22.362 22.362 ↓ 1.1 535,991 1 / 3

Bitmap Index Scan on balance_operations_account_id_index (cost=0.00..9,353.26 rows=506,243 width=0) (actual time=67.086..67.086 rows=535,991 loops=1)

  • Index Cond: (account_id = 104,089)
12. 301.757 4,845.697 ↓ 1.4 925,124 1

Hash (cost=17,670.65..17,670.65 rows=654,205 width=20) (actual time=4,845.696..4,845.697 rows=925,124 loops=1)

  • Buckets: 65,536 Batches: 16 Memory Usage: 3,455kB
13. 247.807 4,543.940 ↓ 1.4 925,124 1

Nested Loop (cost=1,687.06..17,670.65 rows=654,205 width=20) (actual time=869.066..4,543.940 rows=925,124 loops=1)

14. 123.294 926.745 ↓ 765.8 153,154 1

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

  • Group Key: _descendants.account_id
15. 803.451 803.451 ↓ 638.1 153,154 1

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

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

CTE _descendants

17. 80.599 640.041 ↓ 212.4 153,155 1

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

18. 0.032 0.032 ↑ 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.031..0.032 rows=1 loops=1)

  • Index Cond: (id = 3,796)
19. 78.282 559.410 ↓ 24.4 1,760 87

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

20. 21.663 21.663 ↓ 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.249 rows=1,760 loops=87)

21. 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)
22. 3,369.388 3,369.388 ↑ 6.0 6 153,154

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

  • Index Cond: (account_id = _descendants.account_id)
  • Filter: ((status)::text = 'ready'::text)
  • Rows Removed by Filter: 0
23. 49.161 91.648 ↑ 1.0 252,615 1

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

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

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

25. 54.977 129.728 ↑ 1.0 246,727 1

Hash (cost=9,067.27..9,067.27 rows=246,727 width=8) (actual time=129.728..129.728 rows=246,727 loops=1)

  • Buckets: 131,072 Batches: 4 Memory Usage: 3,444kB
26. 74.751 74.751 ↑ 1.0 246,727 1

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

Planning time : 2.381 ms
Execution time : 10,656.317 ms