explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FcZJ : Optimization for: plan #LAXP

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.000 1,512.084 ↑ 2.0 1 1

Unique (cost=63,014.56..63,014.60 rows=2 width=340) (actual time=1,512.083..1,512.084 rows=1 loops=1)

  • Output: (CASE WHEN ((o.leg)::text = 'SellShort'::text) THEN 'Sell'::character varying ELSE o.leg END), o.daily_cumulative_quantity, o.average_price, l.bloomberg_code, l.abbreviated_stock_description, o.trade_date, l.currency
  • Buffers: shared hit=24089 read=49313
  • Output: (CASE WHEN ((o.leg)::text = 'SellShort'::text) THEN 'Sell'::character varying ELSE o.leg END), o.daily_cumulative_quantity, o.average_price, l.bloomberg_code, l.abbreviated_stock_description, o.trade_date, l.currency
  • Sort Key: (CASE WHEN ((o.leg)::text = 'SellShort'::text) THEN 'Sell'::character varying ELSE o.leg END), o.daily_cumulative_quantity, o.average_price, l.bloomberg_code, l.abbreviated_stock_description, o.trade_date, l.currency
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=24089 read=49313
  • Buffers: shared hit=24089 read=49313
  • Output: CASE WHEN ((o.leg)::text = 'SellShort'::text) THEN 'Sell'::character varying ELSE o.leg END, o.daily_cumulative_quantity, o.average_price, l.bloomberg_code, l.abbreviated_stock_description, o.trade_date, l.currency
  • Join Filter: ((l.exchange_mic)::text = (ranked_view.exchange_mic)::text)
  • Rows Removed by Join Filter: 1
  • Buffers: shared hit=7978 read=19746
  • Output: o.leg, o.daily_cumulative_quantity, o.average_price, o.trade_date, o.oms_exchange_id, l.bloomberg_code, l.abbreviated_stock_description, l.currency, l.exchange_mic, l.exchange_id
  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=7953 read=19746
  • Output: o.leg, o.daily_cumulative_quantity, o.average_price, o.trade_date, o.oms_exchange_id, l.bloomberg_code, l.abbreviated_stock_description, l.currency, l.exchange_mic, l.exchange_id
  • Hash Cond: (l.instrument_id = o.instrument_id)
  • Buffers: shared hit=7953 read=19746
  • Worker 0: actual time=1033.536..1033.536 rows=0 loops=1
  • Buffers: shared hit=2667 read=6706
  • Worker 1: actual time=958.551..1033.719 rows=1 loops=1
  • Buffers: shared hit=2648 read=6249
  • 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.secur (...)
  • Filter: (((l.exchange_id)::text = 'NSI'::text) AND ((l.security_board)::text = 'MAIN'::text))
  • Rows Removed by Filter: 229184
  • Buffers: shared hit=480 read=19746
  • Worker 0: actual time=40.672..1015.011 rows=2409 loops=1
  • Buffers: shared hit=154 read=6706
  • Worker 1: actual time=41.833..1012.538 rows=2652 loops=1
  • Buffers: shared hit=135 read=6249
  • Output: o.leg, o.daily_cumulative_quantity, o.average_price, o.trade_date, o.instrument_id, o.oms_exchange_id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=7341
  • Worker 0: actual time=17.953..17.954 rows=1 loops=1
  • Buffers: shared hit=2447
  • Worker 1: actual time=20.619..20.619 rows=1 loops=1
  • Buffers: shared hit=2447
  • Output: o.leg, o.daily_cumulative_quantity, o.average_price, o.trade_date, o.instrument_id, o.oms_exchange_id
  • Filter: ((o.daily_cumulative_quantity > 0) AND ((o.oms_exchange_id)::text = 'NSE'::text) AND (o.client_reference = 111) AND ((o.order_category)::text = 'AGENCY'::text) AND ((o.order_rank)::text = 'SalesOrder'::te (...)
  • Rows Removed by Filter: 37198
  • Buffers: shared hit=7341
  • Worker 0: actual time=3.824..17.948 rows=1 loops=1
  • Buffers: shared hit=2447
  • Worker 1: actual time=4.436..20.610 rows=1 loops=1
  • Buffers: shared hit=2447
  • Output: ranked_view.uuid, ranked_view.source_system, ranked_view.ldm_version, ranked_view.model_name, ranked_view.exchange_id, ranked_view.market_id, ranked_view.exchange_name, ranked_view.exchangeid_mic, ranked_view.bloomberg_exc (...)
  • Filter: (((ranked_view.python_exchange_id)::text = 'NSE'::text) AND ((ranked_view.exchange_id)::text = 'NSI'::text) AND ((ranked_view.rank_col)::integer = 1))
  • Rows Removed by Filter: 1298
  • Buffers: shared hit=25
  • Output: NULL::character varying, NULL::character varying, NULL::integer, NULL::character varying, exchange_static.exchange_id, NULL::character varying, NULL::character varying, exchange_static.exchangeid_mic, NULL::character (...)
  • Buffers: shared hit=25
  • Output: exchange_static.exchangeid_mic, exchange_static.exchange_mic, exchange_static.modified_timestamp, exchange_static.exchange_id, exchange_static.python_exchange_id
  • Sort Key: exchange_static.exchangeid_mic, exchange_static.exchange_mic, exchange_static.modified_timestamp DESC
  • Sort Method: quicksort Memory: 150kB
  • Buffers: shared hit=25
  • Output: exchange_static.exchangeid_mic, exchange_static.exchange_mic, exchange_static.modified_timestamp, exchange_static.exchange_id, exchange_static.python_exchange_id
  • Buffers: shared hit=25
  • Output: CASE WHEN ((o_1.leg)::text = 'SellShort'::text) THEN 'Sell'::character varying ELSE o_1.leg END, o_1.daily_cumulative_quantity, o_1.average_price, l_1.bloomberg_code, l_1.abbreviated_stock_description, o_1.trade_date, l_1.curren (...)
  • Join Filter: ((l_1.exchange_mic)::text = (ranked_view_1.exchange_mic)::text)
  • Buffers: shared hit=16111 read=29567
  • Output: ranked_view_1.uuid, ranked_view_1.source_system, ranked_view_1.ldm_version, ranked_view_1.model_name, ranked_view_1.exchange_id, ranked_view_1.market_id, ranked_view_1.exchange_name, ranked_view_1.exchangeid_mic, ranked_vi (...)
  • Filter: (((ranked_view_1.python_exchange_id)::text = 'NSE'::text) AND ((ranked_view_1.exchange_id)::text = 'NSI'::text) AND ((ranked_view_1.rank_col)::integer = 1))
  • Rows Removed by Filter: 1298
  • Buffers: shared hit=25
  • Output: NULL::character varying, NULL::character varying, NULL::integer, NULL::character varying, exchange_static_1.exchange_id, NULL::character varying, NULL::character varying, exchange_static_1.exchangeid_mic, NULL::chara (...)
  • Buffers: shared hit=25
  • Output: exchange_static_1.exchangeid_mic, exchange_static_1.exchange_mic, exchange_static_1.modified_timestamp, exchange_static_1.exchange_id, exchange_static_1.python_exchange_id
  • Sort Key: exchange_static_1.exchangeid_mic, exchange_static_1.exchange_mic, exchange_static_1.modified_timestamp DESC
  • Sort Method: quicksort Memory: 150kB
  • Buffers: shared hit=25
  • Output: exchange_static_1.exchangeid_mic, exchange_static_1.exchange_mic, exchange_static_1.modified_timestamp, exchange_static_1.exchange_id, exchange_static_1.python_exchange_id
  • Buffers: shared hit=25
  • Output: o_1.leg, o_1.daily_cumulative_quantity, o_1.average_price, o_1.trade_date, o_1.oms_exchange_id, l_1.bloomberg_code, l_1.abbreviated_stock_description, l_1.currency, l_1.exchange_mic, l_1.exchange_id
  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=16086 read=29567
  • Output: o_1.leg, o_1.daily_cumulative_quantity, o_1.average_price, o_1.trade_date, o_1.oms_exchange_id, l_1.bloomberg_code, l_1.abbreviated_stock_description, l_1.currency, l_1.exchange_mic, l_1.exchange_id
  • Hash Cond: (l_1.instrument_id = o_1.instrument_id)
  • Buffers: shared hit=16086 read=29567
  • Worker 0: actual time=176.490..176.490 rows=0 loops=2
  • Buffers: shared hit=5680 read=4342
  • Worker 1: actual time=176.741..176.741 rows=0 loops=2
  • Buffers: shared hit=6603 read=3487
  • Output: l_1.reuters_identification_code, l_1.bloomberg_code, l_1.instrument_id, l_1.listing_id, l_1.abbreviated_stock_description, l_1.stock_description, l_1.exchange_id, l_1.currency, l_1.local_security_code, l_1.secu (...)
  • Filter: (((l_1.exchange_id)::text = 'NSI'::text) AND ((l_1.security_board)::text = 'MAIN'::text))
  • Rows Removed by Filter: 118763
  • Buffers: shared hit=768 read=20231
  • Worker 0: actual time=1.007..1.007 rows=1 loops=2
  • Buffers: shared hit=82 read=52
  • Worker 1: actual time=1.353..1.353 rows=1 loops=2
  • Buffers: shared hit=102 read=100
  • Output: o_1.leg, o_1.daily_cumulative_quantity, o_1.average_price, o_1.trade_date, o_1.instrument_id, o_1.oms_exchange_id
  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=15054 read=9336
  • Worker 0: actual time=175.291..175.291 rows=0 loops=2
  • Buffers: shared hit=5466 read=4290
  • Worker 1: actual time=175.206..175.206 rows=0 loops=2
  • Buffers: shared hit=6369 read=3387
  • Output: o_1.leg, o_1.daily_cumulative_quantity, o_1.average_price, o_1.trade_date, o_1.instrument_id, o_1.oms_exchange_id
  • Filter: ((o_1.sales_orders_group_id IS NULL) AND (o_1.daily_cumulative_quantity > 0) AND ((o_1.oms_exchange_id)::text = 'NSE'::text) AND (o_1.client_reference = 111) AND ((o_1.order_category)::text = 'AGENCY'::te (...)
  • Rows Removed by Filter: 79870
  • Buffers: shared hit=15054 read=9336
  • Worker 0: actual time=175.290..175.290 rows=0 loops=2
  • Buffers: shared hit=5466 read=4290
  • Worker 1: actual time=175.206..175.206 rows=0 loops=2
  • Buffers: shared hit=6369 read=3387
2. 504.027 504.027 ↑ 2.0 1 1 / 3

Sort (cost=63,014.56..63,014.56 rows=2 width=340) (actual time=1,512.082..1,512.082 rows=1 loops=1)

3. 504.023 504.023 ↑ 2.0 1 1 / 3

Append (cost=4,854.22..63,014.55 rows=2 width=340) (actual time=1,051.635..1,512.069 rows=1 loops=1)

4. 350.643 350.643 ↑ 1.0 1 1 / 3

Nested Loop (cost=4,854.22..29,526.34 rows=1 width=83) (actual time=1,051.634..1,051.929 rows=1 loops=1)

5. 349.402 349.402 ↑ 1.0 1 1 / 3

Gather (cost=4,748.98..29,365.84 rows=1 width=67) (actual time=1,048.108..1,048.206 rows=1 loops=1)

6. 1,037.613 1,037.613 ↓ 0.0 0 3 / 3

Hash Join (cost=3,748.98..28,365.74 rows=1 width=67) (actual time=1,012.556..1,037.613 rows=0 loops=3)

7. 1,018.725 1,018.725 ↑ 3.3 3,005 3 / 3

Parallel Seq Scan on reference_brain.latest_listing l (cost=0.00..24,579.55 rows=9,921 width=47) (actual time=45.074..1,018.725 rows=3,005 loops=3)

8. 18.357 18.357 ↑ 1.0 1 3 / 3

Hash (cost=3,748.97..3,748.97 rows=1 width=28) (actual time=18.357..18.357 rows=1 loops=3)

9. 18.351 18.351 ↑ 1.0 1 3 / 3

Seq Scan on trade_brain.latest_orders_1month_cobra o (cost=0.00..3,748.97 rows=1 width=28) (actual time=3.918..18.351 rows=1 loops=3)

10. 1.270 1.270 ↓ 2.0 2 1 / 3

Subquery Scan on ranked_view (cost=105.24..160.49 rows=1 width=12) (actual time=3.518..3.811 rows=2 loops=1)

11. 1.240 1.240 ↑ 1.0 1,300 1 / 3

WindowAgg (cost=105.24..134.49 rows=1,300 width=453) (actual time=2.950..3.719 rows=1,300 loops=1)

12. 1.005 1.005 ↑ 1.0 1,300 1 / 3

Sort (cost=105.24..108.49 rows=1,300 width=25) (actual time=2.941..3.015 rows=1,300 loops=1)

13. 0.145 0.145 ↑ 1.0 1,300 1 / 3

Seq Scan on reference_brain_data.exchange_static (cost=0.00..38.00 rows=1,300 width=25) (actual time=0.013..0.434 rows=1,300 loops=1)

14. 153.379 153.379 ↓ 0.0 0 1 / 3

Nested Loop (cost=8,778.74..33,488.18 rows=1 width=83) (actual time=460.138..460.138 rows=0 loops=1)

15. 1.185 1.185 ↓ 2.0 2 1 / 3

Subquery Scan on ranked_view_1 (cost=105.24..160.49 rows=1 width=12) (actual time=3.268..3.556 rows=2 loops=1)

16. 1.160 1.160 ↑ 1.0 1,300 1 / 3

WindowAgg (cost=105.24..134.49 rows=1,300 width=453) (actual time=2.692..3.480 rows=1,300 loops=1)

17. 0.921 0.921 ↑ 1.0 1,300 1 / 3

Sort (cost=105.24..108.49 rows=1,300 width=25) (actual time=2.688..2.763 rows=1,300 loops=1)

18. 0.077 0.077 ↑ 1.0 1,300 1 / 3

Seq Scan on reference_brain_data.exchange_static exchange_static_1 (cost=0.00..38.00 rows=1,300 width=25) (actual time=0.005..0.231 rows=1,300 loops=1)

19. 152.207 152.207 ↓ 0.0 0 2 / 3

Gather (cost=8,673.50..33,327.67 rows=2 width=67) (actual time=228.289..228.311 rows=0 loops=2)

20. 386.904 386.904 ↓ 0.0 0 6 / 3

Hash Join (cost=7,673.50..32,327.47 rows=1 width=67) (actual time=193.452..193.452 rows=0 loops=6)

21. 55.714 55.714 ↑ 6.6 1,501 6 / 3

Parallel Seq Scan on reference_brain.latest_listing l_1 (cost=0.00..24,579.55 rows=9,921 width=47) (actual time=2.620..27.857 rows=1,501 loops=6)

22. 330.708 330.708 ↓ 0.0 0 5 / 3

Hash (cost=7,673.45..7,673.45 rows=4 width=28) (actual time=198.425..198.425 rows=0 loops=5)

23. 330.707 330.707 ↓ 0.0 0 5 / 3

Seq Scan on trade_brain.latest_orders_1month_python o_1 (cost=0.00..7,673.45 rows=4 width=28) (actual time=198.424..198.424 rows=0 loops=5)

Planning time : 1.283 ms