explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HzH3

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 218.798 1,256,364.950 ↓ 0.0 0 1

Gather (cost=38,720,419.39..38,727,775.38 rows=1,544 width=4,798) (actual time=1,256,359.037..1,256,364.950 rows=0 loops=1)

  • Output: orderhist.uuid, orderhist.source_system, orderhist.ldm_version, orderhist.model_name, orderhist.order_id, orderhist.client_reference, orderhist.created_timestamp, orderhist.dealer_id, orderhist.oms_exchange_id, orderhist.dealer_instruction, order (...)
  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=54718 read=32388450, temp read=160514 written=277601
2. 0.004 1,256,146.152 ↓ 0.0 0 3 / 3

Nested Loop (cost=38,719,419.39..38,726,620.98 rows=643 width=4,798) (actual time=1,256,146.151..1,256,146.152 rows=0 loops=3)

  • Output: orderhist.uuid, orderhist.source_system, orderhist.ldm_version, orderhist.model_name, orderhist.order_id, orderhist.client_reference, orderhist.created_timestamp, orderhist.dealer_id, orderhist.oms_exchange_id, orderhist.dealer_instruction, (...)
  • Buffers: shared hit=54718 read=32388450, temp read=160514 written=277601
  • Worker 0: actual time=1256094.800..1256094.801 rows=0 loops=1
  • Buffers: shared hit=18235 read=10760686, temp read=50492 written=88552
  • Worker 1: actual time=1256285.971..1256285.971 rows=0 loops=1
  • Buffers: shared hit=18233 read=10851357, temp read=60099 written=101619
3. 0.015 1,256,146.148 ↓ 0.0 0 3 / 3

Merge Join (cost=38,719,418.83..38,721,780.50 rows=643 width=3,812) (actual time=1,256,146.148..1,256,146.148 rows=0 loops=3)

  • Output: orderhist.uuid, orderhist.source_system, orderhist.ldm_version, orderhist.model_name, orderhist.order_id, orderhist.client_reference, orderhist.created_timestamp, orderhist.dealer_id, orderhist.oms_exchange_id, orderhist.dealer_instru (...)
  • Merge Cond: ((orderhist.order_id)::text = (sglord.order_id)::text)
  • Buffers: shared hit=54718 read=32388450, temp read=160514 written=277601
  • Worker 0: actual time=1256094.797..1256094.797 rows=0 loops=1
  • Buffers: shared hit=18235 read=10760686, temp read=50492 written=88552
  • Worker 1: actual time=1256285.967..1256285.967 rows=0 loops=1
  • Buffers: shared hit=18233 read=10851357, temp read=60099 written=101619
4. 6,145.085 1,255,936.359 ↑ 379,581.0 1 3 / 3

Sort (cost=38,029,959.73..38,030,908.68 rows=379,581 width=1,534) (actual time=1,255,936.359..1,255,936.359 rows=1 loops=3)

  • Output: orderhist.uuid, orderhist.source_system, orderhist.ldm_version, orderhist.model_name, orderhist.order_id, orderhist.client_reference, orderhist.created_timestamp, orderhist.dealer_id, orderhist.oms_exchange_id, orderhist.dealer_ (...)
  • Sort Key: orderhist.order_id
  • Sort Method: external merge Disk: 301624kB
  • Buffers: shared hit=338 read=32388450, temp read=160514 written=277601
  • Worker 0: actual time=1255872.277..1255872.277 rows=1 loops=1
  • Buffers: shared hit=108 read=10760686, temp read=50492 written=88552
  • Worker 1: actual time=1256071.230..1256071.230 rows=1 loops=1
  • Buffers: shared hit=106 read=10851357, temp read=60099 written=101619
5. 1,249,791.274 1,249,791.274 ↑ 1.0 369,975 3 / 3

Parallel Seq Scan on trade_brain_data.orders orderhist (cost=0.00..37,488,795.87 rows=379,581 width=1,534) (actual time=941,816.215..1,249,791.274 rows=369,975 loops=3)

  • Output: orderhist.uuid, orderhist.source_system, orderhist.ldm_version, orderhist.model_name, orderhist.order_id, orderhist.client_reference, orderhist.created_timestamp, orderhist.dealer_id, orderhist.oms_exchange_id, orderhist.d (...)
  • Filter: (((orderhist.last_modified_event_type)::text = 'NEW_ACCEPTED'::text) AND (orderhist.created_timestamp >= to_timestamp(concat('2020-01-20', ' 21:00:01'), 'YYYY-MM-DD hh24:mi:ss'::text)) AND (orderhist.created_timestamp <= t (...)
  • Rows Removed by Filter: 148063572
  • Buffers: shared hit=328 read=32388450
  • Worker 0: actual time=930760.769..1249947.002 rows=360194 loops=1
  • Buffers: shared hit=103 read=10760686
  • Worker 1: actual time=931119.815..1250836.894 rows=393370 loops=1
  • Buffers: shared hit=101 read=10851357
6. 0.011 209.774 ↓ 0.0 0 3 / 3

Materialize (cost=689,456.17..689,764.72 rows=61,710 width=2,278) (actual time=209.774..209.774 rows=0 loops=3)

  • Output: sglord.uuid, sglord.source_system, sglord.ldm_version, sglord.model_name, sglord.order_id, sglord.client_reference, sglord.created_timestamp, sglord.dealer_id, sglord.oms_exchange_id, sglord.dealer_instruction, sglord.daily_cumu (...)
  • Buffers: shared hit=54380
  • Worker 0: actual time=222.504..222.504 rows=0 loops=1
  • Buffers: shared hit=18127
  • Worker 1: actual time=214.722..214.722 rows=0 loops=1
  • Buffers: shared hit=18127
7. 0.024 209.763 ↓ 0.0 0 3 / 3

Sort (cost=689,456.17..689,610.44 rows=61,710 width=2,278) (actual time=209.763..209.763 rows=0 loops=3)

  • Output: sglord.uuid, sglord.source_system, sglord.ldm_version, sglord.model_name, sglord.order_id, sglord.client_reference, sglord.created_timestamp, sglord.dealer_id, sglord.oms_exchange_id, sglord.dealer_instruction, sglord.dail (...)
  • Sort Key: sglord.order_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=54380
  • Worker 0: actual time=222.492..222.492 rows=0 loops=1
  • Buffers: shared hit=18127
  • Worker 1: actual time=214.710..214.710 rows=0 loops=1
  • Buffers: shared hit=18127
8. 209.739 209.739 ↓ 0.0 0 3 / 3

Index Scan using latest_orders_td_ss_cr_idx on trade_brain.latest_orders sglord (cost=0.56..563,054.14 rows=61,710 width=2,278) (actual time=209.739..209.739 rows=0 loops=3)

  • Output: sglord.uuid, sglord.source_system, sglord.ldm_version, sglord.model_name, sglord.order_id, sglord.client_reference, sglord.created_timestamp, sglord.dealer_id, sglord.oms_exchange_id, sglord.dealer_instruction, sglor (...)
  • Index Cond: ((sglord.trade_date >= '2020-01-20'::date) AND (sglord.trade_date <= '2020-02-06'::date) AND (sglord.client_reference = 5151411))
  • Filter: (((sglord.order_rank)::text <> 'ALOG'::text) AND ((sglord.exchange_id)::text = ANY ('{JTOS,JCHX,JSBI,JTSE,SEHK,NSE,BSE}'::text[])))
  • Rows Removed by Filter: 37259
  • Buffers: shared hit=54380
  • Worker 0: actual time=222.465..222.466 rows=0 loops=1
  • Buffers: shared hit=18127
  • Worker 1: actual time=214.687..214.687 rows=0 loops=1
  • Buffers: shared hit=18127
9. 0.000 0.000 ↓ 0.0 0 / 3

Index Scan using latest_new_order_request_pkey on trade_brain.latest_new_order_request neworderreq (cost=0.56..7.52 rows=1 width=986) (never executed)

  • Output: neworderreq.uuid, neworderreq.source_system, neworderreq.ldm_version, neworderreq.model_name, neworderreq.order_id, neworderreq.trade_date, neworderreq.parent_order_id, neworderreq.client_id, neworderreq.client_order_id, neworderreq.c (...)
  • Index Cond: ((neworderreq.order_id)::text = (orderhist.order_id)::text)
Planning time : 1.804 ms
Execution time : 1,256,408.485 ms