explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6TdL

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 5,145.777 ↓ 0.0 0 1

Subquery Scan on t (cost=2,198,286.23..2,198,286.30 rows=1 width=336) (actual time=5,145.777..5,145.777 rows=0 loops=1)

  • Filter: (t.top <= 20)
2.          

CTE orders

3. 0.002 5,145.729 ↓ 0.0 0 1

Append (cost=435,502.08..2,198,269.19 rows=2 width=83) (actual time=5,145.729..5,145.729 rows=0 loops=1)

4. 0.000 0.003 ↓ 0.0 0 1

Nested Loop (cost=435,502.08..1,099,134.59 rows=1 width=83) (actual time=0.003..0.003 rows=0 loops=1)

  • Join Filter: ((l.instrument_id = o_1.instrument_id) AND ((l.local_security_code)::text = (o_1.local_security_code)::text) AND ((ranked_view.python_exchange_id)::text = (o_1.oms_exchange_id)::text))
5. 0.001 0.003 ↓ 0.0 0 1

Hash Join (cost=20.75..36.19 rows=1 width=76) (actual time=0.003..0.003 rows=0 loops=1)

  • Hash Cond: (((l.exchange_mic)::text = (ranked_view.exchange_mic)::text) AND ((l.exchange_id)::text = (ranked_view.exchange_id)::text))
6. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on latest_listing l (cost=0.00..13.10 rows=310 width=52) (actual time=0.002..0.002 rows=0 loops=1)

7. 0.000 0.000 ↓ 0.0 0

Hash (cost=20.74..20.74 rows=1 width=96) (never executed)

8. 0.000 0.000 ↓ 0.0 0

Subquery Scan on ranked_view (cost=15.86..20.74 rows=1 width=96) (never executed)

  • Filter: ((ranked_view.rank_col)::integer = 1)
9. 0.000 0.000 ↓ 0.0 0

WindowAgg (cost=15.86..18.79 rows=130 width=564) (never executed)

10. 0.000 0.000 ↓ 0.0 0

Sort (cost=15.86..16.19 rows=130 width=136) (never executed)

  • Sort Key: exchange_static.exchangeid_mic, exchange_static.exchange_mic, exchange_static.modified_timestamp DESC
11. 0.000 0.000 ↓ 0.0 0

Seq Scan on exchange_static (cost=0.00..11.30 rows=130 width=136) (never executed)

12. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on latest_orders o_1 (cost=435,481.33..1,099,098.38 rows=1 width=33) (never executed)

  • Recheck Cond: (((trade_date >= (CURRENT_DATE - 30)) AND (trade_date < CURRENT_DATE) AND ((source_system)::text = 'COBRA'::text)) OR ((trade_date >= (CURRENT_DATE - 30)) AND (trade_date < CURRENT_DATE) AND ((source_system)::text = 'PYT (...)
  • Filter: (((oms_exchange_id)::text <> 'NSE'::text) AND (daily_cumulative_quantity > 0) AND (client_reference = 111) AND ((order_category)::text = 'AGENCY'::text) AND ((order_rank)::text = 'SalesOrder'::text) AND (trade_date < CURRENT_D (...)
13. 0.000 0.000 ↓ 0.0 0

BitmapOr (cost=435,481.33..435,481.33 rows=192,913 width=0) (never executed)

14. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on latest_orders_td_ss_idx (cost=0.00..217,740.66 rows=59,656 width=0) (never executed)

  • Index Cond: ((trade_date >= (CURRENT_DATE - 30)) AND (trade_date < CURRENT_DATE) AND ((source_system)::text = 'COBRA'::text))
15. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on latest_orders_td_ss_idx (cost=0.00..217,740.66 rows=133,257 width=0) (never executed)

  • Index Cond: ((trade_date >= (CURRENT_DATE - 30)) AND (trade_date < CURRENT_DATE) AND ((source_system)::text = 'PYTHON'::text))
16. 0.001 5,145.724 ↓ 0.0 0 1

Nested Loop (cost=435,497.19..1,099,134.57 rows=1 width=83) (actual time=5,145.724..5,145.724 rows=0 loops=1)

  • Join Filter: ((l_1.exchange_mic)::text = (ranked_view_1.exchange_mic)::text)
17. 0.026 5,145.723 ↓ 0.0 0 1

Nested Loop (cost=435,481.33..1,099,113.17 rows=1 width=67) (actual time=5,145.723..5,145.723 rows=0 loops=1)

  • Join Filter: (o_2.instrument_id = l_1.instrument_id)
18. 3,623.194 5,145.689 ↓ 4.0 4 1

Bitmap Heap Scan on latest_orders o_2 (cost=435,481.33..1,099,098.38 rows=1 width=28) (actual time=2,245.728..5,145.689 rows=4 loops=1)

  • Recheck Cond: (((trade_date >= (CURRENT_DATE - 30)) AND (trade_date < CURRENT_DATE) AND ((source_system)::text = 'COBRA'::text)) OR ((trade_date >= (CURRENT_DATE - 30)) AND (trade_date < CURRENT_DATE) AND ((source_system)::text (...)
  • Filter: ((daily_cumulative_quantity > 0) AND ((oms_exchange_id)::text = 'NSE'::text) AND (client_reference = 111) AND ((order_category)::text = 'AGENCY'::text) AND ((order_rank)::text = 'SalesOrder'::text) AND (trade_date < CURR (...)
  • Rows Removed by Filter: 145,314
  • Heap Blocks: exact=33,985
19. 0.005 1,522.495 ↓ 0.0 0 1

BitmapOr (cost=435,481.33..435,481.33 rows=192,913 width=0) (actual time=1,522.495..1,522.495 rows=0 loops=1)

20. 1,123.203 1,123.203 ↑ 1.3 45,759 1

Bitmap Index Scan on latest_orders_td_ss_idx (cost=0.00..217,740.66 rows=59,656 width=0) (actual time=1,123.203..1,123.203 rows=45,759 loops=1)

  • Index Cond: ((trade_date >= (CURRENT_DATE - 30)) AND (trade_date < CURRENT_DATE) AND ((source_system)::text = 'COBRA'::text))
21. 399.287 399.287 ↑ 1.3 99,559 1

Bitmap Index Scan on latest_orders_td_ss_idx (cost=0.00..217,740.66 rows=133,257 width=0) (actual time=399.287..399.287 rows=99,559 loops=1)

  • Index Cond: ((trade_date >= (CURRENT_DATE - 30)) AND (trade_date < CURRENT_DATE) AND ((source_system)::text = 'PYTHON'::text))
22. 0.008 0.008 ↓ 0.0 0 4

Seq Scan on latest_listing l_1 (cost=0.00..14.65 rows=11 width=47) (actual time=0.002..0.002 rows=0 loops=4)

  • Filter: (((exchange_id)::text = 'NSI'::text) AND ((security_board)::text = 'MAIN'::text))
23. 0.000 0.000 ↓ 0.0 0

Subquery Scan on ranked_view_1 (cost=15.86..21.39 rows=1 width=96) (never executed)

  • 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))
24. 0.000 0.000 ↓ 0.0 0

WindowAgg (cost=15.86..18.79 rows=130 width=564) (never executed)

25. 0.000 0.000 ↓ 0.0 0

Sort (cost=15.86..16.19 rows=130 width=136) (never executed)

  • Sort Key: exchange_static_1.exchangeid_mic, exchange_static_1.exchange_mic, exchange_static_1.modified_timestamp DESC
26. 0.000 0.000 ↓ 0.0 0

Seq Scan on exchange_static exchange_static_1 (cost=0.00..11.30 rows=130 width=136) (never executed)

27. 0.005 5,145.776 ↓ 0.0 0 1

WindowAgg (cost=17.04..17.09 rows=2 width=344) (actual time=5,145.776..5,145.776 rows=0 loops=1)

28. 0.022 5,145.771 ↓ 0.0 0 1

Sort (cost=17.04..17.05 rows=2 width=336) (actual time=5,145.771..5,145.771 rows=0 loops=1)

  • Sort Key: o.leg, (sum((((o.daily_cumulative_quantity)::double precision * o.average_price) * fx.previous_close_rate))) DESC
  • Sort Method: quicksort Memory: 25kB
29. 0.003 5,145.749 ↓ 0.0 0 1

GroupAggregate (cost=16.94..17.03 rows=2 width=336) (actual time=5,145.749..5,145.749 rows=0 loops=1)

  • Group Key: o.leg, o.bloomberg_code, o.abbreviated_stock_description
30. 0.013 5,145.746 ↓ 0.0 0 1

Sort (cost=16.94..16.95 rows=2 width=332) (actual time=5,145.746..5,145.746 rows=0 loops=1)

  • Sort Key: o.leg, o.bloomberg_code, o.abbreviated_stock_description
  • Sort Method: quicksort Memory: 25kB
31. 0.001 5,145.733 ↓ 0.0 0 1

Nested Loop (cost=0.42..16.93 rows=2 width=332) (actual time=5,145.733..5,145.733 rows=0 loops=1)

32. 5,145.732 5,145.732 ↓ 0.0 0 1

CTE Scan on orders o (cost=0.00..0.04 rows=2 width=340) (actual time=5,145.732..5,145.732 rows=0 loops=1)

33. 0.000 0.000 ↓ 0.0 0

Index Scan using ltst_for_exch_clsg_rte_idx_src_ccy_ref_ccy_rte_typ_curr_clsg_dt on latest_foreign_exchange_closing_rate fx (cost=0.42..8.45 rows=1 width=16) (never executed)

  • Index Cond: (((source_currency)::text = (o.currency)::text) AND ((reference_currency)::text = 'USD'::text) AND ((rate_type)::text = 'SPOT'::text) AND (currency_closing_date = o.trade_date))
Planning time : 32.796 ms
Execution time : 5,146.228 ms