explain.depesz.com

PostgreSQL's explain analyze made readable

Result: t7B7

Settings
# exclusive inclusive rows x rows loops node
1. 308,087.840 308,087.840 ↑ 1.0 10 1

CTE Scan on transactionrows (cost=100,818.47..100,818.67 rows=10 width=849) (actual time=308,071.902..308,087.840 rows=10 loops=1)

  • Buffers: shared hit=1004893 read=283448
2.          

CTE accountwithlines

3. 0.004 308,070.896 ↑ 1.0 10 1

Limit (cost=81,815.34..81,815.36 rows=10 width=273) (actual time=308,070.888..308,070.896 rows=10 loops=1)

  • Buffers: shared hit=1004568 read=283433
4. 260.756 308,070.892 ↑ 1.6 10 1

Sort (cost=81,815.34..81,815.38 rows=16 width=273) (actual time=308,070.888..308,070.892 rows=10 loops=1)

  • Sort Key: t.sequence DESC
  • Sort Method: top-N heapsort Memory: 27kB
  • Buffers: shared hit=1004568 read=283433
5. 904.567 307,810.136 ↓ 13,424.5 214,792 1

Nested Loop (cost=1,203.32..81,815.02 rows=16 width=273) (actual time=94.052..307,810.136 rows=214,792 loops=1)

  • Buffers: shared hit=1004568 read=283433
6. 87.597 167,075.977 ↓ 13,424.5 214,792 1

Nested Loop (cost=1,202.76..81,772.01 rows=16 width=61) (actual time=94.002..167,075.977 rows=214,792 loops=1)

  • Buffers: shared hit=57365 read=151458
7. 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
8. 166,907.727 166,988.350 ↓ 10.2 214,792 1

Bitmap Heap Scan on lines l (cost=1,202.33..81,547.64 rows=21,148 width=98) (actual time=93.984..166,988.350 rows=214,792 loops=1)

  • Recheck Cond: ((account = a.id) AND (created >= '2019-05-31 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: 5090552
  • Heap Blocks: exact=40375 lossy=159719
  • Buffers: shared hit=57357 read=151458
9. 80.623 80.623 ↓ 13.9 293,559 1

Bitmap Index Scan on lines_account_created (cost=0.00..1,197.04 rows=21,148 width=0) (actual time=80.623..80.623 rows=293,559 loops=1)

  • Index Cond: ((account = a.id) AND (created >= '2019-05-31 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=8721
10. 139,829.592 139,829.592 ↑ 1.0 1 214,792

Index Scan using transactions_id_idx on transactions t (cost=0.57..2.56 rows=1 width=145) (actual time=0.643..0.651 rows=1 loops=214,792)

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

CTE accountdetails

12. 308,070.950 308,087.430 ↑ 1.0 10 1

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

  • Buffers: shared hit=1004893 read=283448
13.          

SubPlan (forCTE Scan)

14. 0.140 15.230 ↑ 57.0 1 10

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

  • Buffers: shared hit=96 read=14
15. 14.990 14.990 ↑ 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=1.499..1.499 rows=1 loops=10)

  • Index Cond: ((transaction = al.transaction_id) AND ((type)::text = 'CREDIT'::text))
  • Buffers: shared hit=46 read=14
16. 0.100 0.100 ↑ 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.008..0.010 rows=1 loops=10)

  • Index Cond: (id = creditl.account)
  • Buffers: shared hit=50
17. 0.140 0.140 ↑ 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.014..0.014 rows=1 loops=10)

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

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

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

  • Buffers: shared hit=110
20. 0.100 0.100 ↑ 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.010 rows=1 loops=10)

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

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

CTE transactionrows

23. 308,087.808 308,087.808 ↑ 1.0 10 1

CTE Scan on accountdetails t_1 (cost=0.00..5.60 rows=10 width=849) (actual time=308,071.898..308,087.808 rows=10 loops=1)

  • Buffers: shared hit=1004893 read=283448
Planning time : 1.979 ms
Execution time : 308,088.507 ms