explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aixO

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

Nested Loop (cost=5,902.59..209,535.00 rows=1 width=325) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

Nested Loop (cost=5,901.90..209,499.67 rows=1 width=418) (actual rows= loops=)

  • Join Filter: ((ts_1.train_service_key = stt.train_service_key) AND (COALESCE(staging.service_tp_timestamp(LEAST(ts_1.origin_depart_time_public, ts_1.origin_depart_time), stt.actual_time), staging.service_tp_timestamp(LEAST(ts_1.origin_depart_time_public, ts_1.origin_depart_time), stt.estimated_time), staging.service_tp_timestamp(LEAST(ts_1.origin_depart_time_public, ts_1.origin_depart_time), stt.working_time)) >= '2020-09-09 19:58:00+01'::timestamp with time zone) AND (COALESCE(staging.service_tp_timestamp(LEAST(ts_1.origin_depart_time_public, ts_1.origin_depart_time), stt.actual_time), staging.service_tp_timestamp(LEAST(ts_1.origin_depart_time_public, ts_1.origin_depart_time), stt.estimated_time), staging.service_tp_timestamp(LEAST(ts_1.origin_depart_time_public, ts_1.origin_depart_time), stt.working_time)) <= '2020-09-09 21:30:00+01'::timestamp with time zone))
3. 0.000 0.000 ↓ 0.0

Nested Loop (cost=5,901.21..209,270.43 rows=21 width=363) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on train_service ts_1 (cost=5,900.65..63,719.92 rows=17,889 width=42) (actual rows= loops=)

  • Recheck Cond: ((dp_code = 'DARWIN'::text) AND (run_date >= '2020-09-08 00:00:00'::timestamp without time zone) AND (run_date <= (GREATEST('2020-09-09 20:30:00.000000'::text, '2020-09-09 21:40:00.000000'::text))::date))
5. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on train_service_run_date_index (cost=0.00..5,896.18 rows=17,889 width=0) (actual rows= loops=)

  • Index Cond: ((dp_code = 'DARWIN'::text) AND (run_date >= '2020-09-08 00:00:00'::timestamp without time zone) AND (run_date <= (GREATEST('2020-09-09 20:30:00.000000'::text, '2020-09-09 21:40:00.000000'::text))::date))
6. 0.000 0.000 ↓ 0.0

Index Scan using train_service_pk on train_service ts (cost=0.56..8.13 rows=1 width=325) (actual rows= loops=)

  • Index Cond: ((dp_code = 'DARWIN'::text) AND (train_service_key = ts_1.train_service_key))
  • Filter: (ts_1.run_date = run_date)
7. 0.000 0.000 ↓ 0.0

Index Scan using service_tp_time_pk on service_tp_time stt (cost=0.69..7.85 rows=2 width=95) (actual rows= loops=)

  • Index Cond: ((dp_code = 'DARWIN'::text) AND (train_service_key = ts.train_service_key) AND (location_key = 'WATRLMN'::text))
  • Filter: (time_type <> 'pass'::text)
8. 0.000 0.000 ↓ 0.0

Index Scan using service_timing_point_pk on service_timing_point stp (cost=0.69..35.32 rows=1 width=72) (actual rows= loops=)

  • Index Cond: ((dp_code = 'DARWIN'::text) AND (train_service_key = stt.train_service_key) AND (location_key = 'WATRLMN'::text) AND (tiploc_instance = stt.tiploc_instance))
  • Filter: (SubPlan 1)
9.          

SubPlan (for Index Scan)

10. 0.000 0.000 ↓ 0.0

Result (cost=1.94..27.55 rows=1 width=0) (actual rows= loops=)

  • One-Time Filter: (stp.dp_code = 'DARWIN'::text)
11. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.94..27.55 rows=1 width=0) (actual rows= loops=)

  • Join Filter: ((stp_1.tiploc_instance = stt_1.tiploc_instance) AND (COALESCE(staging.service_tp_timestamp(LEAST(ts_2.origin_depart_time_public, ts_2.origin_depart_time), stt_1.actual_time), staging.service_tp_timestamp(LEAST(ts_2.origin_depart_time_public, ts_2.origin_depart_time), stt_1.estimated_time), staging.service_tp_timestamp(LEAST(ts_2.origin_depart_time_public, ts_2.origin_depart_time), stt_1.working_time)) >= '2020-09-09 20:30:00+01'::timestamp with time zone) AND (COALESCE(staging.service_tp_timestamp(LEAST(ts_2.origin_depart_time_public, ts_2.origin_depart_time), stt_1.actual_time), staging.service_tp_timestamp(LEAST(ts_2.origin_depart_time_public, ts_2.origin_depart_time), stt_1.estimated_time), staging.service_tp_timestamp(LEAST(ts_2.origin_depart_time_public, ts_2.origin_depart_time), stt_1.working_time)) <= '2020-09-09 21:40:00+01'::timestamp with time zone))
12. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.25..17.30 rows=1 width=85) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Index Scan using service_tp_time_fki_service_tp on service_timing_point stp_1 (cost=0.69..8.71 rows=1 width=72) (actual rows= loops=)

  • Index Cond: ((dp_code = 'DARWIN'::text) AND (train_service_key = stp.train_service_key) AND (location_key = 'ASFDMSX'::text))
  • Filter: (timing_point_seq > stp.timing_point_seq)
14. 0.000 0.000 ↓ 0.0

Index Scan using train_service_pk on train_service ts_2 (cost=0.56..8.58 rows=1 width=38) (actual rows= loops=)

  • Index Cond: ((dp_code = 'DARWIN'::text) AND (train_service_key = stp.train_service_key))
15. 0.000 0.000 ↓ 0.0

Index Scan using service_tp_time_fkey_service_tp_idx on service_tp_time stt_1 (cost=0.69..8.71 rows=1 width=95) (actual rows= loops=)

  • Index Cond: ((dp_code = 'DARWIN'::text) AND (train_service_key = stp.train_service_key) AND (location_key = 'ASFDMSX'::text))