explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KrMh

Settings
# exclusive inclusive rows x rows loops node
1. 362.743 111,954.923 ↓ 19,570.0 19,570 1

WindowAgg (cost=2,904,057.30..2,909,505.62 rows=1 width=1,296) (actual time=14,264.885..111,954.923 rows=19,570 loops=1)

  • Output: to_char((('now'::cstring)::date)::timestamp with time zone, 'yyyyMMdd'::text), "*SELECT* 1".order_src, COALESCE(("*SELECT* 1".client_reference)::text, 'INVALID_SBP'::text), "*SELECT* 1".assignee, "*SELECT* 1".dealer_id, 'ASIA', 'CASH_JP', ((("*SE (...)
  • Buffers: shared hit=5797748 read=2140172 dirtied=517 written=623, temp read=1451 written=1445
2. 85.775 14,290.140 ↓ 19,570.0 19,570 1

Sort (cost=2,904,057.30..2,904,057.31 rows=1 width=269) (actual time=14,264.518..14,290.140 rows=19,570 loops=1)

  • Output: "*SELECT* 1".created_timestamp, "*SELECT* 1".order_id, "*SELECT* 1".order_src, "*SELECT* 1".client_reference, "*SELECT* 1".assignee, "*SELECT* 1".dealer_id, "*SELECT* 1".version, s.order_id, s.parent_order_id, "*SELECT* 1".leg, "*SELECT* 1" (...)
  • Sort Key: "*SELECT* 1".order_id, "*SELECT* 1".created_timestamp
  • Sort Method: external merge Disk: 5696kB
  • Buffers: shared hit=958178 read=2138875 dirtied=517 written=623, temp read=1451 written=1445
3. 35.858 14,204.365 ↓ 19,570.0 19,570 1

Hash Join (cost=367,717.37..2,904,057.29 rows=1 width=269) (actual time=10,112.338..14,204.365 rows=19,570 loops=1)

  • Output: "*SELECT* 1".created_timestamp, "*SELECT* 1".order_id, "*SELECT* 1".order_src, "*SELECT* 1".client_reference, "*SELECT* 1".assignee, "*SELECT* 1".dealer_id, "*SELECT* 1".version, s.order_id, s.parent_order_id, "*SELECT* 1".leg, "*SELE (...)
  • Hash Cond: (s.client_reference = c.client_id)
  • Buffers: shared hit=958178 read=2138875 dirtied=517 written=623, temp read=737 written=731
4. 12.013 14,072.150 ↓ 19,570.0 19,570 1

Hash Join (cost=357,828.94..2,893,240.83 rows=1 width=273) (actual time=10,015.548..14,072.150 rows=19,570 loops=1)

  • Output: "*SELECT* 1".order_src, "*SELECT* 1".client_reference, "*SELECT* 1".assignee, "*SELECT* 1".dealer_id, "*SELECT* 1".version, "*SELECT* 1".leg, "*SELECT* 1".quantity, "*SELECT* 1".price, "*SELECT* 1".order_type, "*SELECT* 1".creat (...)
  • Hash Cond: (((s.local_security_code)::text = (l.local_security_code)::text) AND (s.instrument_id = l.instrument_id))
  • Buffers: shared hit=958177 read=2135246 dirtied=516 written=479
5. 495.763 13,819.555 ↓ 19,570.0 19,570 1

Hash Join (cost=316,309.37..2,851,720.13 rows=1 width=228) (actual time=9,774.866..13,819.555 rows=19,570 loops=1)

  • Output: "*SELECT* 1".order_src, "*SELECT* 1".client_reference, "*SELECT* 1".assignee, "*SELECT* 1".dealer_id, "*SELECT* 1".version, "*SELECT* 1".leg, "*SELECT* 1".quantity, "*SELECT* 1".price, "*SELECT* 1".order_type, "*SELECT* 1" (...)
  • Hash Cond: ((s.trade_date = "*SELECT* 1".trade_date) AND ((s.order_id)::text = ("*SELECT* 1".order_id)::text) AND ((s.exchange_id)::text = ("*SELECT* 1".exchange_id)::text))
  • Buffers: shared hit=958177 read=2104484 dirtied=516 written=476
6. 11,952.304 11,952.304 ↓ 23.2 1,243,894 1

Seq Scan on trade_brain.latest_orders s (cost=0.00..2,534,808.20 rows=53,560 width=107) (actual time=9.064..11,952.304 rows=1,243,894 loops=1)

  • Output: s.uuid, s.source_system, s.ldm_version, s.model_name, s.order_id, s.client_reference, s.created_timestamp, s.dealer_id, s.oms_exchange_id, s.dealer_instruction, s.daily_cumulative_quantity, s.order_type, s.order_dura (...)
  • Filter: (((s.source_system)::text = 'LIQUID'::text) AND ((s.order_rank)::text = 'MLOG'::text))
  • Rows Removed by Filter: 39290147
  • Buffers: shared hit=161 read=2033370 dirtied=424 written=383
7. 18.537 1,371.488 ↓ 1,397.9 19,570 1

Hash (cost=316,309.13..316,309.13 rows=14 width=130) (actual time=1,371.488..1,371.488 rows=19,570 loops=1)

  • Output: "*SELECT* 1".order_src, "*SELECT* 1".client_reference, "*SELECT* 1".assignee, "*SELECT* 1".dealer_id, "*SELECT* 1".version, "*SELECT* 1".leg, "*SELECT* 1".quantity, "*SELECT* 1".price, "*SELECT* 1".order_type, "*SELE (...)
  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 3251kB
  • Buffers: shared hit=958016 read=71114 dirtied=92 written=93
8. 3.607 1,352.951 ↓ 1,397.9 19,570 1

Append (cost=0.57..316,309.13 rows=14 width=130) (actual time=278.927..1,352.951 rows=19,570 loops=1)

  • Buffers: shared hit=958016 read=71114 dirtied=92 written=93
9. 2.792 790.952 ↓ 1,022.8 12,273 1

Subquery Scan on *SELECT* 1 (cost=0.57..158,154.61 rows=12 width=130) (actual time=278.926..790.952 rows=12,273 loops=1)

  • Output: "*SELECT* 1".order_src, "*SELECT* 1".client_reference, "*SELECT* 1".assignee, "*SELECT* 1".dealer_id, "*SELECT* 1".version, "*SELECT* 1".leg, "*SELECT* 1".quantity, "*SELECT* 1".price, "*SELECT* 1".order_ (...)
  • Buffers: shared hit=479008 read=35557 dirtied=92 written=93
10. 788.160 788.160 ↓ 1,022.8 12,273 1

Index Scan using idx_tbd_orders_trddate on trade_brain_data.orders (cost=0.57..158,154.49 rows=12 width=170) (actual time=278.924..788.160 rows=12,273 loops=1)

  • Output: orders.trade_date, 'NEW_ORDER'::text, orders.client_reference, orders.assignee, orders.dealer_id, orders.leg, orders.quantity, orders.price, orders.order_type, NULL::timestamp without time zone, ord (...)
  • Index Cond: (orders.trade_date = (('now'::cstring)::date - 0))
  • Filter: ((NOT orders.archived) AND (orders.maturity_month_year IS NULL) AND ((orders.exchange_id)::text = 'JTSE'::text) AND ((orders.source_system)::text = 'LIQUID'::text) AND ((orders.order_rank)::text = ' (...)
  • Rows Removed by Filter: 519651
  • Buffers: shared hit=479008 read=35557 dirtied=92 written=93
11. 1.856 558.392 ↓ 3,648.5 7,297 1

Subquery Scan on *SELECT* 2 (cost=0.57..158,154.51 rows=2 width=130) (actual time=42.641..558.392 rows=7,297 loops=1)

  • Output: "*SELECT* 2".order_src, "*SELECT* 2".client_reference, "*SELECT* 2".assignee, "*SELECT* 2".dealer_id, "*SELECT* 2".version, "*SELECT* 2".leg, "*SELECT* 2".quantity, "*SELECT* 2".price, "*SELECT* 2".order_ (...)
  • Buffers: shared hit=479008 read=35557
12. 556.536 556.536 ↓ 3,648.5 7,297 1

Index Scan using idx_tbd_orders_trddate on trade_brain_data.orders orders_1 (cost=0.57..158,154.49 rows=2 width=170) (actual time=42.638..556.536 rows=7,297 loops=1)

  • Output: orders_1.trade_date, 'AMD_ORDER'::text, orders_1.client_reference, orders_1.assignee, orders_1.dealer_id, orders_1.leg, orders_1.quantity, orders_1.price, orders_1.order_type, NULL::timestamp withou (...)
  • Index Cond: (orders_1.trade_date = (('now'::cstring)::date - 0))
  • Filter: ((NOT orders_1.archived) AND (orders_1.maturity_month_year IS NULL) AND ((orders_1.last_action)::text = ANY ('{AMEND_DONE,UNSOLICITED_AMENDED}'::text[])) AND ((orders_1.exchange_id)::text = 'JTSE':: (...)
  • Rows Removed by Filter: 524627
  • Buffers: shared hit=479008 read=35557
13. 2.007 240.582 ↓ 4.5 4,151 1

Hash (cost=41,505.76..41,505.76 rows=920 width=58) (actual time=240.582..240.582 rows=4,151 loops=1)

  • Output: l.abbreviated_stock_description, l.sedol, l.isin, l.exchange_mic, l.local_security_code, l.instrument_id
  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 401kB
  • Buffers: shared read=30762 written=3
14. 238.575 238.575 ↓ 4.5 4,151 1

Seq Scan on reference_brain.latest_listing l (cost=0.00..41,505.76 rows=920 width=58) (actual time=0.297..238.575 rows=4,151 loops=1)

  • Output: l.abbreviated_stock_description, l.sedol, l.isin, l.exchange_mic, l.local_security_code, l.instrument_id
  • Filter: ((l.exchange_id)::text = 'JP'::text)
  • Rows Removed by Filter: 855726
  • Buffers: shared read=30762 written=3
15. 45.061 96.357 ↓ 1.0 237,113 1

Hash (cost=5,999.97..5,999.97 rows=236,997 width=4) (actual time=96.357..96.357 rows=237,113 loops=1)

  • Output: c.client_id
  • Buckets: 131072 Batches: 4 Memory Usage: 3108kB
  • Buffers: shared hit=1 read=3629 dirtied=1 written=144, temp written=519
16. 51.296 51.296 ↓ 1.0 237,113 1

Seq Scan on reference_brain.latest_client_details c (cost=0.00..5,999.97 rows=236,997 width=4) (actual time=0.015..51.296 rows=237,113 loops=1)

  • Output: c.client_id
  • Buffers: shared hit=1 read=3629 dirtied=1 written=144
17.          

SubPlan (forWindowAgg)

18. 78.280 97,302.040 ↑ 1.0 1 19,570

Aggregate (cost=5,448.21..5,448.22 rows=1 width=8) (actual time=4.972..4.972 rows=1 loops=19,570)

  • Output: min(latest_trade_match.created_timestamp)
  • Buffers: shared hit=4839570 read=1297
19. 39.140 97,223.760 ↓ 0.0 0 19,570

Bitmap Heap Scan on trade_brain.latest_trade_match (cost=5,424.13..5,448.20 rows=1 width=8) (actual time=4.968..4.968 rows=0 loops=19,570)

  • Output: latest_trade_match.uuid, latest_trade_match.source_system, latest_trade_match.ldm_version, latest_trade_match.model_name, latest_trade_match.trade_match_id, latest_trade_match.trade_match_execution_qty, latest_trade_match.trade_matc (...)
  • Recheck Cond: (((latest_trade_match.order_reference)::text = (s.order_id)::text) AND (latest_trade_match.trade_date = s.trade_date))
  • Filter: ((latest_trade_match.source_system)::text = 'LIQUID'::text)
  • Heap Blocks: exact=7837
  • Buffers: shared hit=4839570 read=1297
20. 34.264 97,184.620 ↓ 0.0 0 19,570

BitmapAnd (cost=5,424.13..5,424.13 rows=6 width=0) (actual time=4.966..4.966 rows=0 loops=19,570)

  • Buffers: shared hit=4832333 read=697
21. 117.420 117.420 ↓ 0.0 0 19,570

Bitmap Index Scan on idx_tb_ltm_ordref (cost=0.00..65.59 rows=2,270 width=0) (actual time=0.006..0.006 rows=0 loops=19,570)

  • Index Cond: ((latest_trade_match.order_reference)::text = (s.order_id)::text)
  • Buffers: shared hit=78450 read=64
22. 97,032.936 97,032.936 ↑ 1.6 180,038 7,501

Bitmap Index Scan on idx_tb_ltm_trddate (cost=0.00..5,358.28 rows=283,962 width=0) (actual time=12.936..12.936 rows=180,038 loops=7,501)

  • Index Cond: (latest_trade_match.trade_date = s.trade_date)
  • Buffers: shared hit=4753883 read=633