explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3UrX

Settings
# exclusive inclusive rows x rows loops node
1. 148.847 148.847 ↑ 1.0 10 1

CTE Scan on transactionrows (cost=68,828.56..68,828.76 rows=10 width=849) (actual time=147.354..148.847 rows=10 loops=1)

  • Buffers: shared hit=15499
2.          

CTE accountwithlines

3. 0.005 147.691 ↑ 1.0 10 1

Limit (cost=2,105.78..49,825.46 rows=10 width=265) (actual time=147.068..147.691 rows=10 loops=1)

  • Buffers: shared hit=15159
4. 0.055 147.686 ↑ 2.9 10 1

Nested Loop (cost=2,105.78..140,492.84 rows=29 width=265) (actual time=147.067..147.686 rows=10 loops=1)

  • Buffers: shared hit=15159
5. 0.005 147.491 ↑ 2.9 10 1

Nested Loop (cost=2,105.21..140,420.93 rows=29 width=61) (actual time=147.015..147.491 rows=10 loops=1)

  • Buffers: shared hit=15109
6. 0.032 0.032 ↑ 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.032..0.032 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=4
7. 8.894 147.454 ↑ 3,702.7 10 1

Bitmap Heap Scan on lines l (cost=2,104.79..140,037.77 rows=37,027 width=98) (actual time=146.979..147.454 rows=10 loops=1)

  • Recheck Cond: ((account = a.id) AND (created >= '2019-04-30 17:00:00-07'::timestamp with time zone) AND (created <= '2019-07-09 16:59:59.999-07'::timestamp with time zone))
  • Rows Removed by Index Recheck: 578
  • Heap Blocks: exact=4 lossy=19
  • Buffers: shared hit=15105
8. 138.560 138.560 ↓ 12.5 463,757 1

Bitmap Index Scan on lines_account_created (cost=0.00..2,095.53 rows=37,027 width=0) (actual time=138.560..138.560 rows=463,757 loops=1)

  • Index Cond: ((account = a.id) AND (created >= '2019-04-30 17:00:00-07'::timestamp with time zone) AND (created <= '2019-07-09 16:59:59.999-07'::timestamp with time zone))
  • Buffers: shared hit=15082
9. 0.140 0.140 ↑ 1.0 1 10

Index Scan using transactions_id_idx on transactions t (cost=0.57..2.35 rows=1 width=145) (actual time=0.014..0.014 rows=1 loops=10)

  • Index Cond: (id = l.transaction)
  • Buffers: shared hit=50
10.          

CTE accountdetails

11. 147.758 148.508 ↑ 1.0 10 1

CTE Scan on accountwithlines al (cost=0.00..18,997.50 rows=10 width=479) (actual time=147.238..148.508 rows=10 loops=1)

  • Buffers: shared hit=15499
12.          

SubPlan (forCTE Scan)

13. 0.100 0.310 ↑ 57.0 1 10

Nested Loop (cost=1.12..715.76 rows=57 width=32) (actual time=0.030..0.031 rows=1 loops=10)

  • Buffers: shared hit=110
14. 0.140 0.140 ↑ 57.0 1 10

Index Scan using lines_transaction_type_idx on lines creditl (cost=0.70..233.82 rows=57 width=37) (actual time=0.014..0.014 rows=1 loops=10)

  • Index Cond: ((transaction = al.transaction_id) AND ((type)::text = 'CREDIT'::text))
  • Buffers: shared hit=60
15. 0.070 0.070 ↑ 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.007..0.007 rows=1 loops=10)

  • Index Cond: (id = creditl.account)
  • Buffers: shared hit=50
16. 0.110 0.110 ↑ 57.0 1 10

Index Scan using lines_transaction_type_idx on lines creditl_1 (cost=0.70..234.11 rows=57 width=8) (actual time=0.011..0.011 rows=1 loops=10)

  • Index Cond: ((transaction = al.transaction_id) AND ((type)::text = 'CREDIT'::text))
  • Buffers: shared hit=60
17. 0.100 0.100 ↑ 57.0 1 10

Index Scan using lines_transaction_type_idx on lines debitl (cost=0.70..234.11 rows=57 width=8) (actual time=0.010..0.010 rows=1 loops=10)

  • Index Cond: ((transaction = al.transaction_id) AND ((type)::text = 'DEBIT'::text))
  • Buffers: shared hit=60
18. 0.070 0.230 ↑ 57.0 1 10

Nested Loop (cost=1.12..715.76 rows=57 width=32) (actual time=0.022..0.023 rows=1 loops=10)

  • Buffers: shared hit=110
19. 0.090 0.090 ↑ 57.0 1 10

Index Scan using lines_transaction_type_idx on lines debitl_1 (cost=0.70..233.82 rows=57 width=37) (actual time=0.009..0.009 rows=1 loops=10)

  • Index Cond: ((transaction = al.transaction_id) AND ((type)::text = 'DEBIT'::text))
  • Buffers: shared hit=60
20. 0.070 0.070 ↑ 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.007..0.007 rows=1 loops=10)

  • Index Cond: (id = debitl_1.account)
  • Buffers: shared hit=50
21.          

CTE transactionrows

22. 148.822 148.822 ↑ 1.0 10 1

CTE Scan on accountdetails t_1 (cost=0.00..5.60 rows=10 width=849) (actual time=147.349..148.822 rows=10 loops=1)

  • Buffers: shared hit=15499
Planning time : 1.964 ms
Execution time : 149.537 ms