explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DfJQ

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

GroupAggregate (cost=165,297.49..165,961.51 rows=22,134 width=70) (actual time=5,020.397..5,124.251 rows=42,712 loops=1)

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

Sort (cost=165,297.49..165,352.83 rows=22,134 width=43) (actual time=5,020.382..5,067.293 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. 196.060 4,727.695 ↓ 6.0 133,899 1

Nested Loop (cost=104,104.63..163,700.09 rows=22,134 width=43) (actual time=3,470.766..4,727.695 rows=133,899 loops=1)

4. 208.992 3,726.489 ↓ 4.0 134,191 1

Hash Join (cost=104,104.20..114,432.36 rows=33,566 width=20) (actual time=3,470.727..3,726.489 rows=134,191 loops=1)

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

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

6. 38.596 3,469.547 ↓ 4.0 134,191 1

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

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

Hash Join (cost=63,152.35..103,684.62 rows=33,566 width=20) (actual time=1,182.525..3,430.951 rows=134,191 loops=1)

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

Hash Join (cost=53,606.52..92,603.68 rows=33,566 width=28) (actual time=1,080.690..3,226.488 rows=134,191 loops=1)

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

Nested Loop (cost=1,685.40..36,111.14 rows=200,921 width=36) (actual time=779.541..2,706.670 rows=347,910 loops=1)

10. 108.545 832.794 ↓ 765.8 153,154 1

HashAggregate (cost=1,684.84..1,686.84 rows=200 width=4) (actual time=779.495..832.794 rows=153,154 loops=1)

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

CTE Scan on _descendants (cost=1,663.81..1,681.84 rows=240 width=4) (actual time=0.064..724.249 rows=153,154 loops=1)

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

CTE _descendants

13. 70.252 585.497 ↓ 212.4 153,155 1

Recursive Union (cost=0.42..1,663.81 rows=721 width=44) (actual time=0.032..585.497 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=44) (actual time=0.030..0.031 rows=1 loops=1)

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

Nested Loop (cost=0.42..164.10 rows=72 width=44) (actual time=0.007..5.922 rows=1,760 loops=87)

16. 20.967 20.967 ↓ 176.0 1,760 87

WorkTable Scan on _descendants _descendants_1 (cost=0.00..0.20 rows=10 width=36) (actual time=0.000..0.241 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=12) (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. 51.360 300.792 ↓ 1.0 233,605 1

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

  • Buckets: 131,072 Batches: 4 Memory Usage: 3,313kB
20. 229.820 249.432 ↓ 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=27.576..249.432 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. 19.612 19.612 ↑ 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=19.612..19.612 rows=243,628 loops=1)

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

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

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

Seq Scan on accounts a (cost=0.00..5,401.15 rows=252,615 width=8) (actual time=0.007..47.239 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 : 2.291 ms
Execution time : 5,130.885 ms