explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kXxg

Settings
# exclusive inclusive rows x rows loops node
1. 0.068 708,789.665 ↓ 76.0 76 1

Limit (cost=540,494.97..540,494.98 rows=1 width=41) (actual time=675,352.879..708,789.665 rows=76 loops=1)

  • Buffers: shared hit=26,405,197 read=209,595, temp read=978,907 written=6,713
2. 3,741.591 708,789.597 ↓ 76.0 76 1

Unique (cost=540,494.97..540,494.98 rows=1 width=41) (actual time=675,352.877..708,789.597 rows=76 loops=1)

  • Buffers: shared hit=26,405,197 read=209,595, temp read=978,907 written=6,713
3. 93,865.990 705,048.006 ↓ 366,265.0 366,265 1

Sort (cost=540,494.97..540,494.98 rows=1 width=41) (actual time=675,352.869..705,048.006 rows=366,265 loops=1)

  • Sort Key: eq.broker, oms.broker_no, lb.oms_name
  • Sort Method: external merge Disk: 20,200kB
  • Buffers: shared hit=24,386,561 read=209,595, temp read=978,907 written=6,713
4. 64,567.555 611,182.016 ↓ 366,265.0 366,265 1

Nested Loop (cost=338,859.97..540,494.96 rows=1 width=41) (actual time=166,272.055..611,182.016 rows=366,265 loops=1)

  • Join Filter: (b.lead_oms_no = lb.oms_no)
  • Rows Removed by Join Filter: 252,722,850
  • Buffers: shared hit=6,022,757 read=196,059, temp read=975,836 written=3,642
5. 55,502.544 499,000.011 ↓ 366,265.0 366,265 1

Nested Loop (cost=338,859.97..540,467.41 rows=1 width=25) (actual time=166,272.031..499,000.011 rows=366,265 loops=1)

  • Join Filter: (oms.broker_no = b.oms_no)
  • Rows Removed by Join Filter: 252,722,850
  • Buffers: shared hit=1,627,588 read=196,048, temp read=975,836 written=3,642
6. 8.830 8.830 ↑ 1.0 691 1

Index Scan using idx_broker_lead on broker b (cost=0.28..288.13 rows=691 width=8) (actual time=0.011..8.830 rows=691 loops=1)

  • Buffers: shared hit=95 read=2
7. 57,738.632 443,488.637 ↓ 366,265.0 366,265 691

Materialize (cost=338,859.69..540,168.92 rows=1 width=21) (actual time=238.610..641.807 rows=366,265 loops=691)

  • Buffers: shared hit=1,627,493 read=196,046, temp read=975,836 written=3,642
8. 544.587 385,750.005 ↓ 366,265.0 366,265 1

Nested Loop (cost=338,859.69..540,168.92 rows=1 width=21) (actual time=164,876.270..385,750.005 rows=366,265 loops=1)

  • Buffers: shared hit=1,627,493 read=196,046, temp read=2,246 written=2,232
9. 367.455 165,207.990 ↓ 413,529.0 413,529 1

Hash Right Join (cost=338,859.26..540,164.15 rows=1 width=27) (actual time=164,866.622..165,207.990 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=18,513 read=82,647, temp read=2,246 written=2,232
10. 22,007.331 22,007.331 ↑ 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=3.811..22,007.331 rows=33,277 loops=1)

  • Index Cond: (deal_status = 7)
  • Filter: (deal_no IS NOT NULL)
  • Buffers: shared hit=16,208 read=21,146
11. 303.552 142,833.204 ↓ 1.0 415,485 1

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

  • Buckets: 65,536 Batches: 8 Memory Usage: 3,567kB
  • Buffers: shared hit=2,305 read=61,501, temp written=2,128
12. 141,907.497 142,529.652 ↓ 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=635.936..142,529.652 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=2,305 read=61,501
13. 622.155 622.155 ↓ 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=622.155..622.155 rows=745,942 loops=1)

  • Index Cond: (trade_date > '2019-01-01'::date)
  • Buffers: shared hit=2,299 read=2,348
14. 219,997.428 219,997.428 ↑ 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.516..0.532 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,608,980 read=113,399
15. 47,614.450 47,614.450 ↑ 1.0 691 366,265

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

  • Buffers: shared hit=4,395,169 read=11
Planning time : 43.701 ms
Execution time : 708,797.256 ms