explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6SLu

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 7.117 9,628.831 ↓ 27.7 3,238 1

Sort (cost=3,193,686.90..3,193,687.19 rows=117 width=418) (actual time=9,628.683..9,628.831 rows=3,238 loops=1)

  • Output: enrichedlistings.instrument_id, enrichedlistings.local_security_code, enrichedlistings.oms_exchange_id, enrichedlistings.security_board, enrichedlistings.security_market, enrichedlistings.isin, enrichedlistings.cusip, enrichedlistings.sedol, enri (...)
  • Sort Key: enrichedlistings.local_security_code, enrichedlistings.security_market
  • Sort Method: quicksort Memory: 544kB
  • Buffers: shared hit=924363 read=236819, temp read=13202 written=21289
2.          

CTE uslistings

3. 33.695 388.324 ↓ 1.1 131,045 1

WindowAgg (cost=47,980.04..50,297.86 rows=115,891 width=20) (actual time=337.120..388.324 rows=131,045 loops=1)

  • Output: row_number() OVER (?), latest_listing.instrument_id, latest_listing.local_security_code, latest_listing.exchange_id
  • Buffers: shared hit=24781, temp read=367 written=368
4. 152.990 354.629 ↓ 1.1 131,045 1

Sort (cost=47,980.04..48,269.77 rows=115,891 width=12) (actual time=337.104..354.629 rows=131,045 loops=1)

  • Output: latest_listing.instrument_id, latest_listing.exchange_id, latest_listing.local_security_code
  • Sort Key: latest_listing.instrument_id, latest_listing.exchange_id DESC
  • Sort Method: external merge Disk: 2936kB
  • Buffers: shared hit=24781, temp read=367 written=368
5. 201.639 201.639 ↓ 1.1 131,045 1

Seq Scan on reference_brain.latest_listing (cost=0.00..36,251.21 rows=115,891 width=12) (actual time=24.847..201.639 rows=131,045 loops=1)

  • Output: latest_listing.instrument_id, latest_listing.exchange_id, latest_listing.local_security_code
  • Filter: ((latest_listing.exchange_mic IS NOT NULL) AND ((latest_listing.exchange_mic)::text !~~ '%OTC%'::text) AND ((latest_listing.security_market)::text = 'US'::text))
  • Rows Removed by Filter: 633636
  • Buffers: shared hit=24781
6.          

CTE orders

7. 4.237 6,723.473 ↑ 2.2 3,683 1

Unique (cost=2,674,113.54..2,674,235.05 rows=8,096 width=23) (actual time=6,718.230..6,723.473 rows=3,683 loops=1)

  • Output: latest_orders.source_system, latest_orders.instrument_id, latest_orders.local_security_code, latest_orders.security_board, latest_orders.oms_exchange_id
  • Buffers: shared hit=874776 read=236819
8. 22.146 6,719.236 ↓ 1.9 15,262 1

Sort (cost=2,674,113.54..2,674,133.79 rows=8,101 width=23) (actual time=6,718.224..6,719.236 rows=15,262 loops=1)

  • Output: latest_orders.source_system, latest_orders.instrument_id, latest_orders.local_security_code, latest_orders.security_board, latest_orders.oms_exchange_id
  • Sort Key: latest_orders.source_system, latest_orders.instrument_id, latest_orders.local_security_code, latest_orders.security_board, latest_orders.oms_exchange_id
  • Sort Method: quicksort Memory: 1577kB
  • Buffers: shared hit=874776 read=236819
9. 11.070 6,697.090 ↓ 1.9 15,262 1

Gather (cost=1,000.59..2,673,587.63 rows=8,101 width=23) (actual time=69.215..6,697.090 rows=15,262 loops=1)

  • Output: latest_orders.source_system, latest_orders.instrument_id, latest_orders.local_security_code, latest_orders.security_board, latest_orders.oms_exchange_id
  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=874776 read=236819
10. 6,686.020 6,686.020 ↓ 1.5 5,087 3 / 3

Parallel Index Scan using latest_orders_td_ss_cr_idx on trade_brain.latest_orders (cost=0.58..2,671,777.53 rows=3,375 width=23) (actual time=59.001..6,686.020 rows=5,087 loops=3)

  • Output: latest_orders.source_system, latest_orders.instrument_id, latest_orders.local_security_code, latest_orders.security_board, latest_orders.oms_exchange_id
  • Index Cond: ((latest_orders.trade_date >= (to_char(to_timestamp('15-MAY-20'::text, 'DD-MON-YY HH24:mi:ss:FF3'::text), 'YYYY-MM-DD'::text))::date) AND (latest_orders.trade_date <= (to_char(to_timestamp('20-MAY-20'::text, 'DD-MON- (...)
  • Filter: (CASE WHEN (((latest_orders.source_system)::text = 'LIQUID'::text) AND ((latest_orders.exchange_id)::text = ANY ('{JTOS,JCHX,JSBI,JTSE}'::text[])) AND ((latest_orders.order_rank)::text = 'CLOG'::text) AND (latest_orders. (...)
  • Rows Removed by Filter: 683765
  • Buffers: shared hit=874776 read=236819
  • Worker 0: actual time=54.711..6682.872 rows=4165 loops=1
  • Buffers: shared hit=226064 read=70447
  • Worker 1: actual time=54.497..6683.667 rows=5248 loops=1
  • Buffers: shared hit=243442 read=73841
11.          

CTE listings

12. 291.101 379.603 ↓ 1.1 948,505 1

Hash Join (cost=54.32..50,069.68 rows=898,489 width=55) (actual time=0.538..379.603 rows=948,505 loops=1)

  • Output: l.instrument_id, l.local_security_code, l.security_board, exchange_static.oms_exchange_id, l.exchange_id, l.security_market, l.isin, l.cusip, l.sedol
  • Hash Cond: ((l.exchange_id)::text = (exchange_static.exchange_id)::text)
  • Buffers: shared hit=24806
13. 88.045 88.045 ↑ 1.0 764,681 1

Seq Scan on reference_brain.latest_listing l (cost=0.00..32,427.81 rows=764,681 width=50) (actual time=0.035..88.045 rows=764,681 loops=1)

  • Output: l.reuters_identification_code, l.bloomberg_code, l.instrument_id, l.listing_id, l.abbreviated_stock_description, l.stock_description, l.exchange_id, l.currency, l.local_security_code, l.security_market, l.security_board, l.preferred (...)
  • Buffers: shared hit=24781
14. 0.175 0.457 ↑ 1.0 1,303 1

Hash (cost=38.03..38.03 rows=1,303 width=8) (actual time=0.457..0.457 rows=1,303 loops=1)

  • Output: exchange_static.oms_exchange_id, exchange_static.exchange_id
  • Buckets: 2048 Batches: 1 Memory Usage: 63kB
  • Buffers: shared hit=25
15. 0.282 0.282 ↑ 1.0 1,303 1

Seq Scan on reference_brain_data.exchange_static (cost=0.00..38.03 rows=1,303 width=8) (actual time=0.012..0.282 rows=1,303 loops=1)

  • Output: exchange_static.oms_exchange_id, exchange_static.exchange_id
  • Buffers: shared hit=25
16.          

CTE enrichedlistings

17. 4.614 9,617.560 ↑ 2.8 8,404 1

WindowAgg (cost=417,791.19..418,552.93 rows=23,438 width=458) (actual time=9,612.636..9,617.560 rows=8,404 loops=1)

  • Output: row_number() OVER (?), o.source_system, o.instrument_id, o.local_security_code, o.security_board, o.oms_exchange_id, n.security_market, n.isin, n.cusip, n.sedol, CASE WHEN ((o.instrument_id = us.ussinst_id) AND ((n.security_market)::text (...)
  • Buffers: shared hit=924363 read=236819, temp read=13202 written=21289
18. 3.631 9,612.946 ↑ 2.8 8,404 1

Sort (cost=417,791.19..417,849.79 rows=23,438 width=488) (actual time=9,612.614..9,612.946 rows=8,404 loops=1)

  • Output: o.instrument_id, o.oms_exchange_id, o.source_system, o.local_security_code, o.security_board, n.security_market, n.isin, n.cusip, n.sedol, us.ussinst_id, us.ussymbol
  • Sort Key: o.instrument_id, o.oms_exchange_id DESC
  • Sort Method: quicksort Memory: 1552kB
  • Buffers: shared hit=924363 read=236819, temp read=13202 written=21289
19. 2.041 9,609.315 ↑ 2.8 8,404 1

Hash Left Join (cost=398,799.76..410,962.50 rows=23,438 width=488) (actual time=9,038.938..9,609.315 rows=8,404 loops=1)

  • Output: o.instrument_id, o.oms_exchange_id, o.source_system, o.local_security_code, o.security_board, n.security_market, n.isin, n.cusip, n.sedol, us.ussinst_id, us.ussymbol
  • Hash Cond: (n.instrument_id = us.ussinst_id)
  • Buffers: shared hit=924363 read=236819, temp read=13202 written=21289
20. 145.266 9,176.018 ↓ 1.0 8,404 1

Merge Left Join (cost=396,184.97..407,506.13 rows=8,096 width=390) (actual time=8,607.626..9,176.018 rows=8,404 loops=1)

  • Output: o.source_system, o.instrument_id, o.local_security_code, o.security_board, o.oms_exchange_id, n.security_market, n.isin, n.cusip, n.sedol, n.instrument_id
  • Merge Cond: ((o.instrument_id = n.instrument_id) AND ((o.oms_exchange_id)::text = ((CASE WHEN (n.oms_exchange_id IS NULL) THEN n.exchange_id ELSE n.oms_exchange_id END)::text)) AND ((o.local_security_code)::text = (n.local_secur (...)
  • Buffers: shared hit=899582 read=236819, temp read=12835 written=20427
21. 1.922 6,726.142 ↑ 2.2 3,683 1

Sort (cost=687.47..707.71 rows=8,096 width=192) (actual time=6,725.810..6,726.142 rows=3,683 loops=1)

  • Output: o.source_system, o.instrument_id, o.local_security_code, o.security_board, o.oms_exchange_id
  • Sort Key: o.instrument_id, o.oms_exchange_id DESC, o.local_security_code
  • Sort Method: quicksort Memory: 384kB
  • Buffers: shared hit=874776 read=236819
22. 6,724.220 6,724.220 ↑ 2.2 3,683 1

CTE Scan on orders o (cost=0.00..161.92 rows=8,096 width=192) (actual time=6,718.236..6,724.220 rows=3,683 loops=1)

  • Output: o.source_system, o.instrument_id, o.local_security_code, o.security_board, o.oms_exchange_id
  • Buffers: shared hit=874776 read=236819
23. 77.461 2,304.610 ↓ 1.1 948,171 1

Materialize (cost=395,497.50..399,989.94 rows=898,489 width=352) (actual time=1,881.779..2,304.610 rows=948,171 loops=1)

  • Output: n.security_market, n.isin, n.cusip, n.sedol, n.instrument_id, n.local_security_code, n.oms_exchange_id, n.exchange_id, ((CASE WHEN (n.oms_exchange_id IS NULL) THEN n.exchange_id ELSE n.oms_exchange_id END)::text)
  • Buffers: shared hit=24806, temp read=12835 written=20427
24. 1,339.471 2,227.149 ↓ 1.1 947,064 1

Sort (cost=395,497.50..397,743.72 rows=898,489 width=352) (actual time=1,881.766..2,227.149 rows=947,064 loops=1)

  • Output: n.security_market, n.isin, n.cusip, n.sedol, n.instrument_id, n.local_security_code, n.oms_exchange_id, n.exchange_id, ((CASE WHEN (n.oms_exchange_id IS NULL) THEN n.exchange_id ELSE n.oms_exchange_id END)::t (...)
  • Sort Key: n.instrument_id, ((CASE WHEN (n.oms_exchange_id IS NULL) THEN n.exchange_id ELSE n.oms_exchange_id END)::text) DESC, n.local_security_code
  • Sort Method: external merge Disk: 61152kB
  • Buffers: shared hit=24806, temp read=12835 written=20427
25. 887.678 887.678 ↓ 1.1 948,505 1

CTE Scan on listings n (cost=0.00..17,969.78 rows=898,489 width=352) (actual time=0.542..887.678 rows=948,505 loops=1)

  • Output: n.security_market, n.isin, n.cusip, n.sedol, n.instrument_id, n.local_security_code, n.oms_exchange_id, n.exchange_id, CASE WHEN (n.oms_exchange_id IS NULL) THEN n.exchange_id ELSE n.oms_exchange_id END
  • Buffers: shared hit=24806, temp written=7557
26. 1.301 431.256 ↓ 16.4 9,475 1

Hash (cost=2,607.55..2,607.55 rows=579 width=102) (actual time=431.256..431.256 rows=9,475 loops=1)

  • Output: us.ussinst_id, us.ussymbol
  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 507kB
  • Buffers: shared hit=24781, temp read=367 written=862
27. 429.955 429.955 ↓ 16.4 9,475 1

CTE Scan on uslistings us (cost=0.00..2,607.55 rows=579 width=102) (actual time=337.133..429.955 rows=9,475 loops=1)

  • Output: us.ussinst_id, us.ussymbol
  • Filter: (us.serial = 1)
  • Rows Removed by Filter: 121570
  • Buffers: shared hit=24781, temp read=367 written=862
28. 9,621.714 9,621.714 ↓ 27.7 3,238 1

CTE Scan on enrichedlistings (cost=0.00..527.36 rows=117 width=418) (actual time=9,612.642..9,621.714 rows=3,238 loops=1)

  • Output: enrichedlistings.instrument_id, enrichedlistings.local_security_code, enrichedlistings.oms_exchange_id, enrichedlistings.security_board, enrichedlistings.security_market, enrichedlistings.isin, enrichedlistings.cusip, enrichedlistings.sedol (...)
  • Filter: (enrichedlistings.serial = 1)
  • Rows Removed by Filter: 5166
  • Buffers: shared hit=924363 read=236819, temp read=13202 written=21289
Planning time : 20.253 ms
Execution time : 9,654.500 ms