explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Gt5N

Settings
# exclusive inclusive rows x rows loops node
1. 8.254 374,382.081 ↓ 76.0 76 1

Limit (cost=528,656.14..528,656.15 rows=1 width=41) (actual time=359,915.379..374,382.081 rows=76 loops=1)

  • Buffers: shared hit=30,888,232 read=68,749, temp read=4,759 written=4,745
2. 3,310.148 374,373.827 ↓ 76.0 76 1

Unique (cost=528,656.14..528,656.15 rows=1 width=41) (actual time=359,915.377..374,373.827 rows=76 loops=1)

  • Buffers: shared hit=30,888,232 read=68,749, temp read=4,759 written=4,745
3. 31,772.015 371,063.679 ↓ 366,265.0 366,265 1

Sort (cost=528,656.14..528,656.14 rows=1 width=41) (actual time=359,915.375..371,063.679 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=28,979,426 read=68,749, temp read=4,759 written=4,745
4. 62,271.229 339,291.664 ↓ 366,265.0 366,265 1

Nested Loop Left Join (cost=336,780.89..528,656.13 rows=1 width=41) (actual time=109,432.859..339,291.664 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,429,806 read=68,749, temp read=2,226 written=2,212
5. 62,857.321 228,307.190 ↓ 366,265.0 366,265 1

Nested Loop Left Join (cost=336,780.89..528,628.58 rows=1 width=25) (actual time=109,432.566..228,307.190 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,034,626 read=68,749, temp read=2,226 written=2,212
6. 628.896 116,736.624 ↓ 366,265.0 366,265 1

Nested Loop (cost=336,780.89..528,601.03 rows=1 width=21) (actual time=109,432.542..116,736.624 rows=366,265 loops=1)

  • Buffers: shared hit=1,639,446 read=68,749, temp read=2,226 written=2,212
7. 1,334.011 109,964.703 ↓ 409,535.0 409,535 1

Hash Right Join (cost=336,780.46..528,596.22 rows=1 width=27) (actual time=109,432.480..109,964.703 rows=409,535 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,914
  • Buffers: shared hit=31,233 read=68,448, temp read=2,226 written=2,212
8. 12,159.444 12,159.444 ↓ 1.1 33,194 1

Index Scan using idx_oms_trade_notification_esb_trade_date on oms_trade_notification_esb (cost=0.43..163,373.09 rows=30,178 width=8) (actual time=27.996..12,159.444 rows=33,194 loops=1)

  • Index Cond: (deal_status = 7)
  • Filter: (deal_no IS NOT NULL)
  • Buffers: shared hit=28,992 read=7,958
9. 1,450.787 96,471.248 ↓ 1.1 411,449 1

Hash (cost=329,245.83..329,245.83 rows=389,616 width=27) (actual time=96,471.248..96,471.248 rows=411,449 loops=1)

  • Buckets: 65,536 Batches: 8 Memory Usage: 3,537kB
  • Buffers: shared hit=2,241 read=60,490, temp written=2,106
10. 93,558.942 95,020.461 ↓ 1.1 411,449 1

Bitmap Heap Scan on oms_trade_notification_esb oms (cost=13,158.13..329,245.83 rows=389,616 width=27) (actual time=1,476.249..95,020.461 rows=411,449 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: 286,428
  • Heap Blocks: exact=31,789 lossy=26,588
  • Buffers: shared hit=2,241 read=60,490
11. 1,461.519 1,461.519 ↓ 1.1 735,964 1

Bitmap Index Scan on oms_trade_notification_esb_currency (cost=0.00..13,060.73 rows=659,240 width=0) (actual time=1,461.519..1,461.519 rows=735,964 loops=1)

  • Index Cond: (trade_date > '2019-01-01'::date)
  • Buffers: shared hit=2,041 read=2,313
12. 6,143.025 6,143.025 ↑ 1.0 1 409,535

Index Scan using idx_equity_tca_virtu_omskey on equity_tca_virtu eq (cost=0.43..4.80 rows=1 width=41) (actual time=0.014..0.015 rows=1 loops=409,535)

  • 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,213 read=301
13. 48,713.245 48,713.245 ↑ 1.0 691 366,265

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

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

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

  • Buffers: shared hit=4,395,180
Planning time : 23.989 ms
Execution time : 374,387.057 ms