explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MMhX

Settings
# exclusive inclusive rows x rows loops node
1. 85,627.265 85,627.265 ↑ 1.0 10 1

CTE Scan on accountwithlines (cost=209,176.70..209,176.90 rows=10 width=399) (actual time=85,627.251..85,627.265 rows=10 loops=1)

  • Buffers: shared hit=2082556 read=71328
2.          

CTE accountwithlines

3. 0.002 85,627.252 ↑ 1.0 10 1

Limit (cost=209,176.67..209,176.70 rows=10 width=273) (actual time=85,627.248..85,627.252 rows=10 loops=1)

  • Buffers: shared hit=2082556 read=71328
4. 246.569 85,627.250 ↑ 4.1 10 1

Sort (cost=209,176.67..209,176.77 rows=41 width=273) (actual time=85,627.248..85,627.250 rows=10 loops=1)

  • Sort Key: t.sequence DESC
  • Sort Method: top-N heapsort Memory: 27kB
  • Buffers: shared hit=2082556 read=71328
5. 516.387 85,380.681 ↓ 9,020.4 369,836 1

Nested Loop (cost=2,624.25..209,175.78 rows=41 width=273) (actual time=223.084..85,380.681 rows=369,836 loops=1)

  • Buffers: shared hit=2082556 read=71328
6. 115.887 60,824.954 ↓ 9,020.4 369,836 1

Nested Loop (cost=2,623.68..209,085.03 rows=41 width=61) (actual time=221.417..60,824.954 rows=369,836 loops=1)

  • Buffers: shared hit=238498 read=53755
7. 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.018..0.032 rows=1 loops=1)

  • Index Cond: ((user_id = '5ac4a041eb1610a6212a75bb'::text) AND ((type)::text = ANY ('{CASH,XENDIT_DISBURSEMENT_FEE_REFUND}'::text[])) AND (currency = 'IDR'::text))
  • Buffers: shared hit=7
8. 60,562.251 60,709.035 ↓ 6.6 369,836 1

Bitmap Heap Scan on lines l (cost=2,623.26..208,508.57 rows=56,358 width=98) (actual time=221.395..60,709.035 rows=369,836 loops=1)

  • Recheck Cond: ((account = a.id) AND (created >= '2019-02-28 16:00:00-08'::timestamp with time zone) AND (created <= '2019-06-09 16:59:59.999-07'::timestamp with time zone))
  • Heap Blocks: exact=286137
  • Buffers: shared hit=238491 read=53755
9. 146.784 146.784 ↓ 6.6 369,836 1

Bitmap Index Scan on lines_account_created (cost=0.00..2,609.17 rows=56,358 width=0) (actual time=146.784..146.784 rows=369,836 loops=1)

  • Index Cond: ((account = a.id) AND (created >= '2019-02-28 16:00:00-08'::timestamp with time zone) AND (created <= '2019-06-09 16:59:59.999-07'::timestamp with time zone))
  • Buffers: shared hit=5085 read=1024
10. 24,039.340 24,039.340 ↑ 1.0 1 369,836

Index Scan using transactions_id_idx on transactions t (cost=0.57..2.08 rows=1 width=145) (actual time=0.064..0.065 rows=1 loops=369,836)

  • Index Cond: (id = l.transaction)
  • Buffers: shared hit=1844058 read=17573
Planning time : 0.948 ms
Execution time : 85,628.731 ms