explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Goic : Optimization for: plan #6SLu

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 4.542 3,140.001 ↓ 12.1 2,268 1

Sort (cost=893,570.30..893,570.77 rows=187 width=418) (actual time=3,139.910..3,140.001 rows=2,268 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: 409kB
  • Buffers: shared hit=108515 read=1412 dirtied=54837, temp read=13202 written=21289
2.          

CTE uslistings

3. 33.339 381.051 ↓ 1.1 131,045 1

WindowAgg (cost=47,980.04..50,297.86 rows=115,891 width=20) (actual time=330.846..381.051 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. 148.777 347.712 ↓ 1.1 131,045 1

Sort (cost=47,980.04..48,269.77 rows=115,891 width=12) (actual time=330.832..347.712 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. 198.935 198.935 ↓ 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.160..198.935 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. 1.580 320.410 ↑ 4.9 2,632 1

HashAggregate (cost=368,009.21..368,138.44 rows=12,923 width=192) (actual time=319.881..320.410 rows=2,632 loops=1)

  • Output: latest_orders_prev31days_liquid.source_system, latest_orders_prev31days_liquid.instrument_id, latest_orders_prev31days_liquid.local_security_code, latest_orders_prev31days_liquid.security_board, latest_orders_prev31days_liquid.oms_exchang (...)
  • Group Key: latest_orders_prev31days_liquid.source_system, latest_orders_prev31days_liquid.instrument_id, latest_orders_prev31days_liquid.local_security_code, latest_orders_prev31days_liquid.security_board, latest_orders_prev31days_liquid.oms_exch (...)
  • Buffers: shared hit=58928 read=1412 dirtied=54837
8. 0.136 318.830 ↑ 4.9 2,632 1

Append (cost=2,116.31..367,847.67 rows=12,923 width=192) (actual time=310.287..318.830 rows=2,632 loops=1)

  • Buffers: shared hit=58928 read=1412 dirtied=54837
9. 0.001 0.087 ↓ 0.0 0 1

Unique (cost=2,116.31..2,117.24 rows=62 width=24) (actual time=0.086..0.087 rows=0 loops=1)

  • Output: latest_orders_prev31days_liquid.source_system, latest_orders_prev31days_liquid.instrument_id, latest_orders_prev31days_liquid.local_security_code, latest_orders_prev31days_liquid.security_board, latest_orders_prev31days_liquid (...)
  • Buffers: shared read=3
10. 0.011 0.086 ↓ 0.0 0 1

Sort (cost=2,116.31..2,116.46 rows=62 width=24) (actual time=0.086..0.086 rows=0 loops=1)

  • Output: latest_orders_prev31days_liquid.source_system, latest_orders_prev31days_liquid.instrument_id, latest_orders_prev31days_liquid.local_security_code, latest_orders_prev31days_liquid.security_board, latest_orders_prev31days_ (...)
  • Sort Key: latest_orders_prev31days_liquid.source_system, latest_orders_prev31days_liquid.instrument_id, latest_orders_prev31days_liquid.local_security_code, latest_orders_prev31days_liquid.security_board, latest_orders_prev31day (...)
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared read=3
11. 0.075 0.075 ↓ 0.0 0 1

Index Scan using latest_orders_prev31days_liquid_client_reference_trade_date_idx on trade_brain.latest_orders_prev31days_liquid (cost=0.43..2,114.46 rows=62 width=24) (actual time=0.075..0.075 rows=0 loops=1)

  • Output: latest_orders_prev31days_liquid.source_system, latest_orders_prev31days_liquid.instrument_id, latest_orders_prev31days_liquid.local_security_code, latest_orders_prev31days_liquid.security_board, latest_orders_prev3 (...)
  • Index Cond: ((latest_orders_prev31days_liquid.client_reference = 5151411) AND (latest_orders_prev31days_liquid.trade_date >= '2020-05-19'::date) AND (latest_orders_prev31days_liquid.trade_date <= '2020-05-28'::date))
  • Filter: (((latest_orders_prev31days_liquid.order_rank)::text = 'CLOG'::text) AND ((latest_orders_prev31days_liquid.exchange_id)::text = ANY ('{JTOS,JCHX,JSBI,JTSE}'::text[])))
  • Buffers: shared read=3
12. 0.233 310.473 ↑ 7.1 1,079 1

Unique (cost=356,283.07..356,398.44 rows=7,661 width=22) (actual time=310.200..310.473 rows=1,079 loops=1)

  • Output: latest_orders_prev31days_viper.source_system, latest_orders_prev31days_viper.instrument_id, latest_orders_prev31days_viper.local_security_code, latest_orders_prev31days_viper.security_board, latest_orders_prev31days_viper.oms_ (...)
  • Buffers: shared hit=58338 read=1360 dirtied=54837
13. 1.193 310.240 ↑ 7.0 1,099 1

Sort (cost=356,283.07..356,302.30 rows=7,691 width=22) (actual time=310.198..310.240 rows=1,099 loops=1)

  • Output: latest_orders_prev31days_viper.source_system, latest_orders_prev31days_viper.instrument_id, latest_orders_prev31days_viper.local_security_code, latest_orders_prev31days_viper.security_board, latest_orders_prev31days_vipe (...)
  • Sort Key: latest_orders_prev31days_viper.source_system, latest_orders_prev31days_viper.instrument_id, latest_orders_prev31days_viper.local_security_code, latest_orders_prev31days_viper.security_board, latest_orders_prev31days_vi (...)
  • Sort Method: quicksort Memory: 134kB
  • Buffers: shared hit=58338 read=1360 dirtied=54837
14. 264.636 309.047 ↑ 7.0 1,099 1

Bitmap Heap Scan on trade_brain.latest_orders_prev31days_viper (cost=4,037.81..355,786.66 rows=7,691 width=22) (actual time=140.528..309.047 rows=1,099 loops=1)

  • Output: latest_orders_prev31days_viper.source_system, latest_orders_prev31days_viper.instrument_id, latest_orders_prev31days_viper.local_security_code, latest_orders_prev31days_viper.security_board, latest_orders_prev31day (...)
  • Recheck Cond: ((latest_orders_prev31days_viper.client_reference = 5151411) AND (latest_orders_prev31days_viper.trade_date >= '2020-05-19'::date) AND (latest_orders_prev31days_viper.trade_date <= '2020-05-28'::date))
  • Filter: ((latest_orders_prev31days_viper.order_rank)::text = 'DealingOrder'::text)
  • Rows Removed by Filter: 495861
  • Heap Blocks: exact=58337
  • Buffers: shared hit=58338 read=1360 dirtied=54837
15. 44.411 44.411 ↓ 2.9 496,960 1

Bitmap Index Scan on latest_orders_prev31days_viper_client_reference_trade_date_idx (cost=0.00..4,035.89 rows=171,796 width=0) (actual time=44.411..44.411 rows=496,960 loops=1)

  • Index Cond: ((latest_orders_prev31days_viper.client_reference = 5151411) AND (latest_orders_prev31days_viper.trade_date >= '2020-05-19'::date) AND (latest_orders_prev31days_viper.trade_date <= '2020-05-28'::date))
  • Buffers: shared hit=1 read=1360
16. 3.062 8.134 ↑ 3.3 1,553 1

HashAggregate (cost=9,150.77..9,202.77 rows=5,200 width=24) (actual time=7.862..8.134 rows=1,553 loops=1)

  • Output: latest_orders_prev31days_python.source_system, latest_orders_prev31days_python.instrument_id, latest_orders_prev31days_python.local_security_code, latest_orders_prev31days_python.security_board, latest_orders_prev31days_python (...)
  • Group Key: latest_orders_prev31days_python.source_system, latest_orders_prev31days_python.instrument_id, latest_orders_prev31days_python.local_security_code, latest_orders_prev31days_python.security_board, latest_orders_prev31days_pyt (...)
  • Buffers: shared hit=590 read=49
17. 4.119 5.072 ↑ 1.0 6,414 1

Bitmap Heap Scan on trade_brain.latest_orders_prev31days_python (cost=289.13..9,070.10 rows=6,453 width=24) (actual time=1.009..5.072 rows=6,414 loops=1)

  • Output: latest_orders_prev31days_python.uuid, latest_orders_prev31days_python.source_system, latest_orders_prev31days_python.ldm_version, latest_orders_prev31days_python.model_name, latest_orders_prev31days_python.order_id, late (...)
  • Recheck Cond: ((latest_orders_prev31days_python.trade_date >= '2020-05-19'::date) AND (latest_orders_prev31days_python.trade_date <= '2020-05-28'::date))
  • Filter: ((latest_orders_prev31days_python.order_rank)::text = 'SalesOrder'::text)
  • Rows Removed by Filter: 3158
  • Heap Blocks: exact=589
  • Buffers: shared hit=590 read=49
18. 0.953 0.953 ↓ 1.0 9,572 1

Bitmap Index Scan on latest_orders_prev31days_python_trade_date_order_id_idx (cost=0.00..287.52 rows=9,510 width=0) (actual time=0.953..0.953 rows=9,572 loops=1)

  • Index Cond: ((latest_orders_prev31days_python.trade_date >= '2020-05-19'::date) AND (latest_orders_prev31days_python.trade_date <= '2020-05-28'::date))
  • Buffers: shared hit=1 read=49
19.          

CTE listings

20. 281.444 365.476 ↓ 1.1 948,505 1

Hash Join (cost=54.32..50,069.68 rows=898,489 width=55) (actual time=0.492..365.476 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
21. 83.559 83.559 ↑ 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.009..83.559 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
22. 0.181 0.473 ↑ 1.0 1,303 1

Hash (cost=38.03..38.03 rows=1,303 width=8) (actual time=0.473..0.473 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
23. 0.292 0.292 ↑ 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.006..0.292 rows=1,303 loops=1)

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

CTE enrichedlistings

25. 3.187 3,132.694 ↑ 6.3 5,983 1

WindowAgg (cost=422,999.60..424,215.49 rows=37,412 width=458) (actual time=3,129.298..3,132.694 rows=5,983 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=108515 read=1412 dirtied=54837, temp read=13202 written=21289
26. 2.693 3,129.507 ↑ 6.3 5,983 1

Sort (cost=422,999.60..423,093.13 rows=37,412 width=488) (actual time=3,129.284..3,129.507 rows=5,983 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: 1022kB
  • Buffers: shared hit=108515 read=1412 dirtied=54837, temp read=13202 written=21289
27. 1.339 3,126.814 ↑ 6.3 5,983 1

Hash Left Join (cost=399,253.23..411,971.43 rows=37,412 width=488) (actual time=2,565.338..3,126.814 rows=5,983 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=108515 read=1412 dirtied=54837, temp read=13202 written=21289
28. 140.419 2,702.885 ↑ 2.2 5,983 1

Merge Left Join (cost=396,638.45..408,013.30 rows=12,923 width=390) (actual time=2,142.703..2,702.885 rows=5,983 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=83734 read=1412 dirtied=54837, temp read=12835 written=20427
29. 1.102 322.401 ↑ 4.9 2,632 1

Sort (cost=1,140.95..1,173.26 rows=12,923 width=192) (actual time=322.033..322.401 rows=2,632 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: 302kB
  • Buffers: shared hit=58928 read=1412 dirtied=54837
30. 321.299 321.299 ↑ 4.9 2,632 1

CTE Scan on orders o (cost=0.00..258.46 rows=12,923 width=192) (actual time=319.884..321.299 rows=2,632 loops=1)

  • Output: o.source_system, o.instrument_id, o.local_security_code, o.security_board, o.oms_exchange_id
  • Buffers: shared hit=58928 read=1412 dirtied=54837
31. 77.440 2,240.065 ↓ 1.1 947,964 1

Materialize (cost=395,497.50..399,989.94 rows=898,489 width=352) (actual time=1,820.647..2,240.065 rows=947,964 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
32. 1,311.170 2,162.625 ↓ 1.1 947,064 1

Sort (cost=395,497.50..397,743.72 rows=898,489 width=352) (actual time=1,820.639..2,162.625 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
33. 851.455 851.455 ↓ 1.1 948,505 1

CTE Scan on listings n (cost=0.00..17,969.78 rows=898,489 width=352) (actual time=0.494..851.455 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
34. 1.286 422.590 ↓ 16.4 9,475 1

Hash (cost=2,607.55..2,607.55 rows=579 width=102) (actual time=422.590..422.590 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
35. 421.304 421.304 ↓ 16.4 9,475 1

CTE Scan on uslistings us (cost=0.00..2,607.55 rows=579 width=102) (actual time=330.854..421.304 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
36. 3,135.459 3,135.459 ↓ 12.1 2,268 1

CTE Scan on enrichedlistings (cost=0.00..841.77 rows=187 width=418) (actual time=3,129.305..3,135.459 rows=2,268 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: 3715
  • Buffers: shared hit=108515 read=1412 dirtied=54837, temp read=13202 written=21289
Planning time : 1.244 ms
Execution time : 3,168.634 ms