explain.depesz.com

PostgreSQL's explain analyze made readable

Result: efYQ

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Nested Loop (cost=16.54..830,703,221.76 rows=1 width=65) (actual rows= loops=)

  • Join Filter: (m.fill_id = f.fill_id)
2. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.69..830.56 rows=1 width=37) (actual rows= loops=)

  • Join Filter: ((h.report_dt = hss.report_dt) AND (h.stock_name = hss.cc_id))
3. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.13..822.26 rows=1 width=46) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Index Scan using historical_tradeable_stock_report_dt_stock_name_idx on historical_tradeable_stock h (cost=0.56..8.58 rows=1 width=32) (actual rows= loops=)

  • Index Cond: ((report_dt >= '2018-12-19'::date) AND (report_dt <= '2018-12-19'::date))
5. 0.000 0.000 ↓ 0.0

Index Scan using fill_metrics_time_port_sym_idx on fill_metrics m (cost=0.56..813.67 rows=1 width=20) (actual rows= loops=)

  • Index Cond: (((portfolio)::text = h.portfolio) AND ((symbol)::text = h.stock_name))
  • Filter: (h.report_dt = date("timestamp"))
6. 0.000 0.000 ↓ 0.0

Index Scan using historical_symbol_pkey on historical_symbol hss (cost=0.56..8.29 rows=1 width=18) (actual rows= loops=)

  • Index Cond: ((report_dt = date(m."timestamp")) AND (cc_id = (m.symbol)::text))
7. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=14.85..830,424,747.42 rows=22,211,488 width=201) (actual rows= loops=)

  • Hash Cond: ((ex.regulating_country = r.country) AND (f.asset_class = r.asset_class))
  • Join Filter: ((f."timestamp" >= r.valid_time_start) AND (f."timestamp" <= r.valid_time_end))
8. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=13.25..829,019,196.76 rows=22,211,488 width=169) (actual rows= loops=)

  • Hash Cond: ((ex.regulating_country = b.country) AND (bm.execution_type = b.execution_type) AND (f.broker = b.broker) AND (f.asset_class = b.asset_class))
  • Join Filter: ((f."timestamp" >= b.valid_time_start) AND (f."timestamp" <= b.valid_time_end) AND ((f.exchange = b.exchange) OR (f.exchange IS NULL) OR ((NOT (SubPlan 1)) AND (b.exchange = 'NA'::text))))
9. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=9.63..826,315,769.17 rows=22,211,488 width=157) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=9.36..1,254,177.50 rows=22,211,488 width=93) (actual rows= loops=)

  • Hash Cond: (f.exchange_interface = bm.sendercompid)
11. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=7.28..990,178.70 rows=22,211,488 width=70) (actual rows= loops=)

  • Hash Cond: (f.exchange = ex.description)
12. 0.000 0.000 ↓ 0.0

Seq Scan on fill_metrics f (cost=0.00..690,505.88 rows=22,211,488 width=65) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Hash (cost=4.90..4.90 rows=190 width=12) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Seq Scan on exchange ex (cost=0.00..4.90 rows=190 width=12) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Hash (cost=1.48..1.48 rows=48 width=41) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Seq Scan on broker_map bm (cost=0.00..1.48 rows=48 width=41) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Index Scan using exchange_fees_pkey on exchange_fees e (cost=0.28..37.14 rows=1 width=114) (actual rows= loops=)

  • Index Cond: ((f."timestamp" >= valid_time_start) AND (f."timestamp" <= valid_time_end) AND (asset_class = f.asset_class) AND (broker = f.broker) AND (exchange = f.exchange))
  • Filter: (((f.liquidity_flags = liquidity_flags) OR ((f.liquidity_flags IS NULL) AND (liquidity_flags = 'NA'::text))) AND ((f.strategy = strategy) OR (f.strategy IS NULL) OR ((NOT (SubPlan 3)) AND (strategy = 'NA'::text))) AND ((f. (...)
18.          

SubPlan (forIndex Scan)

19. 0.000 0.000 ↓ 0.0

Index Only Scan using exchange_fees_pkey on exchange_fees ef_1 (cost=0.28..19.53 rows=1 width=3) (actual rows= loops=)

  • Index Cond: ((valid_time_start <= f."timestamp") AND (valid_time_end >= f."timestamp") AND (asset_class = f.asset_class) AND (broker = f.broker) AND (exchange = f.exchange))
  • Filter: ((f.liquidity_flags = liquidity_flags) OR ((f.liquidity_flags IS NULL) AND (liquidity_flags = 'NA'::text)))
20. 0.000 0.000 ↓ 0.0

Index Only Scan using exchange_fees_pkey on exchange_fees ef (cost=0.28..19.53 rows=1 width=3) (actual rows= loops=)

  • Index Cond: ((valid_time_start <= f."timestamp") AND (valid_time_end >= f."timestamp") AND (asset_class = f.asset_class) AND (broker = f.broker) AND (exchange = f.exchange))
  • Filter: ((f.liquidity_flags = liquidity_flags) OR ((f.liquidity_flags IS NULL) AND (liquidity_flags = 'NA'::text)))
21. 0.000 0.000 ↓ 0.0

Hash (cost=2.54..2.54 rows=54 width=92) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Seq Scan on broker_commissions b (cost=0.00..2.54 rows=54 width=92) (actual rows= loops=)

23.          

SubPlan (forHash Left Join)

24. 0.000 0.000 ↓ 0.0

Seq Scan on broker_commissions bc (cost=0.00..3.35 rows=1 width=3) (actual rows= loops=)

  • Filter: ((f."timestamp" >= valid_time_start) AND (f."timestamp" <= valid_time_end) AND (broker = f.broker) AND (bm.execution_type = execution_type) AND (asset_class = f.asset_class) AND (ex.regulating_country = country))
25. 0.000 0.000 ↓ 0.0

Hash (cost=1.24..1.24 rows=24 width=79) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Seq Scan on regulator_fees r (cost=0.00..1.24 rows=24 width=79) (actual rows= loops=)