explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PgeY

Settings
# exclusive inclusive rows x rows loops node
1. 0.056 525,041.417 ↓ 76.0 76 1

Limit (cost=540,224.02..540,224.04 rows=1 width=41) (actual time=486,304.613..525,041.417 rows=76 loops=1)

  • Buffers: shared hit=30,969,136 read=29,448, temp read=4,779 written=4,765
2. 7,109.774 525,041.361 ↓ 76.0 76 1

Unique (cost=540,224.02..540,224.04 rows=1 width=41) (actual time=486,304.612..525,041.361 rows=76 loops=1)

  • Buffers: shared hit=30,969,136 read=29,448, temp read=4,779 written=4,765
3. 120,355.799 517,931.587 ↓ 366,265.0 366,265 1

Sort (cost=540,224.02..540,224.03 rows=1 width=41) (actual time=486,304.610..517,931.587 rows=366,265 loops=1)

  • Sort Key: eq.broker, oms.broker_no, lb.oms_name
  • Sort Method: external merge Disk: 20,160kB
  • Buffers: shared hit=29,060,325 read=29,448, temp read=4,779 written=4,765
4. 103,499.034 397,575.788 ↓ 366,265.0 366,265 1

Nested Loop Left Join (cost=338,859.69..540,224.01 rows=1 width=41) (actual time=6,463.018..397,575.788 rows=366,265 loops=1)

  • Join Filter: (lb.oms_no = b.lead_oms_no)
  • Rows Removed by Join Filter: 252,722,850
  • Buffers: shared hit=10,522,463 read=17,701, temp read=2,246 written=2,232
5. 104,991.904 214,597.249 ↓ 366,265.0 366,265 1

Nested Loop Left Join (cost=338,859.69..540,196.47 rows=1 width=25) (actual time=6,441.371..214,597.249 rows=366,265 loops=1)

  • Join Filter: (b.oms_no = oms.broker_no)
  • Rows Removed by Join Filter: 252,722,850
  • Buffers: shared hit=6,127,294 read=17,690, temp read=2,246 written=2,232
6. 1,410.675 30,125.840 ↓ 366,265.0 366,265 1

Nested Loop (cost=338,859.69..540,168.92 rows=1 width=21) (actual time=6,441.348..30,125.840 rows=366,265 loops=1)

  • Buffers: shared hit=1,732,114 read=17,690, temp read=2,246 written=2,232
7. 796.775 7,211.657 ↓ 413,529.0 413,529 1

Hash Right Join (cost=338,859.26..540,164.15 rows=1 width=27) (actual time=6,441.298..7,211.657 rows=413,529 loops=1)

  • Hash Cond: (oms_trade_notification_esb.deal_no = oms.deal_no)
  • Filter: (oms_trade_notification_esb.id IS NULL)
  • Rows Removed by Filter: 1,956
  • Buffers: shared hit=85,749 read=15,409, temp read=2,246 written=2,232
8. 4,271.837 4,271.837 ↑ 1.0 33,277 1

Index Scan using idx_oms_trade_notification_esb_trade_date on oms_trade_notification_esb (cost=0.43..169,455.30 rows=33,341 width=8) (actual time=16.660..4,271.837 rows=33,277 loops=1)

  • Index Cond: (deal_status = 7)
  • Filter: (deal_no IS NOT NULL)
  • Buffers: shared hit=24,246 read=13,106
9. 291.652 2,143.045 ↓ 1.0 415,485 1

Hash (cost=330,855.59..330,855.59 rows=413,859 width=27) (actual time=2,143.045..2,143.045 rows=415,485 loops=1)

  • Buckets: 65,536 Batches: 8 Memory Usage: 3,567kB
  • Buffers: shared hit=61,503 read=2,303, temp written=2,128
10. 1,366.540 1,851.393 ↓ 1.0 415,485 1

Bitmap Heap Scan on oms_trade_notification_esb oms (cost=13,972.90..330,855.59 rows=413,859 width=27) (actual time=497.032..1,851.393 rows=415,485 loops=1)

  • Recheck Cond: (trade_date > '2019-01-01'::date)
  • Rows Removed by Index Recheck: 12,437
  • Filter: ((deal_status <> 7) AND (instrument_type = ANY ('{10,2509}'::integer[])))
  • Rows Removed by Filter: 287,863
  • Heap Blocks: exact=32,571 lossy=26,588
  • Buffers: shared hit=61,503 read=2,303
11. 484.853 484.853 ↓ 1.1 745,942 1

Bitmap Index Scan on oms_trade_notification_esb_currency (cost=0.00..13,869.44 rows=704,668 width=0) (actual time=484.853..484.853 rows=745,942 loops=1)

  • Index Cond: (trade_date > '2019-01-01'::date)
  • Buffers: shared hit=2,344 read=2,303
12. 21,503.508 21,503.508 ↑ 1.0 1 413,529

Index Scan using idx_equity_tca_virtu_omskey on equity_tca_virtu eq (cost=0.43..4.76 rows=1 width=41) (actual time=0.050..0.052 rows=1 loops=413,529)

  • Index Cond: ((order_number = oms.order_no) AND (order_id = oms.allocation_match_id) AND (deal_no = oms.deal_no) AND (account = oms.fund_code) AND ((symbol)::text = (oms.sedol)::text))
  • Filter: (trade_date > '2019-01-01'::date)
  • Buffers: shared hit=1,646,365 read=2,281
13. 79,479.505 79,479.505 ↑ 1.0 691 366,265

Seq Scan on broker b (cost=0.00..18.91 rows=691 width=8) (actual time=0.004..0.217 rows=691 loops=366,265)

  • Buffers: shared hit=4,395,180
14. 79,479.505 79,479.505 ↑ 1.0 691 366,265

Seq Scan on broker lb (cost=0.00..18.91 rows=691 width=24) (actual time=0.003..0.217 rows=691 loops=366,265)

  • Buffers: shared hit=4,395,169 read=11
Planning time : 15.405 ms
Execution time : 525,049.268 ms