explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4qEX

Settings
# exclusive inclusive rows x rows loops node
1. 114,012.198 114,012.198 ↓ 13,275.5 384,990 1

CTE Scan on transactionrows (cost=195,601.85..195,602.43 rows=29 width=849) (actual time=141.998..114,012.198 rows=384,990 loops=1)

  • Buffers: shared hit=15327847 read=60278, temp written=57196
2.          

CTE accountwithlines

3. 1,045.138 8,579.816 ↓ 13,275.5 384,990 1

Nested Loop (cost=2,105.78..140,492.84 rows=29 width=265) (actual time=141.736..8,579.816 rows=384,990 loops=1)

  • Buffers: shared hit=2254875
4. 98.473 2,529.808 ↓ 13,275.5 384,990 1

Nested Loop (cost=2,105.21..140,420.93 rows=29 width=61) (actual time=141.693..2,529.808 rows=384,990 loops=1)

  • Buffers: shared hit=319798
5. 0.030 0.030 ↑ 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.015..0.030 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=8
6. 2,297.630 2,431.305 ↓ 10.4 384,990 1

Bitmap Heap Scan on lines l (cost=2,104.79..140,037.77 rows=37,027 width=98) (actual time=141.674..2,431.305 rows=384,990 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: 9612486
  • Heap Blocks: exact=39141 lossy=265567
  • Buffers: shared hit=319790
7. 133.675 133.675 ↓ 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=133.675..133.675 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
8. 5,004.870 5,004.870 ↑ 1.0 1 384,990

Index Scan using transactions_id_idx on transactions t (cost=0.57..2.35 rows=1 width=145) (actual time=0.012..0.013 rows=1 loops=384,990)

  • Index Cond: (id = l.transaction)
  • Buffers: shared hit=1935077
9.          

CTE accountdetails

10. 9,905.257 104,227.807 ↓ 13,275.5 384,990 1

CTE Scan on accountwithlines al (cost=0.00..55,092.76 rows=29 width=479) (actual time=141.891..104,227.807 rows=384,990 loops=1)

  • Buffers: shared hit=15327847 read=60278, temp written=8068
11.          

SubPlan (forCTE Scan)

12. 3,079.920 70,068.180 ↑ 57.0 1 384,990

Nested Loop (cost=1.12..715.76 rows=57 width=32) (actual time=0.178..0.182 rows=1 loops=384,990)

  • Buffers: shared hit=4189675 read=52971
13. 64,678.320 64,678.320 ↑ 57.0 1 384,990

Index Scan using lines_transaction_type_idx on lines creditl (cost=0.70..233.82 rows=57 width=37) (actual time=0.165..0.168 rows=1 loops=384,990)

  • Index Cond: ((transaction = al.transaction_id) AND ((type)::text = 'CREDIT'::text))
  • Buffers: shared hit=2264725 read=52971
14. 2,309.940 2,309.940 ↑ 1.0 1 384,990

Index Scan using accounts_id_idx on accounts credita (cost=0.42..8.44 rows=1 width=167) (actual time=0.006..0.006 rows=1 loops=384,990)

  • Index Cond: (id = creditl.account)
  • Buffers: shared hit=1924950
15. 4,234.890 4,234.890 ↑ 57.0 1 384,990

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=384,990)

  • Index Cond: ((transaction = al.transaction_id) AND ((type)::text = 'CREDIT'::text))
  • Buffers: shared hit=2317696
16. 11,549.700 11,549.700 ↑ 57.0 1 384,990

Index Scan using lines_transaction_type_idx on lines debitl (cost=0.70..234.11 rows=57 width=8) (actual time=0.026..0.030 rows=1 loops=384,990)

  • Index Cond: ((transaction = al.transaction_id) AND ((type)::text = 'DEBIT'::text))
  • Buffers: shared hit=2316672 read=7307
17. 2,694.930 8,469.780 ↑ 57.0 1 384,990

Nested Loop (cost=1.12..715.76 rows=57 width=32) (actual time=0.021..0.022 rows=1 loops=384,990)

  • Buffers: shared hit=4248929
18. 3,464.910 3,464.910 ↑ 57.0 1 384,990

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=384,990)

  • Index Cond: ((transaction = al.transaction_id) AND ((type)::text = 'DEBIT'::text))
  • Buffers: shared hit=2323979
19. 2,309.940 2,309.940 ↑ 1.0 1 384,990

Index Scan using accounts_id_idx on accounts debita (cost=0.42..8.44 rows=1 width=167) (actual time=0.006..0.006 rows=1 loops=384,990)

  • Index Cond: (id = debitl_1.account)
  • Buffers: shared hit=1924950
20.          

CTE transactionrows

21. 113,197.637 113,197.637 ↓ 13,275.5 384,990 1

CTE Scan on accountdetails t_1 (cost=0.00..16.24 rows=29 width=849) (actual time=141.995..113,197.637 rows=384,990 loops=1)

  • Buffers: shared hit=15327847 read=60278, temp written=41312
Planning time : 1.898 ms
Execution time : 114,146.069 ms