explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Pms3

Settings
# exclusive inclusive rows x rows loops node
1. 26,846.673 26,846.673 ↑ 1.0 10 1

CTE Scan on accountwithlines (cost=446,783.14..446,783.34 rows=10 width=399) (actual time=26,846.666..26,846.673 rows=10 loops=1)

  • Buffers: shared hit=8879432
2.          

CTE accountwithlines

3. 0.002 26,846.663 ↑ 1.0 10 1

Limit (cost=446,783.12..446,783.14 rows=10 width=273) (actual time=26,846.663..26,846.663 rows=10 loops=1)

  • Buffers: shared hit=8879432
4. 816.411 26,846.661 ↑ 9.3 10 1

Sort (cost=446,783.12..446,783.35 rows=93 width=273) (actual time=26,846.661..26,846.661 rows=10 loops=1)

  • Sort Key: t.sequence DESC
  • Sort Method: top-N heapsort Memory: 27kB
  • Buffers: shared hit=8879432
5. 1,642.073 26,030.250 ↓ 17,192.7 1,598,924 1

Nested Loop (cost=5,913.98..446,781.11 rows=93 width=273) (actual time=358.497..26,030.250 rows=1,598,924 loops=1)

  • Buffers: shared hit=8879432
6. 211.661 6,800.013 ↓ 17,192.7 1,598,924 1

Nested Loop (cost=5,913.42..446,584.74 rows=93 width=61) (actual time=358.445..6,800.013 rows=1,598,924 loops=1)

  • Buffers: shared hit=835274
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=7
8. 6,238.566 6,588.322 ↓ 12.6 1,598,924 1

Bitmap Heap Scan on lines l (cost=5,912.99..445,301.04 rows=127,082 width=98) (actual time=358.426..6,588.322 rows=1,598,924 loops=1)

  • Recheck Cond: ((account = a.id) AND (created >= '2018-12-31 16:00:00-08'::timestamp with time zone) AND (created <= '2019-08-09 16:59:59.999-07'::timestamp with time zone))
  • Rows Removed by Index Recheck: 33257618
  • Heap Blocks: exact=41296 lossy=767804
  • Buffers: shared hit=835267
9. 349.756 349.756 ↓ 12.6 1,598,924 1

Bitmap Index Scan on lines_account_created (cost=0.00..5,881.22 rows=127,082 width=0) (actual time=349.756..349.756 rows=1,598,924 loops=1)

  • Index Cond: ((account = a.id) AND (created >= '2018-12-31 16:00:00-08'::timestamp with time zone) AND (created <= '2019-08-09 16:59:59.999-07'::timestamp with time zone))
  • Buffers: shared hit=26167
10. 17,588.164 17,588.164 ↑ 1.0 1 1,598,924

Index Scan using transactions_id_idx on transactions t (cost=0.57..1.98 rows=1 width=145) (actual time=0.011..0.011 rows=1 loops=1,598,924)

  • Index Cond: (id = l.transaction)
  • Buffers: shared hit=8044158
Planning time : 0.964 ms
Execution time : 26,847.005 ms