explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Rgr9 : 1

Settings
# exclusive inclusive rows x rows loops node
1. 0.089 17.363 ↓ 17.0 51 1

Sort (cost=836.59..836.59 rows=3 width=480) (actual time=17.359..17.363 rows=51 loops=1)

  • Sort Key: fop.user_id
  • Sort Method: quicksort Memory: 30kB
  • Buffers: shared hit=6,069
2. 0.340 17.274 ↓ 17.0 51 1

Nested Loop (cost=90.7..836.56 rows=3 width=480) (actual time=1.561..17.274 rows=51 loops=1)

  • Buffers: shared hit=6,069
3. 0.058 16.067 ↓ 17.0 51 1

Nested Loop (cost=90.43..771.38 rows=3 width=210) (actual time=1.516..16.067 rows=51 loops=1)

  • Buffers: shared hit=5,707
4. 0.071 16.009 ↓ 17.0 51 1

Nested Loop (cost=90.15..768.18 rows=3 width=187) (actual time=1.51..16.009 rows=51 loops=1)

  • Buffers: shared hit=5,707
5. 0.041 15.887 ↓ 17.0 51 1

Nested Loop (cost=90..767.66 rows=3 width=186) (actual time=1.505..15.887 rows=51 loops=1)

  • Buffers: shared hit=5,605
6. 0.044 15.744 ↓ 17.0 51 1

Nested Loop (cost=89.86..767.15 rows=3 width=185) (actual time=1.497..15.744 rows=51 loops=1)

  • Buffers: shared hit=5,503
7. 1.388 15.496 ↓ 17.0 51 1

Hash Join (cost=89.58..764.03 rows=3 width=150) (actual time=1.472..15.496 rows=51 loops=1)

  • Buffers: shared hit=5,350
8. 1.940 8.020 ↑ 1.0 662 1

Hash Join (cost=44.79..391.33 rows=662 width=129) (actual time=0.66..8.02 rows=662 loops=1)

  • Buffers: shared hit=2,677
9. 0.164 0.164 ↑ 1.0 662 1

Seq Scan on financial_operation fop (cost=0..18.62 rows=662 width=108) (actual time=0.006..0.164 rows=662 loops=1)

  • Buffers: shared hit=12
10. 0.339 0.620 ↑ 1.0 1,324 1

Hash (cost=28.24..28.24 rows=1,324 width=29) (actual time=0.619..0.62 rows=1,324 loops=1)

  • Buffers: shared hit=15
11. 0.281 0.281 ↑ 1.0 1,324 1

Seq Scan on financial_transaction from_trn (cost=0..28.24 rows=1,324 width=29) (actual time=0.005..0.281 rows=1,324 loops=1)

  • Buffers: shared hit=15
12.          

SubPlan (for Hash Join)

13. 1.324 5.296 ↑ 1.0 1 1,324

Limit (cost=4.02..9.85 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=1,324)

  • Buffers: shared hit=2,650
14. 2.648 3.972 ↑ 1.0 1 1,324

Bitmap Heap Scan on financial_transaction trn_2 (cost=4.02..9.85 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=1,324)

  • Filter: (trn_2.sum < '0'::numeric)
  • Heap Blocks: exact=1,326
  • Buffers: shared hit=2,650
15. 1.324 1.324 ↑ 1.0 2 1,324

Bitmap Index Scan on idx_financial_transaction_fiop_hash (cost=0..4.01 rows=2 width=0) (actual time=0.001..0.001 rows=2 loops=1,324)

  • Index Cond: (fop.id = trn_2.financial_operation_id)
  • Buffers: shared hit=1,324
16. 0.471 0.792 ↑ 1.0 1,324 1

Hash (cost=28.24..28.24 rows=1,324 width=29) (actual time=0.792..0.792 rows=1,324 loops=1)

  • Buffers: shared hit=15
17. 0.321 0.321 ↑ 1.0 1,324 1

Seq Scan on financial_transaction to_trn (cost=0..28.24 rows=1,324 width=29) (actual time=0.015..0.321 rows=1,324 loops=1)

  • Buffers: shared hit=15
18.          

SubPlan (for Hash Join)

19. 1.324 5.296 ↑ 1.0 1 1,324

Limit (cost=4.02..9.85 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=1,324)

  • Buffers: shared hit=2,658
20. 2.648 3.972 ↑ 1.0 1 1,324

Bitmap Heap Scan on financial_transaction trn_3 (cost=4.02..9.85 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=1,324)

  • Filter: (trn_3.sum >= '0'::numeric)
  • Heap Blocks: exact=1,334
  • Buffers: shared hit=2,658
21. 1.324 1.324 ↑ 1.0 2 1,324

Bitmap Index Scan on idx_financial_transaction_fiop_hash (cost=0..4.01 rows=2 width=0) (actual time=0.001..0.001 rows=2 loops=1,324)

  • Index Cond: (fop.id = trn_3.financial_operation_id)
  • Buffers: shared hit=1,324
22. 0.204 0.204 ↑ 1.0 1 51

Index Scan using idx_124355_primary on user usr (cost=0.28..1.04 rows=1 width=35) (actual time=0.004..0.004 rows=1 loops=51)

  • Index Cond: (usr.id = fop.user_id)
  • Buffers: shared hit=153
23. 0.102 0.102 ↑ 1.0 1 51

Index Scan using idx_123765_primary on currency from_curr (cost=0.14..0.17 rows=1 width=17) (actual time=0.002..0.002 rows=1 loops=51)

  • Index Cond: (from_curr.id = from_trn.currency_id)
  • Buffers: shared hit=102
24. 0.051 0.051 ↑ 1.0 1 51

Index Scan using idx_123765_primary on currency to_curr (cost=0.14..0.17 rows=1 width=17) (actual time=0.001..0.001 rows=1 loops=51)

  • Index Cond: (to_curr.id = to_trn.currency_id)
  • Buffers: shared hit=102
25. 0.000 0.000 ↓ 0.0 0 51

Index Scan using idx_123916_primary on game game (cost=0.28..1.07 rows=1 width=23) (actual time=0..0 rows=0 loops=51)

  • Index Cond: (fop.game_id = game.id)
26. 0.102 0.102 ↓ 0.0 0 51

Index Scan using idx_123688_balance_lof_fo_fk_idx on balance_log bal (cost=0.28..0.37 rows=1 width=26) (actual time=0.002..0.002 rows=0 loops=51)

  • Index Cond: (bal.financial_operation_id = fop.id)
  • Buffers: shared hit=106
27.          

SubPlan (for Nested Loop)

28. 0.051 0.204 ↑ 1.0 1 51

Limit (cost=4.02..9.85 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=51)

  • Buffers: shared hit=102
29. 0.102 0.153 ↑ 1.0 1 51

Bitmap Heap Scan on financial_transaction trn (cost=4.02..9.85 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=51)

  • Filter: (trn.sum < '0'::numeric)
  • Heap Blocks: exact=51
  • Buffers: shared hit=102
30. 0.051 0.051 ↑ 1.0 2 51

Bitmap Index Scan on idx_financial_transaction_fiop_hash (cost=0..4.01 rows=2 width=0) (actual time=0.001..0.001 rows=2 loops=51)

  • Index Cond: (fop.id = trn.financial_operation_id)
  • Buffers: shared hit=51
31. 0.051 0.204 ↑ 1.0 1 51

Limit (cost=4.02..9.85 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=51)

  • Buffers: shared hit=103
32. 0.102 0.153 ↑ 1.0 1 51

Bitmap Heap Scan on financial_transaction trn_1 (cost=4.02..9.85 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=51)

  • Filter: (trn_1.sum >= '0'::numeric)
  • Heap Blocks: exact=52
  • Buffers: shared hit=103
33. 0.051 0.051 ↑ 1.0 2 51

Bitmap Index Scan on idx_financial_transaction_fiop_hash (cost=0..4.01 rows=2 width=0) (actual time=0.001..0.001 rows=2 loops=51)

  • Index Cond: (fop.id = trn_1.financial_operation_id)
  • Buffers: shared hit=51
34. 0.102 0.357 ↑ 1.0 1 51

Limit (cost=0..1.55 rows=1 width=108) (actual time=0.006..0.007 rows=1 loops=51)

  • Buffers: shared hit=51
35. 0.255 0.255 ↑ 1.0 1 51

Seq Scan on provider p (cost=0..1.55 rows=1 width=108) (actual time=0.005..0.005 rows=1 loops=51)

  • Filter: (p.id = CASE WHEN (fop.type = 'withdraw'::financial_operation_type) THEN to_trn.provider_id ELSE from_trn.provider_id END)
  • Buffers: shared hit=51
Planning time : 7.114 ms
Execution time : 17.605 ms