explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8ai5

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 12,629.269 ↓ 20.0 20 1

Limit (cost=1,186.57..1,186.58 rows=1 width=287) (actual time=12,629.261..12,629.269 rows=20 loops=1)

  • Buffers: shared hit=237,943 read=206
2. 1.728 12,629.261 ↓ 20.0 20 1

Sort (cost=1,186.57..1,186.58 rows=1 width=287) (actual time=12,629.259..12,629.261 rows=20 loops=1)

  • Sort Key: t.sequence DESC
  • Sort Method: top-N heapsort Memory: 30kB
  • Buffers: shared hit=237,943 read=206
3. 5.731 12,627.533 ↓ 980.0 980 1

Nested Loop (cost=3.72..1,186.56 rows=1 width=287) (actual time=3.977..12,627.533 rows=980 loops=1)

  • Buffers: shared hit=237,940 read=206
4. 1.743 5,724.562 ↓ 980.0 980 1

Nested Loop (cost=3.28..1,181.09 rows=1 width=267) (actual time=2.892..5,724.562 rows=980 loops=1)

  • Join Filter: (l.transaction = debit_l.transaction)
  • Buffers: shared hit=105,692 read=201
5. 1.487 5,718.899 ↓ 980.0 980 1

Nested Loop (cost=2.71..772.68 rows=1 width=283) (actual time=2.884..5,718.899 rows=980 loops=1)

  • Buffers: shared hit=100,775 read=200
6. 0.979 282.332 ↓ 980.0 980 1

Nested Loop (cost=2.28..766.79 rows=1 width=254) (actual time=2.825..282.332 rows=980 loops=1)

  • Join Filter: (l.transaction = credit_l.transaction)
  • Buffers: shared hit=10,439 read=200
7. 1.374 96.133 ↓ 980.0 980 1

Nested Loop (cost=1.70..358.37 rows=1 width=204) (actual time=2.807..96.133 rows=980 loops=1)

  • Buffers: shared hit=5,653 read=68
8. 0.446 16.359 ↓ 980.0 980 1

Nested Loop (cost=1.13..349.85 rows=1 width=48) (actual time=2.792..16.359 rows=980 loops=1)

  • Buffers: shared hit=803 read=11
9. 8.235 8.235 ↑ 5.0 1 1

Index Scan using accounts_user_id_type_currency on accounts a (cost=0.56..24.60 rows=5 width=16) (actual time=0.099..8.235 rows=1 loops=1)

  • Index Cond: ((user_id = '5ece0edaf9826e1314c8d321'::bpchar) AND ((type)::text = 'CASH'::text) AND (currency = 'IDR'::bpchar))
  • Buffers: shared hit=210
10. 7.678 7.678 ↓ 65.3 980 1

Index Scan using lines_account_created on lines l (cost=0.57..64.90 rows=15 width=64) (actual time=2.691..7.678 rows=980 loops=1)

  • Index Cond: ((account = a.id) AND (created >= '2020-07-01 00:00:00+07'::timestamp with time zone) AND (created <= '2020-07-01 01:35:51+07'::timestamp with time zone))
  • Buffers: shared hit=593 read=11
11. 78.400 78.400 ↑ 1.0 1 980

Index Scan using transactions_pkey on transactions t (cost=0.57..8.51 rows=1 width=156) (actual time=0.080..0.080 rows=1 loops=980)

  • Index Cond: (id = l.transaction)
  • Buffers: shared hit=4,850 read=57
12. 185.220 185.220 ↑ 83.0 1 980

Index Scan using lines_transaction on lines credit_l (cost=0.57..407.39 rows=83 width=50) (actual time=0.187..0.189 rows=1 loops=980)

  • Index Cond: (transaction = t.id)
  • Filter: ((type)::text = 'CREDIT'::text)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=4,786 read=132
13. 5,435.080 5,435.080 ↑ 1.0 1 980

Index Scan using accounts_pkey on accounts credit_a (cost=0.43..5.88 rows=1 width=61) (actual time=0.012..5.546 rows=1 loops=980)

  • Index Cond: (id = credit_l.account)
  • Buffers: shared hit=90,336
14. 3.920 3.920 ↑ 82.0 1 980

Index Scan using lines_transaction on lines debit_l (cost=0.57..407.39 rows=82 width=32) (actual time=0.003..0.004 rows=1 loops=980)

  • Index Cond: (transaction = credit_l.transaction)
  • Filter: ((type)::text = 'DEBIT'::text)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=4,917 read=1
15. 6,897.240 6,897.240 ↑ 1.0 1 980

Index Only Scan using accounts_pkey on accounts debit_a (cost=0.43..5.43 rows=1 width=16) (actual time=0.015..7.038 rows=1 loops=980)

  • Index Cond: (id = debit_l.account)
  • Heap Fetches: 18,884,170
  • Buffers: shared hit=132,248 read=5
Planning time : 5.560 ms
Execution time : 12,629.429 ms