explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Mmja

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

Unique (cost=427,374.02..427,374.02 rows=1 width=16) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

Sort (cost=427,374.02..427,374.02 rows=1 width=16) (actual rows= loops=)

  • Sort Key: stp.train_service_key
3. 0.000 0.000 ↓ 0.0

Nested Loop (cost=5,902.03..427,374.01 rows=1 width=16) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Nested Loop (cost=5,901.34..422,146.21 rows=148 width=93) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on train_service ts (cost=5,900.65..63,719.92 rows=17,889 width=38) (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 20:40:00.000000'::text))::date))
6. 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 20:40:00.000000'::text))::date))
7. 0.000 0.000 ↓ 0.0

Index Scan using service_tp_time_pk on service_tp_time stt (cost=0.69..20.03 rows=1 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) AND (COALESCE(staging.service_tp_timestamp(LEAST(ts.origin_depart_time_public, ts.origin_depart_time), actual_time), staging.service_tp_timestamp(LEAST(ts.origin_depart_time_public, ts.origin_depart_time), estimated_time), staging.service_tp_timestamp(LEAST(ts.origin_depart_time_public, ts.origin_depart_time), working_time)) >= '2020-09-09 19:58:00+01'::timestamp with time zone) AND (COALESCE(staging.service_tp_timestamp(LEAST(ts.origin_depart_time_public, ts.origin_depart_time), actual_time), staging.service_tp_timestamp(LEAST(ts.origin_depart_time_public, ts.origin_depart_time), estimated_time), staging.service_tp_timestamp(LEAST(ts.origin_depart_time_public, ts.origin_depart_time), working_time)) <= '2020-09-09 20:10:00+01'::timestamp with time zone))
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_1.origin_depart_time_public, ts_1.origin_depart_time), stt_1.actual_time), staging.service_tp_timestamp(LEAST(ts_1.origin_depart_time_public, ts_1.origin_depart_time), stt_1.estimated_time), staging.service_tp_timestamp(LEAST(ts_1.origin_depart_time_public, ts_1.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_1.origin_depart_time_public, ts_1.origin_depart_time), stt_1.actual_time), staging.service_tp_timestamp(LEAST(ts_1.origin_depart_time_public, ts_1.origin_depart_time), stt_1.estimated_time), staging.service_tp_timestamp(LEAST(ts_1.origin_depart_time_public, ts_1.origin_depart_time), stt_1.working_time)) <= '2020-09-09 20: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_1 (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))