explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rApL

Settings
# exclusive inclusive rows x rows loops node
1. 245,313.174 245,313.174 ↑ 1.0 10 1

CTE Scan on accountwithlines (cost=269,362.94..269,363.14 rows=10 width=399) (actual time=245,313.169..245,313.174 rows=10 loops=1)

  • Buffers: shared hit=3746924 read=136383
2.          

CTE accountwithlines

3. 0.001 245,313.170 ↑ 1.0 10 1

Limit (cost=269,362.91..269,362.94 rows=10 width=273) (actual time=245,313.166..245,313.170 rows=10 loops=1)

  • Buffers: shared hit=3746924 read=136383
4. 458.073 245,313.169 ↑ 5.4 10 1

Sort (cost=269,362.91..269,363.05 rows=54 width=273) (actual time=245,313.166..245,313.169 rows=10 loops=1)

  • Sort Key: t.sequence DESC
  • Sort Method: top-N heapsort Memory: 27kB
  • Buffers: shared hit=3746924 read=136383
5. 1,450.611 244,855.096 ↓ 12,605.1 680,676 1

Nested Loop (cost=3,425.03..269,361.74 rows=54 width=273) (actual time=770.602..244,855.096 rows=680,676 loops=1)

  • Buffers: shared hit=3746924 read=136383
6. 226.174 192,353.785 ↓ 12,605.1 680,676 1

Nested Loop (cost=3,424.46..269,244.67 rows=54 width=61) (actual time=770.537..192,353.785 rows=680,676 loops=1)

  • Buffers: shared hit=356160 read=103596
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.017..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=7
8. 191,484.652 192,127.579 ↓ 9.2 680,676 1

Bitmap Heap Scan on lines l (cost=3,424.04..268,495.05 rows=73,674 width=98) (actual time=770.516..192,127.579 rows=680,676 loops=1)

  • Recheck Cond: ((account = a.id) AND (created >= '2019-03-31 17:00:00-07'::timestamp with time zone) AND (created <= '2019-08-09 16:59:59.999-07'::timestamp with time zone))
  • Heap Blocks: exact=448532
  • Buffers: shared hit=356153 read=103596
9. 642.927 642.927 ↓ 9.2 680,676 1

Bitmap Index Scan on lines_account_created (cost=0.00..3,405.62 rows=73,674 width=0) (actual time=642.927..642.927 rows=680,676 loops=1)

  • Index Cond: ((account = a.id) AND (created >= '2019-03-31 17:00:00-07'::timestamp with time zone) AND (created <= '2019-08-09 16:59:59.999-07'::timestamp with time zone))
  • Buffers: shared hit=9862 read=1355
10. 51,050.700 51,050.700 ↑ 1.0 1 680,676

Index Scan using transactions_id_idx on transactions t (cost=0.57..2.04 rows=1 width=145) (actual time=0.075..0.075 rows=1 loops=680,676)

  • Index Cond: (id = l.transaction)
  • Buffers: shared hit=3390764 read=32787
Planning time : 0.881 ms
Execution time : 245,315.448 ms