explain.depesz.com

PostgreSQL's explain analyze made readable

Result: s8hL

Settings
# exclusive inclusive rows x rows loops node
1. 0.046 425,006.332 ↓ 76.0 76 1

Limit (cost=534,212.88..534,212.89 rows=1 width=41) (actual time=411,349.464..425,006.332 rows=76 loops=1)

  • Buffers: shared hit=31,011,694 read=39,004, temp read=4,767 written=4,753
2. 3,092.792 425,006.286 ↓ 76.0 76 1

Unique (cost=534,212.88..534,212.89 rows=1 width=41) (actual time=411,349.463..425,006.286 rows=76 loops=1)

  • Buffers: shared hit=31,011,694 read=39,004, temp read=4,767 written=4,753
3. 58,548.100 421,913.494 ↓ 366,265.0 366,265 1

Sort (cost=534,212.88..534,212.89 rows=1 width=41) (actual time=411,349.461..421,913.494 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,102,886 read=39,004, temp read=4,767 written=4,753
4. 77,030.021 363,365.394 ↓ 366,265.0 366,265 1

Nested Loop Left Join (cost=338,948.06..534,212.87 rows=1 width=41) (actual time=27,771.968..363,365.394 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,558,975 read=33,068, temp read=2,234 written=2,220
5. 76,455.970 227,000.443 ↓ 366,265.0 366,265 1

Nested Loop Left Join (cost=338,948.06..534,185.32 rows=1 width=25) (actual time=27,771.678..227,000.443 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,163,795 read=33,068, temp read=2,234 written=2,220
6. 903.505 91,209.543 ↓ 366,265.0 366,265 1

Nested Loop (cost=338,948.06..534,157.78 rows=1 width=21) (actual time=27,771.649..91,209.543 rows=366,265 loops=1)

  • Buffers: shared hit=1,768,615 read=33,068, temp read=2,234 written=2,220
7. 578.279 28,302.116 ↓ 410,622.0 410,622 1

Hash Right Join (cost=338,947.63..534,153.01 rows=1 width=27) (actual time=27,755.480..28,302.116 rows=410,622 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,951
  • Buffers: shared hit=82,428 read=17,835, temp read=2,234 written=2,220
8. 25,246.761 25,246.761 ↑ 1.0 33,257 1

Index Scan using idx_oms_trade_notification_esb_trade_date on oms_trade_notification_esb (cost=0.43..170,059.81 rows=33,766 width=8) (actual time=23.959..25,246.761 rows=33,257 loops=1)

  • Index Cond: (deal_status = 7)
  • Filter: (deal_no IS NOT NULL)
  • Buffers: shared hit=19,434 read=17,835
9. 397.220 2,477.076 ↑ 1.0 412,573 1

Hash (cost=330,870.18..330,870.18 rows=417,682 width=27) (actual time=2,477.076..2,477.076 rows=412,573 loops=1)

  • Buckets: 65,536 Batches: 8 Memory Usage: 3,546kB
  • Buffers: shared hit=62,994, temp written=2,113
10. 1,739.186 2,079.856 ↑ 1.0 412,573 1

Bitmap Heap Scan on oms_trade_notification_esb oms (cost=13,947.15..330,870.18 rows=417,682 width=27) (actual time=350.925..2,079.856 rows=412,573 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,219
  • Heap Blocks: exact=32,039 lossy=26,588
  • Buffers: shared hit=62,994
11. 340.670 340.670 ↓ 1.0 739,144 1

Bitmap Index Scan on oms_trade_notification_esb_currency (cost=0.00..13,842.73 rows=706,973 width=0) (actual time=340.670..340.670 rows=739,144 loops=1)

  • Index Cond: (trade_date > '2019-01-01'::date)
  • Buffers: shared hit=4,367
12. 62,003.922 62,003.922 ↑ 1.0 1 410,622

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.137..0.151 rows=1 loops=410,622)

  • 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,686,187 read=15,233
13. 59,334.930 59,334.930 ↑ 1.0 691 366,265

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

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

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

  • Buffers: shared hit=4,395,180
Planning time : 3.841 ms
Execution time : 425,012.054 ms