explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BuwNo

Settings
# exclusive inclusive rows x rows loops node
1. 267,407.661 267,407.661 ↑ 1.0 10 1

CTE Scan on transactionrows (cost=292,921.14..292,921.34 rows=10 width=849) (actual time=267,385.427..267,407.661 rows=10 loops=1)

  • Buffers: shared hit=5861021 read=145923
2.          

CTE accountwithlines

3. 0.001 267,380.774 ↑ 1.0 10 1

Limit (cost=272,928.04..272,928.07 rows=10 width=273) (actual time=267,380.767..267,380.774 rows=10 loops=1)

  • Buffers: shared hit=5859270 read=145909
4. 741.102 267,380.773 ↑ 5.5 10 1

Sort (cost=272,928.04..272,928.18 rows=55 width=273) (actual time=267,380.767..267,380.773 rows=10 loops=1)

  • Sort Key: t.sequence DESC
  • Sort Method: top-N heapsort Memory: 27kB
  • Buffers: shared hit=5859270 read=145909
5. 1,881.369 266,639.671 ↓ 19,912.1 1,095,168 1

Nested Loop (cost=3,470.28..272,926.85 rows=55 width=273) (actual time=394.911..266,639.671 rows=1,095,168 loops=1)

  • Buffers: shared hit=5859270 read=145909
6. 315.796 188,096.542 ↓ 19,912.1 1,095,168 1

Nested Loop (cost=3,469.71..272,807.73 rows=55 width=61) (actual time=394.854..188,096.542 rows=1,095,168 loops=1)

  • Buffers: shared hit=384013 read=111694
7. 0.088 0.088 ↑ 1.0 1 1

Index Scan using accounts_user_id_type_currency on accounts a (cost=0.42..12.88 rows=1 width=37) (actual time=0.023..0.088 rows=1 loops=1)

  • Index Cond: ((user_id = '5a743292ea1830b877710ed2'::text) AND ((type)::text = ANY ('{CASH,XENDIT_DISBURSEMENT_FEE_REFUND}'::text[])) AND (currency = 'IDR'::text))
  • Buffers: shared hit=75
8. 187,528.041 187,780.658 ↓ 14.7 1,095,168 1

Bitmap Heap Scan on lines l (cost=3,469.29..272,047.72 rows=74,713 width=98) (actual time=394.827..187,780.658 rows=1,095,168 loops=1)

  • Recheck Cond: ((account = a.id) AND (created >= '2018-12-31 16:00:00-08'::timestamp with time zone) AND (created <= '2019-05-09 16:59:59.999-07'::timestamp with time zone))
  • Heap Blocks: exact=477691
  • Buffers: shared hit=383938 read=111694
9. 252.617 252.617 ↓ 14.7 1,095,168 1

Bitmap Index Scan on lines_account_created (cost=0.00..3,450.61 rows=74,713 width=0) (actual time=252.617..252.617 rows=1,095,168 loops=1)

  • Index Cond: ((account = a.id) AND (created >= '2018-12-31 16:00:00-08'::timestamp with time zone) AND (created <= '2019-05-09 16:59:59.999-07'::timestamp with time zone))
  • Buffers: shared hit=17941
10. 76,661.760 76,661.760 ↑ 1.0 1 1,095,168

Index Scan using transactions_id_idx on transactions t (cost=0.57..2.04 rows=1 width=145) (actual time=0.069..0.070 rows=1 loops=1,095,168)

  • Index Cond: (id = l.transaction)
  • Buffers: shared hit=5475257 read=34215
11.          

CTE accountdetails

12. 267,380.846 267,407.236 ↑ 1.0 10 1

CTE Scan on accountwithlines al (cost=0.00..19,987.48 rows=10 width=479) (actual time=267,385.298..267,407.236 rows=10 loops=1)

  • Buffers: shared hit=5861021 read=145923
13.          

SubPlan (for CTE Scan)

14. 0.160 24.600 ↑ 60.0 1 10

Nested Loop (cost=1.12..753.18 rows=60 width=32) (actual time=2.428..2.460 rows=1 loops=10)

  • Buffers: shared hit=822 read=13
15. 23.980 23.980 ↑ 60.0 1 10

Index Scan using lines_transaction_type_idx on lines creditl (cost=0.70..245.88 rows=60 width=37) (actual time=2.397..2.398 rows=1 loops=10)

  • Index Cond: ((transaction = al.transaction_id) AND ((type)::text = 'CREDIT'::text))
  • Buffers: shared hit=47 read=13
16. 0.460 0.460 ↑ 1.0 1 10

Index Scan using accounts_id_idx on accounts credita (cost=0.42..8.44 rows=1 width=167) (actual time=0.014..0.046 rows=1 loops=10)

  • Index Cond: (id = creditl.account)
  • Buffers: shared hit=775
17. 0.180 0.180 ↑ 60.0 1 10

Index Scan using lines_transaction_type_idx on lines creditl_1 (cost=0.70..246.18 rows=60 width=8) (actual time=0.017..0.018 rows=1 loops=10)

  • Index Cond: ((transaction = al.transaction_id) AND ((type)::text = 'CREDIT'::text))
  • Buffers: shared hit=60
18. 1.010 1.010 ↑ 60.0 1 10

Index Scan using lines_transaction_type_idx on lines debitl (cost=0.70..246.18 rows=60 width=8) (actual time=0.101..0.101 rows=1 loops=10)

  • Index Cond: ((transaction = al.transaction_id) AND ((type)::text = 'DEBIT'::text))
  • Buffers: shared hit=59 read=1
19. 0.110 0.600 ↑ 60.0 1 10

Nested Loop (cost=1.12..753.18 rows=60 width=32) (actual time=0.034..0.060 rows=1 loops=10)

  • Buffers: shared hit=810
20. 0.110 0.110 ↑ 60.0 1 10

Index Scan using lines_transaction_type_idx on lines debitl_1 (cost=0.70..245.88 rows=60 width=37) (actual time=0.011..0.011 rows=1 loops=10)

  • Index Cond: ((transaction = al.transaction_id) AND ((type)::text = 'DEBIT'::text))
  • Buffers: shared hit=60
21. 0.380 0.380 ↑ 1.0 1 10

Index Scan using accounts_id_idx on accounts debita (cost=0.42..8.44 rows=1 width=167) (actual time=0.012..0.038 rows=1 loops=10)

  • Index Cond: (id = debitl_1.account)
  • Buffers: shared hit=750
22.          

CTE transactionrows

23. 267,407.632 267,407.632 ↑ 1.0 10 1

CTE Scan on accountdetails t_1 (cost=0.00..5.60 rows=10 width=849) (actual time=267,385.423..267,407.632 rows=10 loops=1)

  • Buffers: shared hit=5861021 read=145923
Planning time : 2.112 ms
Execution time : 267,410.418 ms