explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LAXP

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.000 14,170.003 ↑ 1.0 1 1

Nested Loop (cost=4,426.78..303,980.06 rows=1 width=83) (actual time=14,161.045..14,170.003 rows=1 loops=1)

  • Join Filter: ((o.instrument_id = l.instrument_id) AND ((ranked_view.exchange_mic)::text = (l.exchange_mic)::text))
  • Rows Removed by Join Filter: 18031
  • Filter: (((exchange_id)::text = 'NSI'::text) AND ((security_board)::text = 'MAIN'::text))
  • Rows Removed by Filter: 687552
  • Recheck Cond: (((source_system)::text = 'COBRA'::text) OR ((source_system)::text = 'PYTHON'::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 < CURRENT_DATE (...)
  • Rows Removed by Filter: 117097
  • Heap Blocks: exact=21769
  • Index Cond: ((source_system)::text = 'COBRA'::text)
  • Index Cond: ((source_system)::text = 'PYTHON'::text)
  • 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
  • Sort Key: exchange_static.exchangeid_mic, exchange_static.exchange_mic, exchange_static.modified_timestamp DESC
  • Sort Method: quicksort Memory: 150kB
2. 144.031 144.031 ↑ 2.6 9,016 1

Seq Scan on latest_listing l (cost=0.00..30,674.52 rows=23,810 width=47) (actual time=6.103..144.031 rows=9,016 loops=1)

3. 14,019.880 14,019.880 ↓ 2.0 2 9,016

Materialize (cost=4,426.78..272,888.87 rows=1 width=32) (actual time=0.876..1.555 rows=2 loops=9,016)

4. 14,023.090 14,023.090 ↓ 2.0 2 1

Nested Loop (cost=4,426.78..272,888.86 rows=1 width=32) (actual time=7,898.133..14,023.090 rows=2 loops=1)

5. 14,019.783 14,019.783 ↑ 1.0 1 1

Bitmap Heap Scan on latest_orders_1month_table_2 o (cost=4,321.55..272,728.37 rows=1 width=28) (actual time=7,895.059..14,019.783 rows=1 loops=1)

6. 2,071.965 2,071.965 ↓ 0.0 0 1

BitmapOr (cost=4,321.55..4,321.55 rows=125,924 width=0) (actual time=2,071.965..2,071.965 rows=0 loops=1)

7. 741.048 741.048 ↑ 1.1 37,187 1

Bitmap Index Scan on latest_orders_1month_table_2_source_system_parent_order_id_idx (cost=0.00..1,372.36 rows=39,973 width=0) (actual time=741.048..741.048 rows=37,187 loops=1)

8. 1,330.915 1,330.915 ↑ 1.1 79,911 1

Bitmap Index Scan on latest_orders_1month_table_2_source_system_parent_order_id_idx (cost=0.00..2,949.19 rows=85,951 width=0) (actual time=1,330.915..1,330.915 rows=79,911 loops=1)

9. 3.299 3.299 ↓ 2.0 2 1

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

10. 3.236 3.236 ↑ 1.0 1,300 1

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

11. 2.607 2.607 ↑ 1.0 1,300 1

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

12. 0.328 0.328 ↑ 1.0 1,300 1

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

Planning time : 20.096 ms