explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9twJ

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

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

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

Sort (cost=492,556.86..492,866.70 rows=123,939 width=43) (actual time=10,979.203..11,100.740 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. 356.171 10,280.814 ↓ 2.4 292,441 1

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

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

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

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

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

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

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

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

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

  • Hash Cond: (bom.balance_operation_id = bo.id)
8. 1,365.789 1,365.789 ↑ 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,365.789 rows=2,652,712 loops=3)

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

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

  • Buckets: 65,536 Batches: 8 Memory Usage: 4,224kB
10. 1,591.389 1,613.699 ↑ 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=79.000..1,613.699 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,796 lossy=23,083
11. 22.310 22.310 ↓ 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=66.930..66.930 rows=535,991 loops=1)

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

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

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

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

14. 117.754 872.392 ↓ 765.8 153,154 1

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

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

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

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

CTE _descendants

17. 76.949 599.766 ↓ 212.4 153,155 1

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

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

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

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

20. 20.619 20.619 ↓ 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.237 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. 50.228 94.127 ↑ 1.0 252,615 1

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

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

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

25. 54.708 144.710 ↑ 1.0 246,727 1

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

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

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

Planning time : 3.213 ms
Execution time : 11,267.026 ms