explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pxlJ4

Settings
# exclusive inclusive rows x rows loops node
1. 229.349 1,815.848 ↑ 4.3 74,943 1

Merge Left Join (cost=70,027.56..72,918.63 rows=319,860 width=159) (actual time=1,520.826..1,815.848 rows=74,943 loops=1)

  • Merge Cond: ((msci_index_definitions.msci_index_code = msci_country_weights.msci_index_code) AND (msci_index_components.iso_country_symbol_next_day = msci_country_weights.iso_country_code))
  • Join Filter: (msci_index_definitions.msci_index_headers_id = msci_country_weights.msci_index_headers_id)
  • Filter: ((msci_country_weights.iso_country_code IS NOT NULL) OR (msci_index_definitions.iso_country_symbol = msci_index_components.iso_country_symbol))
  • Rows Removed by Filter: 408031
  • Buffers: shared hit=823
2. 1,084.015 1,568.505 ↓ 1.5 482,974 1

Sort (cost=68,660.89..69,460.54 rows=319,860 width=174) (actual time=1,505.510..1,568.505 rows=482,974 loops=1)

  • Sort Key: msci_index_definitions.msci_index_code, msci_index_components.iso_country_symbol_next_day
  • Sort Method: quicksort Memory: 140578kB
  • Buffers: shared hit=514
3. 415.508 484.490 ↓ 1.5 482,974 1

Nested Loop (cost=0.57..39,414.36 rows=319,860 width=174) (actual time=0.457..484.490 rows=482,974 loops=1)

  • Join Filter: ((((msci_index_definitions.dm_flag = msci_index_components.dm_universe_flag) AND (msci_index_definitions.em_flag = msci_index_components.em_universe_flag)) OR (msci_index_definitions.iso_country_symbol = msci_index_components.iso_country_symbol)) AND ((msci_index_definitions.large_cap_flag = msci_index_components.family_large_flag) OR (msci_index_definitions.large_cap_flag = msci_index_components.family_large_flag_next_day) OR (msci_index_definitions.mid_cap_flag = msci_index_components.family_mid_flag) OR (msci_index_definitions.mid_cap_flag = msci_index_components.family_mid_flag_next_day)))
  • Rows Removed by Join Filter: 809231
  • Buffers: shared hit=507
4. 2.502 2.502 ↑ 1.0 4,155 1

Index Scan using msci_index_components_headers_id_idx on msci_index_components (cost=0.29..390.00 rows=4,155 width=105) (actual time=0.061..2.502 rows=4,155 loops=1)

  • Index Cond: (msci_index_headers_id = 101)
  • Buffers: shared hit=314
5. 63.728 66.480 ↑ 1.0 311 4,155

Materialize (cost=0.28..258.99 rows=311 width=77) (actual time=0.000..0.016 rows=311 loops=4,155)

  • Buffers: shared hit=193
6. 2.752 2.752 ↑ 1.0 311 1

Index Scan using msci_index_definitions_headers_id_idx on msci_index_definitions (cost=0.28..257.44 rows=311 width=77) (actual time=0.066..2.752 rows=311 loops=1)

  • Index Cond: (msci_index_headers_id = 101)
  • Filter: (real_time_ticker IS NOT NULL)
  • Rows Removed by Filter: 2892
  • Buffers: shared hit=193
7. 14.516 17.994 ↓ 6.2 75,433 1

Sort (cost=1,366.67..1,397.06 rows=12,155 width=15) (actual time=14.677..17.994 rows=75,433 loops=1)

  • Sort Key: msci_country_weights.msci_index_code, msci_country_weights.iso_country_code
  • Sort Method: quicksort Memory: 1334kB
  • Buffers: shared hit=309
8. 3.478 3.478 ↑ 1.0 12,155 1

Index Scan using msci_country_weights_headers_id_idx on msci_country_weights (cost=0.29..542.00 rows=12,155 width=15) (actual time=0.036..3.478 rows=12,155 loops=1)

  • Index Cond: (msci_index_headers_id = 101)
  • Buffers: shared hit=309
Planning time : 2.862 ms
Execution time : 1,829.925 ms