explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Xl2P

Settings
# exclusive inclusive rows x rows loops node
1. 74.166 2,342,301.152 ↓ 1,916.4 15,331 1

Sort (cost=17,514.04..17,514.06 rows=8 width=358) (actual time=2,342,300.214..2,342,301.152 rows=15,331 loops=1)

  • Sort Key: evar.event_id, evar.event_variable_value_id
  • Sort Method: quicksort Memory: 2450kB
2. 43.846 2,342,226.986 ↓ 1,916.4 15,331 1

Nested Loop (cost=0.00..17,513.92 rows=8 width=358) (actual time=691.749..2,342,226.986 rows=15,331 loops=1)

3. 114.042 2,342,091.154 ↓ 1,916.4 15,331 1

Nested Loop (cost=0.00..17,511.68 rows=8 width=347) (actual time=691.741..2,342,091.154 rows=15,331 loops=1)

4. 136.328 2,341,716.637 ↓ 2,170.6 17,365 1

Nested Loop (cost=0.00..17,508.28 rows=8 width=351) (actual time=691.719..2,341,716.637 rows=17,365 loops=1)

5. 232.383 1,148.853 ↓ 3,202.0 25,616 1

Nested Loop (cost=0.00..15,278.12 rows=8 width=345) (actual time=611.514..1,148.853 rows=25,616 loops=1)

  • Join Filter: (evar.event_variable_name_id = evn.event_variable_name_id)
  • Rows Removed by Join Filter: 729724
6. 17.638 790.580 ↓ 100.1 62,945 1

Append (cost=0.00..15,127.27 rows=629 width=330) (actual time=611.088..790.580 rows=62,945 loops=1)

7. 0.021 0.021 ↓ 0.0 0 1

Index Scan using evf_idx_msgutc on event_variable_fact evar (cost=0.00..8.31 rows=1 width=30) (actual time=0.021..0.021 rows=0 loops=1)

  • Index Cond: ((message_utc >= '2019-10-06 09:08:29+00'::timestamp with time zone) AND (message_utc <= '2019-10-08 15:08:29+00'::timestamp with time zone))
  • Filter: (eg_id = ANY ('{4020144,4020150,4020157,4020213,4020216,4020222,4020263,4020297,4020328,4020331,4020333,4020343,4020346,4020352,4020359,4020380,4020383,4020389,4020408,4020422,4020424,4020425,4020431,4020433,4020436,4020437,4020440,4020445,4020452,40
8. 0.038 268.881 ↓ 0.0 0 1

Bitmap Heap Scan on event_variable_fact_p2019w40 evar (cost=7,201.65..8,241.92 rows=262 width=37) (actual time=268.881..268.881 rows=0 loops=1)

  • Recheck Cond: ((message_utc >= '2019-10-06 09:08:29+00'::timestamp with time zone) AND (message_utc <= '2019-10-08 15:08:29+00'::timestamp with time zone) AND (eg_id = ANY ('{4020144,4020150,4020157,4020213,4020216,4020222,4020263,4020297,4020328,4020331,402
9. 2.595 268.843 ↓ 0.0 0 1

BitmapAnd (cost=7,201.65..7,201.65 rows=262 width=0) (actual time=268.843..268.843 rows=0 loops=1)

10. 266.174 266.174 ↓ 97.2 1,501,000 1

Bitmap Index Scan on event_variable_fact_p2019w40_message_utc_aircraft_id_etd_id_idx (cost=0.00..627.68 rows=15,440 width=0) (actual time=266.174..266.174 rows=1,501,000 loops=1)

  • Index Cond: ((message_utc >= '2019-10-06 09:08:29+00'::timestamp with time zone) AND (message_utc <= '2019-10-08 15:08:29+00'::timestamp with time zone))
11. 0.074 0.074 ↓ 0.0 0 1

Bitmap Index Scan on event_variable_fact_p2019w40_eg_id_idx (cost=0.00..6,573.59 rows=281,431 width=0) (actual time=0.074..0.074 rows=0 loops=1)

  • Index Cond: (eg_id = ANY ('{4020144,4020150,4020157,4020213,4020216,4020222,4020263,4020297,4020328,4020331,4020333,4020343,4020346,4020352,4020359,4020380,4020383,4020389,4020408,4020422,4020424,4020425,4020431,4020433,4020436,4020437,4020440,40
12. 162.937 504.040 ↓ 172.0 62,945 1

Bitmap Heap Scan on event_variable_fact_p2019w41 evar (cost=5,505.24..6,877.04 rows=366 width=540) (actual time=342.184..504.040 rows=62,945 loops=1)

  • Recheck Cond: ((message_utc >= '2019-10-06 09:08:29+00'::timestamp with time zone) AND (message_utc <= '2019-10-08 15:08:29+00'::timestamp with time zone) AND (eg_id = ANY ('{4020144,4020150,4020157,4020213,4020216,4020222,4020263,4020297,4020328,4020331,402
13. 5.728 341.103 ↓ 0.0 0 1

BitmapAnd (cost=5,505.24..5,505.24 rows=366 width=0) (actual time=341.103..341.103 rows=0 loops=1)

14. 326.898 326.898 ↓ 951.5 2,111,437 1

Bitmap Index Scan on event_variable_fact_p2019w41_message_utc_aircraft_id_etd_id_idx (cost=0.00..311.02 rows=2,219 width=0) (actual time=326.898..326.898 rows=2,111,437 loops=1)

  • Index Cond: ((message_utc >= '2019-10-06 09:08:29+00'::timestamp with time zone) AND (message_utc <= '2019-10-08 15:08:29+00'::timestamp with time zone))
15. 8.477 8.477 ↑ 1.1 68,138 1

Bitmap Index Scan on event_variable_fact_p2019w41_eg_id_idx (cost=0.00..5,193.80 rows=73,212 width=0) (actual time=8.477..8.477 rows=68,138 loops=1)

  • Index Cond: (eg_id = ANY ('{4020144,4020150,4020157,4020213,4020216,4020222,4020263,4020297,4020328,4020331,4020333,4020343,4020346,4020352,4020359,4020380,4020383,4020389,4020408,4020422,4020424,4020425,4020431,4020433,4020436,4020437,4020440,40
16. 125.520 125.890 ↑ 1.0 12 62,945

Materialize (cost=0.00..37.66 rows=12 width=23) (actual time=0.000..0.002 rows=12 loops=62,945)

17. 0.370 0.370 ↑ 1.0 12 1

Seq Scan on event_variable_name evn (cost=0.00..37.60 rows=12 width=23) (actual time=0.015..0.370 rows=12 loops=1)

  • Filter: ((evn_code)::text = ANY ('{latitude_decimal,longitude_decimal,flight_number,flight_number_iata,flight_number_icao,wind_dir_deg,wind_speed_kts,icing_status_code,heater_status_code,peak_edr,display_alt_ft,display_alt_type}'::text[]))
  • Rows Removed by Filter: 892
18. 333.008 2,340,431.456 ↑ 31.0 1 25,616

Append (cost=0.00..278.46 rows=31 width=14) (actual time=81.917..91.366 rows=1 loops=25,616)

19. 7,505.488 7,505.488 ↓ 0.0 0 25,616

Seq Scan on qc_grade_fact qc (cost=0.00..72.87 rows=1 width=14) (actual time=0.293..0.293 rows=0 loops=25,616)

  • Filter: ((message_utc >= '2019-10-06 09:08:29+00'::timestamp with time zone) AND (message_utc <= '2019-10-08 15:08:29+00'::timestamp with time zone) AND (evar.event_variable_value_id = event_variable_value_id))
  • Rows Removed by Filter: 1878
20. 307.392 307.392 ↓ 0.0 0 25,616

Index Scan using qc_grade_fact_p2019w40_pkey on qc_grade_fact_p2019w40 qc (cost=0.00..10.19 rows=1 width=14) (actual time=0.012..0.012 rows=0 loops=25,616)

  • Index Cond: (event_variable_value_id = evar.event_variable_value_id)
  • Filter: ((message_utc >= '2019-10-06 09:08:29+00'::timestamp with time zone) AND (message_utc <= '2019-10-08 15:08:29+00'::timestamp with time zone))
21. 2,332,285.568 2,332,285.568 ↑ 29.0 1 25,616

Index Scan using qc_grade_fact_p2019w41_message_utc_aircraft_id_etd_id_event_idx on qc_grade_fact_p2019w41 qc (cost=0.00..195.41 rows=29 width=14) (actual time=81.601..91.048 rows=1 loops=25,616)

  • Index Cond: ((message_utc >= '2019-10-06 09:08:29+00'::timestamp with time zone) AND (message_utc <= '2019-10-08 15:08:29+00'::timestamp with time zone) AND (event_variable_value_id = evar.event_variable_value_id))
22. 260.475 260.475 ↑ 1.0 1 17,365

Index Scan using pk_etd_id on event_type_dimension etd (cost=0.00..0.42 rows=1 width=4) (actual time=0.014..0.015 rows=1 loops=17,365)

  • Index Cond: (etd_id = evar.etd_id)
  • Filter: (((event_type_code)::text <> 'ARINC620-MTC'::text) AND ((event_type_code)::text <> 'ARINC620-MOC'::text))
  • Rows Removed by Filter: 0
23. 91.986 91.986 ↑ 1.0 1 15,331

Index Scan using pk_qc_grade_alg_id on qc_grade_algorithm_dimension qcg (cost=0.00..0.27 rows=1 width=19) (actual time=0.005..0.006 rows=1 loops=15,331)

  • Index Cond: (qc_grade_algorithm_id = qc.qc_grade_algorithm_id)