explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3tEB

Settings
# exclusive inclusive rows x rows loops node
1. 81.944 11,485.263 ↑ 3.9 31,848 1

GroupAggregate (cost=492,556.83..495,965.15 rows=123,939 width=70) (actual time=11,283.319..11,485.263 rows=31,848 loops=1)

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

Sort (cost=492,556.83..492,866.68 rows=123,939 width=43) (actual time=11,283.300..11,403.319 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. 355.772 10,521.987 ↓ 2.4 292,441 1

Hash Join (cost=269,702.33..478,257.04 rows=123,939 width=43) (actual time=8,987.049..10,521.987 rows=292,441 loops=1)

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

Hash Join (cost=256,586.98..462,088.48 rows=123,939 width=19) (actual time=8,792.960..9,972.447 rows=292,441 loops=1)

  • Hash Cond: (ua.account_id = a.id)
5. 253.768 9,673.498 ↓ 2.4 292,441 1

Hash Join (cost=247,041.14..449,531.99 rows=124,058 width=27) (actual time=8,667.602..9,673.498 rows=292,441 loops=1)

  • Hash Cond: (bo.user_action_id = ua.id)
6. 44.626 4,470.061 ↓ 1.2 315,410 1

Gather (cost=217,358.93..410,710.15 rows=262,682 width=23) (actual time=3,714.722..4,470.061 rows=315,410 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
7. 1,483.896 4,425.435 ↑ 1.0 105,137 3 / 3

Parallel Hash Join (cost=216,358.93..383,441.95 rows=109,451 width=23) (actual time=3,712.464..4,425.435 rows=105,137 loops=3)

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

Parallel Seq Scan on balance_operations_money bom (cost=0.00..124,850.72 rows=3,312,114 width=15) (actual time=0.088..1,582.326 rows=2,652,712 loops=3)

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

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

  • Buckets: 65,536 Batches: 8 Memory Usage: 4,224kB
10. 1,264.722 1,291.927 ↑ 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=89.826..1,291.927 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=17,440 lossy=22,667
11. 27.205 27.205 ↓ 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=81.614..81.614 rows=535,991 loops=1)

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

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

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

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

14. 120.895 866.602 ↓ 765.8 153,154 1

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

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

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

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

CTE _descendants

17. 77.223 590.976 ↓ 212.4 153,155 1

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

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

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

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

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

21. 306.310 306.310 ↑ 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.002 rows=1 loops=153,155)

  • Index Cond: (parent_id = _descendants_1.id)
22. 3,675.696 3,675.696 ↑ 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.008..0.024 rows=6 loops=153,154)

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

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

  • Buckets: 131,072 Batches: 4 Memory Usage: 3,511kB
24. 69.944 69.944 ↑ 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..69.944 rows=252,615 loops=1)

25. 57.593 193.768 ↑ 1.0 246,727 1

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

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

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

Planning time : 1.740 ms
Execution time : 11,565.938 ms