explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 314 : Optimization for: plan #6Ht

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.001 5,538.006 ↓ 2.0 4 1

Unique (cost=14,076.10..14,076.11 rows=2 width=38) (actual time=5,538.005..5,538.006 rows=4 loops=1)

  • Buffers: shared hit=4128 read=2286 dirtied=2
  • I/O Timings: read=5488.558
2. 0.047 5,538.005 ↓ 2.0 4 1

Sort (cost=14,076.10..14,076.11 rows=2 width=38) (actual time=5,538.004..5,538.005 rows=4 loops=1)

  • Sort Key: pt.folio_no
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=4128 read=2286 dirtied=2
  • I/O Timings: read=5488.558
3. 0.004 5,537.958 ↓ 2.0 4 1

Append (cost=147.88..14,076.09 rows=2 width=38) (actual time=1,029.264..5,537.958 rows=4 loops=1)

  • Buffers: shared hit=4125 read=2286 dirtied=2
  • I/O Timings: read=5488.558
4. 8.014 5,518.318 ↓ 4.0 4 1

Hash Join (cost=147.88..10,947.74 rows=1 width=7) (actual time=1,029.263..5,518.318 rows=4 loops=1)

  • Hash Cond: ((pt.trxn_type_code)::text = (tt.trxntypcod)::text)
  • Join Filter: (CASE WHEN (((tt.trxndbcr)::text = 'P'::text) AND ((pt.trxn_subtype_code)::text = 'N'::text) AND ((pt.sch_code)::text = ANY ('{66,349,167,13,157,15,592,573,576,110,232,562,104}'::text[]))) THEN 1 ELSE 0 END = 1)
  • Rows Removed by Join Filter: 6890
  • Buffers: shared hit=3803 read=2285 dirtied=2
  • I/O Timings: read=5482.886
5. 6.449 5,509.505 ↓ 25.3 6,894 1

Nested Loop (cost=60.58..10,850.55 rows=272 width=16) (actual time=23.310..5,509.505 rows=6,894 loops=1)

  • Buffers: shared hit=3776 read=2285 dirtied=2
  • I/O Timings: read=5482.886
6. 0.007 4.388 ↑ 1.0 1 1

Bitmap Heap Scan on r_broker_master (cost=60.01..64.02 rows=1 width=10) (actual time=4.387..4.388 rows=1 loops=1)

  • Recheck Cond: ((amfi_regn_no)::text = 'ARN-95787'::text)
  • Heap Blocks: exact=1
  • Buffers: shared hit=15 read=1
  • I/O Timings: read=3.708
7. 4.381 4.381 ↑ 1.0 1 1

Bitmap Index Scan on i190110193139596564629 (cost=0.00..60.01 rows=1 width=0) (actual time=4.381..4.381 rows=1 loops=1)

  • Index Cond: ((amfi_regn_no)::text = 'ARN-95787'::text)
  • Buffers: shared hit=14 read=1
  • I/O Timings: read=3.708
8. 5,498.668 5,498.668 ↓ 2.6 6,894 1

Index Scan using i190111011400701987172 on r_processed_trxns pt (cost=0.57..10,759.88 rows=2,665 width=25) (actual time=18.912..5,498.668 rows=6,894 loops=1)

  • Index Cond: (((broker_code)::text = (r_broker_master.broker_code)::text) AND (trxn_date >= '2018-07-31 00:00:00'::timestamp without time zone) AND (trxn_date <= '2019-01-31 00:00:00'::timestamp without time zone))
  • Buffers: shared hit=3761 read=2284 dirtied=2
  • I/O Timings: read=5479.178
9. 0.454 0.799 ↑ 1.0 2,680 1

Hash (cost=53.80..53.80 rows=2,680 width=8) (actual time=0.799..0.799 rows=2,680 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 140kB
  • Buffers: shared hit=27
10. 0.345 0.345 ↑ 1.0 2,680 1

Seq Scan on r_transaction_types tt (cost=0.00..53.80 rows=2,680 width=8) (actual time=0.008..0.345 rows=2,680 loops=1)

  • Buffers: shared hit=27
11. 0.004 19.636 ↓ 0.0 0 1

Nested Loop (cost=1,024.14..3,128.33 rows=1 width=7) (actual time=19.636..19.636 rows=0 loops=1)

  • Buffers: shared hit=322 read=1
  • I/O Timings: read=5.672
12. 0.003 0.704 ↑ 1.0 1 1

Bitmap Heap Scan on r_broker_master r_broker_master_1 (cost=60.01..64.02 rows=1 width=10) (actual time=0.703..0.704 rows=1 loops=1)

  • Recheck Cond: ((amfi_regn_no)::text = 'ARN-95787'::text)
  • Heap Blocks: exact=1
  • Buffers: shared hit=16
13. 0.701 0.701 ↑ 1.0 1 1

Bitmap Index Scan on i190110193139596564629 (cost=0.00..60.01 rows=1 width=0) (actual time=0.701..0.701 rows=1 loops=1)

  • Index Cond: ((amfi_regn_no)::text = 'ARN-95787'::text)
  • Buffers: shared hit=15
14. 0.103 18.928 ↓ 0.0 0 1

Bitmap Heap Scan on r_sipstp ss (cost=964.13..3,064.30 rows=1 width=15) (actual time=18.928..18.928 rows=0 loops=1)

  • Recheck Cond: ((brok_code)::text = (r_broker_master_1.broker_code)::text)
  • Filter: ((cease_dt IS NULL) AND (from_date >= '2018-07-31 00:00:00'::timestamp without time zone) AND (from_date <= '2019-01-31 00:00:00'::timestamp without time zone) AND (CASE WHEN (((sch_code)::text = ANY ('{66,349,167,13,157,15,592,573,576,110,232,562,104}'::text[])) AND NULL::boolean) THEN 1 WHEN (((to_sch)::text = ANY ('{66,349,167,13,157,15,592,573,576,110,232,562,104}'::text[])) AND NULL::boolean) THEN 1 ELSE 0 END = 1))
  • Rows Removed by Filter: 75
  • Heap Blocks: exact=67
  • Buffers: shared hit=306 read=1
  • I/O Timings: read=5.672
15. 18.825 18.825 ↑ 7.3 75 1

Bitmap Index Scan on i190111041420670849449 (cost=0.00..964.13 rows=551 width=0) (actual time=18.825..18.825 rows=75 loops=1)

  • Index Cond: ((brok_code)::text = (r_broker_master_1.broker_code)::text)
  • Buffers: shared hit=239 read=1
  • I/O Timings: read=5.672