explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KlEd

Settings
# exclusive inclusive rows x rows loops node
1. 634.118 1,277.198 ↑ 4.0 100,976 1

Nested Loop Left Join (cost=19,865.39..124,508.51 rows=403,908 width=1,400) (actual time=372.187..1,277.198 rows=100,976 loops=1)

  • Output: 0, a.id, d.id, concat(a.id, d.id), a.company, a.remarks, a.tags, a.company, ((a.company)::text || (a.number)::text), ((d.company)::text || (d.number)::text), a.number, d.number, a.register, d.register, COALESCE(a.register, d.register), COALESCE(a.fleet, d.fleet), COALESCE(fleet_mode.id, 109), COALESCE(fleet_mode.code, 'NARROWBODY'::character varying), aena_group.code, a.fleet, d.fleet, a."from", a."to", d."to", to_char(timezone('Europe/London'::text, a.sta), 'HH24MI'::text), to_char(timezone('Europe/London'::text, a.eta), 'HH24MI'::text), to_char(timezone('Europe/London'::text, a.ata), 'HH24MI'::text), to_char(timezone('Europe/London'::text, a.fta), 'HH24MI'::text), to_char(timezone('Europe/London'::text, d.std), 'HH24MI'::text), to_char(timezone('Europe/London'::text, d.etd), 'HH24MI'::text), to_char(timezone('Europe/London'::text, d.atd), 'HH24MI'::text), to_char(timezone('Europe/London'::text, d.ftd), 'HH24MI'::text), (date_part('epoch'::text, ((COALESCE(a.ata, a.eta) - a.sta) / '60'::double precision)))::integer, (timezone('Europe/London'::text, a.sta))::date, (timezone('Europe/London'::text, d.std))::date, (timezone('Europe/London'::text, a.eta))::date, (timezone('Europe/London'::text, a.ata))::date, (timezone('Europe/London'::text, d.etd))::date, a.international, d.international, to_char(timezone('Europe/London'::text, COALESCE(a.ata, a.fta, a.eta, a.sta)), 'YYYY-MM-DD HH24:MI'::text), to_char(timezone('Europe/London'::text, COALESCE(d.ftd, d.atd, d.etd, d.std)), 'YYYY-MM-DD HH24:MI'::text), to_char(timezone('Europe/London'::text, a.rr), 'HH24MI'::text), to_char(timezone('Europe/London'::text, d.rr), 'HH24MI'::text), a.div, d.div, 0, COALESCE(a.ata, a.fta, a.eta, a.sta), COALESCE(d.ftd, d.atd, d.etd, d.std), timezone('Europe/London'::text, COALESCE(a.eta, a.sta)), timezone('Europe/London'::text, COALESCE(d.etd, d.std)), ma.park, md.park, ma.gate, md.gate, ma.gate_id, md.gate_id, ma.status, md.status, ma.status_mask, md.status_mask, timezone('Europe/London'::text, ma.gpu_time), timezone('Europe/London'::text, ma.general_status_date), ma.general_status, ma.status_mask, ((ma.id IS NOT NULL) AND ((ma.status_mask & 32) = 32)), CASE WHEN ((ma.gpu_time IS NOT NULL) AND (ma.general_status_date IS NOT NULL)) THEN 2 ELSE CASE WHEN ((ma.gpu_time IS NOT NULL) OR (ma.general_status_date IS NOT NULL)) THEN 1 ELSE 0 END END, a.route_type, d.route_type, (a.delays)::text, (d.delays)::text, s.service_name
  • JIT:
  • Functions: 184
  • Options: Inlining false, Optimization false, Expressions true, Deforming true
  • Timing: Generation 40.036 ms, Inlining 0.000 ms, Optimization 21.167 ms, Emission 355.824 ms, Total 417.027 ms
2. 79.334 643.080 ↑ 1.0 100,976 1

Hash Left Join (cost=19,864.97..67,951.73 rows=100,977 width=1,089) (actual time=371.784..643.080 rows=100,976 loops=1)

  • Output: a.id, a.company, a.remarks, a.tags, a.number, a.register, a.fleet, a."from", a."to", a.sta, a.eta, a.ata, a.fta, a.international, a.rr, a.div, a.route_type, a.delays, d.id, d.company, d.number, d.register, d.fleet, d."to", d.std, d.etd, d.atd, d.ftd, d.international, d.rr, d.div, d.route_type, d.delays, ma.park, ma.gate, ma.gate_id, ma.status, ma.status_mask, ma.gpu_time, ma.general_status_date, ma.general_status, ma.id, md.park, md.gate, md.gate_id, md.status, md.status_mask, fleet_mode.id, fleet_mode.code, aena_group.code
  • Inner Unique: true
  • Hash Cond: (fleets.group_id = aena_group.id)
3. 14.993 563.638 ↑ 1.0 100,976 1

Gather (cost=19,854.24..67,674.18 rows=100,977 width=1,087) (actual time=371.667..563.638 rows=100,976 loops=1)

  • Output: a.id, a.company, a.remarks, a.tags, a.number, a.register, a.fleet, a."from", a."to", a.sta, a.eta, a.ata, a.fta, a.international, a.rr, a.div, a.route_type, a.delays, d.id, d.company, d.number, d.register, d.fleet, d."to", d.std, d.etd, d.atd, d.ftd, d.international, d.rr, d.div, d.route_type, d.delays, ma.park, ma.gate, ma.gate_id, ma.status, ma.status_mask, ma.gpu_time, ma.general_status_date, ma.general_status, ma.id, md.park, md.gate, md.gate_id, md.status, md.status_mask, fleets.group_id, fleet_mode.id, fleet_mode.code
  • Workers Planned: 2
  • Workers Launched: 2
  • JIT for worker 0:
  • Functions: 56
  • Options: Inlining false, Optimization false, Expressions true, Deforming true
  • Timing: Generation 9.377 ms, Inlining 0.000 ms, Optimization 3.401 ms, Emission 71.993 ms, Total 84.771 ms
  • JIT for worker 1:
  • Functions: 56
  • Options: Inlining false, Optimization false, Expressions true, Deforming true
  • Timing: Generation 17.546 ms, Inlining 0.000 ms, Optimization 12.886 ms, Emission 140.324 ms, Total 170.757 ms
4. 17.157 548.645 ↑ 1.3 33,659 3 / 3

Parallel Hash Left Join (cost=18,854.24..56,576.48 rows=42,074 width=1,087) (actual time=306.522..548.645 rows=33,659 loops=3)

  • Output: a.id, a.company, a.remarks, a.tags, a.number, a.register, a.fleet, a."from", a."to", a.sta, a.eta, a.ata, a.fta, a.international, a.rr, a.div, a.route_type, a.delays, d.id, d.company, d.number, d.register, d.fleet, d."to", d.std, d.etd, d.atd, d.ftd, d.international, d.rr, d.div, d.route_type, d.delays, ma.park, ma.gate, ma.gate_id, ma.status, ma.status_mask, ma.gpu_time, ma.general_status_date, ma.general_status, ma.id, md.park, md.gate, md.gate_id, md.status, md.status_mask, fleets.group_id, fleet_mode.id, fleet_mode.code
  • Hash Cond: (d.id = md.route_id)
  • Worker 0: actual time=303.232..765.582 rows=67,404 loops=1
  • Worker 1: actual time=246.038..507.969 rows=33,449 loops=1
5. 15.306 320.637 ↑ 1.3 33,659 3 / 3

Nested Loop Left Join (cost=9,594.03..47,125.98 rows=42,074 width=1,070) (actual time=95.134..320.637 rows=33,659 loops=3)

  • Output: a.id, a.company, a.remarks, a.tags, a.number, a.register, a.fleet, a."from", a."to", a.sta, a.eta, a.ata, a.fta, a.international, a.rr, a.div, a.route_type, a.delays, d.id, d.company, d.number, d.register, d.fleet, d."to", d.std, d.etd, d.atd, d.ftd, d.international, d.rr, d.div, d.route_type, d.delays, ma.park, ma.gate, ma.gate_id, ma.status, ma.status_mask, ma.gpu_time, ma.general_status_date, ma.general_status, ma.id, fleets.group_id, fleet_mode.id, fleet_mode.code
  • Inner Unique: true
  • Join Filter: (a.id = 528,166)
  • Rows Removed by Join Filter: 12,026
  • Worker 0: actual time=102.869..532.802 rows=67,404 loops=1
  • Worker 1: actual time=91.381..336.137 rows=33,449 loops=1
6. 21.601 238.014 ↑ 1.3 33,659 3 / 3

Hash Left Join (cost=9,593.60..24,286.94 rows=42,074 width=831) (actual time=95.079..238.014 rows=33,659 loops=3)

  • Output: a.id, a.company, a.remarks, a.tags, a.number, a.register, a.fleet, a."from", a."to", a.sta, a.eta, a.ata, a.fta, a.international, a.rr, a.div, a.route_type, a.delays, a.link_id, ma.park, ma.gate, ma.gate_id, ma.status, ma.status_mask, ma.gpu_time, ma.general_status_date, ma.general_status, ma.id, fleets.group_id, fleet_mode.id, fleet_mode.code
  • Hash Cond: ((a.register)::text = (air.register)::text)
  • Worker 0: actual time=102.772..378.101 rows=67,404 loops=1
  • Worker 1: actual time=91.324..243.701 rows=33,449 loops=1
7. 95.886 208.926 ↑ 1.3 33,308 3 / 3

Parallel Hash Join (cost=9,260.22..22,997.09 rows=42,074 width=817) (actual time=87.520..208.926 rows=33,308 loops=3)

  • Output: a.id, a.company, a.remarks, a.tags, a.number, a.register, a.fleet, a."from", a."to", a.sta, a.eta, a.ata, a.fta, a.international, a.rr, a.div, a.route_type, a.delays, a.link_id, ma.park, ma.gate, ma.gate_id, ma.status, ma.status_mask, ma.gpu_time, ma.general_status_date, ma.general_status, ma.id
  • Hash Cond: (a.id = ma.route_id)
  • Worker 0: actual time=95.113..329.432 rows=66,708 loops=1
  • Worker 1: actual time=83.749..212.694 rows=33,095 loops=1
8. 29.619 29.619 ↑ 1.3 180,914 3 / 3

Parallel Seq Scan on public.routes a (cost=0.00..12,710.77 rows=226,877 width=771) (actual time=0.013..29.619 rows=180,914 loops=3)

  • Output: a.id, a.company, a.number, a.register, a.fleet, a.link_id, a.international, a.type, a.status, a.std, a.etd, a.atd, a.ftd, a.sta, a.eta, a.ata, a.fta, a."from", a."to", a.created_at, a.updated_at, a.user_insert_id, a.remarks, a.rr, a.div, a.route_type, a.company_msg, a.final_a, a.final_d, a.code_share, a.route_code, a.route_status, a.delays, a.tags, a.crew, a.crew_captain, a.capacity, a.real_pax, a.estimated_pax
  • Worker 0: actual time=0.011..56.228 rows=350,668 loops=1
  • Worker 1: actual time=0.016..32.411 rows=191,437 loops=1
9. 25.685 83.421 ↑ 1.3 33,308 3 / 3

Parallel Hash (cost=8,734.29..8,734.29 rows=42,074 width=50) (actual time=83.421..83.421 rows=33,308 loops=3)

  • Output: ma.park, ma.gate, ma.gate_id, ma.status, ma.status_mask, ma.gpu_time, ma.general_status_date, ma.general_status, ma.id, ma.route_id
  • Buckets: 131,072 Batches: 1 Memory Usage: 6,528kB
  • Worker 0: actual time=83.625..83.625 rows=23,454 loops=1
  • Worker 1: actual time=83.567..83.567 rows=28,681 loops=1
10. 57.736 57.736 ↑ 1.3 33,308 3 / 3

Parallel Seq Scan on public.routes_airport_medias ma (cost=0.00..8,734.29 rows=42,074 width=50) (actual time=0.100..57.736 rows=33,308 loops=3)

  • Output: ma.park, ma.gate, ma.gate_id, ma.status, ma.status_mask, ma.gpu_time, ma.general_status_date, ma.general_status, ma.id, ma.route_id
  • Filter: ((ma.station)::text = 'LPA'::text)
  • Rows Removed by Filter: 159,275
  • Worker 0: actual time=0.104..58.800 rows=23,454 loops=1
  • Worker 1: actual time=0.041..55.716 rows=28,681 loops=1
11. 1.607 7.487 ↓ 1.0 7,426 3 / 3

Hash (cost=240.67..240.67 rows=7,417 width=19) (actual time=7.487..7.487 rows=7,426 loops=3)

  • Output: air.register, fleets.group_id, fleet_mode.id, fleet_mode.code
  • Buckets: 8,192 Batches: 1 Memory Usage: 448kB
  • Worker 0: actual time=7.580..7.580 rows=7,426 loops=1
  • Worker 1: actual time=7.497..7.497 rows=7,426 loops=1
12. 1.861 5.880 ↓ 1.0 7,427 3 / 3

Hash Left Join (cost=31.62..240.67 rows=7,417 width=19) (actual time=0.443..5.880 rows=7,427 loops=3)

  • Output: air.register, fleets.group_id, fleet_mode.id, fleet_mode.code
  • Inner Unique: true
  • Hash Cond: (fleets.type_id = fleet_mode.id)
  • Worker 0: actual time=0.423..5.912 rows=7,427 loops=1
  • Worker 1: actual time=0.469..5.874 rows=7,427 loops=1
13. 1.883 3.893 ↓ 1.0 7,427 3 / 3

Hash Left Join (cost=20.89..210.20 rows=7,417 width=13) (actual time=0.305..3.893 rows=7,427 loops=3)

  • Output: air.register, fleets.type_id, fleets.group_id
  • Inner Unique: true
  • Hash Cond: (air.fleet_id = fleets.id)
  • Worker 0: actual time=0.296..3.940 rows=7,427 loops=1
  • Worker 1: actual time=0.331..3.857 rows=7,427 loops=1
14. 1.748 1.748 ↓ 1.0 7,427 3 / 3

Seq Scan on public.aircrafts air (cost=0.00..169.71 rows=7,417 width=9) (actual time=0.030..1.748 rows=7,427 loops=3)

  • Output: air.id, air.code, air.register, air.company_code, air.fleet_id, air.created_at, air.active, air.user_insert_id, air.status_mask, air.last_service_id, air.last_service_name, air.last_service_date, air.last_service_user_id, air.last_service_deep_id, air.last_service_deep_name, air.last_service_deep_date, air.last_service_deep_user_id
  • Filter: (air.active = 1)
  • Worker 0: actual time=0.032..1.800 rows=7,427 loops=1
  • Worker 1: actual time=0.044..1.725 rows=7,427 loops=1
15. 0.093 0.262 ↑ 1.0 573 3 / 3

Hash (cost=13.73..13.73 rows=573 width=12) (actual time=0.261..0.262 rows=573 loops=3)

  • Output: fleets.id, fleets.type_id, fleets.group_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 32kB
  • Worker 0: actual time=0.250..0.250 rows=573 loops=1
  • Worker 1: actual time=0.273..0.273 rows=573 loops=1
16. 0.169 0.169 ↑ 1.0 573 3 / 3

Seq Scan on public.fleets (cost=0.00..13.73 rows=573 width=12) (actual time=0.017..0.169 rows=573 loops=3)

  • Output: fleets.id, fleets.type_id, fleets.group_id
  • Worker 0: actual time=0.016..0.158 rows=573 loops=1
  • Worker 1: actual time=0.018..0.178 rows=573 loops=1
17. 0.051 0.126 ↓ 1.0 300 3 / 3

Hash (cost=6.99..6.99 rows=299 width=10) (actual time=0.126..0.126 rows=300 loops=3)

  • Output: fleet_mode.id, fleet_mode.code
  • Buckets: 1,024 Batches: 1 Memory Usage: 21kB
  • Worker 0: actual time=0.116..0.116 rows=300 loops=1
  • Worker 1: actual time=0.127..0.128 rows=300 loops=1
18. 0.075 0.075 ↓ 1.0 300 3 / 3

Seq Scan on public.generic_types fleet_mode (cost=0.00..6.99 rows=299 width=10) (actual time=0.019..0.075 rows=300 loops=3)

  • Output: fleet_mode.id, fleet_mode.code
  • Worker 0: actual time=0.021..0.066 rows=300 loops=1
  • Worker 1: actual time=0.024..0.075 rows=300 loops=1
19. 67.317 67.317 ↓ 0.0 0 100,976 / 3

Index Scan using routes_pkey on public.routes d (cost=0.42..0.53 rows=1 width=243) (actual time=0.002..0.002 rows=0 loops=100,976)

  • Output: d.id, d.company, d.number, d.register, d.fleet, d.link_id, d.international, d.type, d.status, d.std, d.etd, d.atd, d.ftd, d.sta, d.eta, d.ata, d.fta, d."from", d."to", d.created_at, d.updated_at, d.user_insert_id, d.remarks, d.rr, d.div, d.route_type, d.company_msg, d.final_a, d.final_d, d.code_share, d.route_code, d.route_status, d.delays, d.tags, d.crew, d.crew_captain, d.capacity, d.real_pax, d.estimated_pax
  • Index Cond: (d.id = a.link_id)
  • Worker 0: actual time=0.001..0.002 rows=0 loops=67,404
  • Worker 1: actual time=0.002..0.002 rows=0 loops=33,449
20. 23.856 210.851 ↑ 1.3 33,308 3 / 3

Parallel Hash (cost=8,734.29..8,734.29 rows=42,074 width=21) (actual time=210.851..210.851 rows=33,308 loops=3)

  • Output: md.park, md.gate, md.gate_id, md.status, md.status_mask, md.route_id
  • Buckets: 131,072 Batches: 1 Memory Usage: 5,632kB
  • Worker 0: actual time=200.072..200.072 rows=66,615 loops=1
  • Worker 1: actual time=154.327..154.327 rows=0 loops=1
21. 186.995 186.995 ↑ 1.3 33,308 3 / 3

Parallel Seq Scan on public.routes_airport_medias md (cost=0.00..8,734.29 rows=42,074 width=21) (actual time=129.490..186.995 rows=33,308 loops=3)

  • Output: md.park, md.gate, md.gate_id, md.status, md.status_mask, md.route_id
  • Filter: ((md.station)::text = 'LPA'::text)
  • Rows Removed by Filter: 159,275
  • Worker 0: actual time=76.480..163.546 rows=66,615 loops=1
  • Worker 1: actual time=154.199..154.199 rows=0 loops=1
22. 0.052 0.108 ↓ 1.0 300 1

Hash (cost=6.99..6.99 rows=299 width=10) (actual time=0.107..0.108 rows=300 loops=1)

  • Output: aena_group.code, aena_group.id
  • Buckets: 1,024 Batches: 1 Memory Usage: 21kB
23. 0.056 0.056 ↓ 1.0 300 1

Seq Scan on public.generic_types aena_group (cost=0.00..6.99 rows=299 width=10) (actual time=0.013..0.056 rows=300 loops=1)

  • Output: aena_group.code, aena_group.id
24. 0.000 0.000 ↑ 4.0 1 100,976

Materialize (cost=0.42..9.67 rows=4 width=18) (actual time=0.000..0.000 rows=1 loops=100,976)

  • Output: s.service_name
25. 0.046 0.046 ↑ 4.0 1 1

Index Scan using routes_services_cabin_idx_route_id on public.routes_services_cabin s (cost=0.42..9.65 rows=4 width=18) (actual time=0.041..0.046 rows=1 loops=1)

  • Output: s.service_name
  • Index Cond: (s.route_id = 528,166)
  • Filter: ((s.active = 1) AND ((s.service_type)::text = 'BOW'::text))
  • Rows Removed by Filter: 3
Planning time : 5.896 ms
Execution time : 1,346.415 ms