explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gwqI

Settings
# exclusive inclusive rows x rows loops node
1. 73.766 1,206.068 ↑ 1,538.0 62 1

Unique (cost=379,997.20..382,973.03 rows=95,358 width=24) (actual time=1,001.165..1,206.068 rows=62 loops=1)

  • Buffers: shared hit=60,671, temp read=1,896 written=1,896
2. 325.371 1,132.302 ↓ 1.0 415,485 1

Sort (cost=379,997.20..380,989.14 rows=396,778 width=24) (actual time=1,001.164..1,132.302 rows=415,485 loops=1)

  • Sort Key: oms.broker_no, lb.oms_name
  • Sort Method: external merge Disk: 15,088kB
  • Buffers: shared hit=60,671, temp read=1,896 written=1,896
3. 130.755 806.931 ↓ 1.0 415,485 1

Hash Join (cost=13,263.56..334,963.37 rows=396,778 width=24) (actual time=68.546..806.931 rows=415,485 loops=1)

  • Hash Cond: (oms.broker_no = b.oms_no)
  • Buffers: shared hit=60,671
4. 613.256 675.429 ↓ 1.0 415,485 1

Bitmap Heap Scan on oms_trade_notification_esb oms (cost=13,198.96..329,443.07 rows=396,778 width=4) (actual time=67.789..675.429 rows=415,485 loops=1)

  • Recheck Cond: (trade_date > '2019-01-01'::date)
  • Rows Removed by Index Recheck: 13,212
  • Filter: ((deal_status <> 7) AND (instrument_type = ANY ('{10,2509}'::integer[])))
  • Rows Removed by Filter: 287,863
  • Heap Blocks: exact=29,442 lossy=26,612
  • Buffers: shared hit=60,647
5. 62.173 62.173 ↓ 1.1 703,348 1

Bitmap Index Scan on oms_trade_notification_esb_currency (cost=0.00..13,099.76 rows=668,178 width=0) (actual time=62.173..62.173 rows=703,348 loops=1)

  • Index Cond: (trade_date > '2019-01-01'::date)
  • Buffers: shared hit=4,593
6. 0.179 0.747 ↑ 1.0 691 1

Hash (cost=55.96..55.96 rows=691 width=24) (actual time=0.747..0.747 rows=691 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 47kB
  • Buffers: shared hit=24
7. 0.235 0.568 ↑ 1.0 691 1

Hash Join (cost=27.55..55.96 rows=691 width=24) (actual time=0.282..0.568 rows=691 loops=1)

  • Hash Cond: (b.lead_oms_no = lb.oms_no)
  • Buffers: shared hit=24
8. 0.060 0.060 ↑ 1.0 691 1

Seq Scan on broker b (cost=0.00..18.91 rows=691 width=8) (actual time=0.004..0.060 rows=691 loops=1)

  • Buffers: shared hit=12
9. 0.144 0.273 ↑ 1.0 691 1

Hash (cost=18.91..18.91 rows=691 width=24) (actual time=0.273..0.273 rows=691 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 48kB
  • Buffers: shared hit=12
10. 0.129 0.129 ↑ 1.0 691 1

Seq Scan on broker lb (cost=0.00..18.91 rows=691 width=24) (actual time=0.002..0.129 rows=691 loops=1)

  • Buffers: shared hit=12
Planning time : 0.372 ms
Execution time : 1,209.612 ms