explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7Gfe

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 37,833.113 ↑ 4.6 100,846 1

Gather (cost=19,781.97..7,285,969.88 rows=460,058 width=1,101) (actual time=3,784.935..37,833.113 rows=100,846 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, airl.iata, airl.icao, airl_d.iata, airl_d.icao, fleet_mode.id, fleet_mode.code, aena_group.code
  • Workers Planned: 2
  • Workers Launched: 2
  • JIT for worker 0:
  • Functions: 72
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 11.648 ms, Inlining 78.421 ms, Optimization 2070.602 ms, Emission 1024.267 ms, Total 3184.939 ms
  • JIT for worker 1:
  • Functions: 72
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 28.842 ms, Inlining 158.033 ms, Optimization 2018.600 ms, Emission 1214.235 ms, Total 3419.709 ms
  • JIT:
  • Functions: 224
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 55.030 ms, Inlining 405.195 ms, Optimization 6405.474 ms, Emission 3455.000 ms, Total 10320.699 ms
2. 50.298 38,296.982 ↑ 5.7 33,615 3 / 3

Hash Left Join (cost=18,781.97..7,238,964.08 rows=191,691 width=1,101) (actual time=3,721.466..38,296.982 rows=33,615 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, airl.iata, airl.icao, airl_d.iata, airl_d.icao, fleet_mode.id, fleet_mode.code, aena_group.code
  • Inner Unique: true
  • Hash Cond: (fleets.group_id = aena_group.id)
  • Worker 0: actual time=3,721.651..38590.292 rows=34,164 loops=1
  • Worker 1: actual time=3,660.505..38554.912 rows=34,106 loops=1
3. 66.436 38,246.585 ↑ 5.7 33,615 3 / 3

Hash Left Join (cost=18,771.24..7,238,446.82 rows=191,691 width=1,099) (actual time=3,721.357..38,246.585 rows=33,615 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, airl.iata, airl.icao, airl_d.iata, airl_d.icao, fleets.group_id, fleet_mode.id, fleet_mode.code
  • Inner Unique: true
  • Hash Cond: (fleets.type_id = fleet_mode.id)
  • Worker 0: actual time=3,721.542..38532.987 rows=34,164 loops=1
  • Worker 1: actual time=3,660.397..38501.214 rows=34,106 loops=1
4. 7,065.555 38,180.029 ↑ 5.7 33,615 3 / 3

Nested Loop Left Join (cost=18,760.52..7,237,925.79 rows=191,691 width=1,093) (actual time=3,721.224..38,180.029 rows=33,615 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, airl.iata, airl.icao, airl_d.iata, airl_d.icao, fleets.type_id, fleets.group_id
  • Join Filter: (((airl_d.icao)::text = (d.company)::text) OR ((airl_d.iata)::text = (d.company)::text))
  • Rows Removed by Join Filter: 53,213,073
  • Worker 0: actual time=3,721.416..38464.847 rows=34,164 loops=1
  • Worker 1: actual time=3,660.271..38436.907 rows=34,106 loops=1
5. 10,072.194 22,878.717 ↑ 2.7 33,615 3 / 3

Nested Loop Left Join (cost=18,760.52..2,344,895.01 rows=89,657 width=1,087) (actual time=3,720.933..22,878.717 rows=33,615 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, airl.iata, airl.icao, fleets.type_id, fleets.group_id
  • Join Filter: (((airl.icao)::text = (a.company)::text) OR ((airl.iata)::text = (a.company)::text))
  • Rows Removed by Join Filter: 53,188,227
  • Worker 0: actual time=3,721.131..23071.917 rows=34,164 loops=1
  • Worker 1: actual time=3,659.972..23056.836 rows=34,106 loops=1
6. 66.941 4,436.305 ↑ 1.2 33,615 3 / 3

Parallel Hash Left Join (cost=18,760.52..56,346.96 rows=41,934 width=1,081) (actual time=3,720.675..4,436.305 rows=33,615 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.type_id, fleets.group_id
  • Hash Cond: (d.id = md.route_id)
  • Worker 0: actual time=3,720.898..4413.741 rows=34,164 loops=1
  • Worker 1: actual time=3,659.742..4372.051 rows=34,106 loops=1
7. 125.474 728.322 ↑ 1.2 33,615 3 / 3

Nested Loop Left Join (cost=9,531.06..46,927.82 rows=41,934 width=1,064) (actual time=74.124..728.322 rows=33,615 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.type_id, fleets.group_id
  • Inner Unique: true
  • Join Filter: (a.id = 499,371)
  • Rows Removed by Join Filter: 11,984
  • Worker 0: actual time=76.845..694.307 rows=34,164 loops=1
  • Worker 1: actual time=76.343..744.806 rows=34,106 loops=1
8. 66.614 434.771 ↑ 1.2 33,615 3 / 3

Hash Left Join (cost=9,530.63..24,164.40 rows=41,934 width=825) (actual time=74.114..434.771 rows=33,615 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.type_id, fleets.group_id
  • Hash Cond: ((a.register)::text = (air.register)::text)
  • Worker 0: actual time=76.835..412.951 rows=34,164 loops=1
  • Worker 1: actual time=76.334..426.712 rows=34,106 loops=1
9. 241.340 363.920 ↑ 1.3 33,267 3 / 3

Parallel Hash Join (cost=9,229.46..22,909.91 rows=41,934 width=817) (actual time=69.799..363.920 rows=33,267 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=72.521..337.585 rows=33,775 loops=1
  • Worker 1: actual time=72.007..346.669 rows=33,788 loops=1
10. 55.580 55.580 ↑ 1.3 180,315 3 / 3

Parallel Seq Scan on public.routes a (cost=0.00..12,658.43 rows=225,943 width=771) (actual time=0.014..55.580 rows=180,315 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.020..62.375 rows=185,920 loops=1
  • Worker 1: actual time=0.008..36.887 rows=183,316 loops=1
11. 28.323 67.000 ↑ 1.3 33,267 3 / 3

Parallel Hash (cost=8,705.29..8,705.29 rows=41,934 width=50) (actual time=67.000..67.000 rows=33,267 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,560kB
  • Worker 0: actual time=71.629..71.629 rows=28,899 loops=1
  • Worker 1: actual time=64.693..64.694 rows=46,791 loops=1
12. 38.677 38.677 ↑ 1.3 33,267 3 / 3

Parallel Seq Scan on public.routes_airport_medias ma (cost=0.00..8,705.29 rows=41,934 width=50) (actual time=0.084..38.677 rows=33,267 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: 158,669
  • Worker 0: actual time=0.162..31.015 rows=28,899 loops=1
  • Worker 1: actual time=0.045..46.475 rows=46,791 loops=1
13. 1.331 4.237 ↓ 1.0 7,407 3 / 3

Hash (cost=209.24..209.24 rows=7,354 width=13) (actual time=4.237..4.237 rows=7,407 loops=3)

  • Output: air.register, fleets.type_id, fleets.group_id
  • Buckets: 8,192 Batches: 1 Memory Usage: 385kB
  • Worker 0: actual time=4.223..4.223 rows=7,407 loops=1
  • Worker 1: actual time=4.245..4.245 rows=7,407 loops=1
14. 1.438 2.906 ↓ 1.0 7,408 3 / 3

Hash Left Join (cost=20.89..209.24 rows=7,354 width=13) (actual time=0.266..2.906 rows=7,408 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.267..2.890 rows=7,408 loops=1
  • Worker 1: actual time=0.253..2.893 rows=7,408 loops=1
15. 1.247 1.247 ↓ 1.0 7,408 3 / 3

Seq Scan on public.aircrafts air (cost=0.00..168.93 rows=7,354 width=9) (actual time=0.031..1.247 rows=7,408 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.041..1.223 rows=7,408 loops=1
  • Worker 1: actual time=0.031..1.238 rows=7,408 loops=1
16. 0.094 0.221 ↑ 1.0 573 3 / 3

Hash (cost=13.73..13.73 rows=573 width=12) (actual time=0.221..0.221 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.211..0.212 rows=573 loops=1
  • Worker 1: actual time=0.208..0.208 rows=573 loops=1
17. 0.127 0.127 ↑ 1.0 573 3 / 3

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

  • Output: fleets.id, fleets.type_id, fleets.group_id
  • Worker 0: actual time=0.022..0.118 rows=573 loops=1
  • Worker 1: actual time=0.021..0.114 rows=573 loops=1
18. 168.077 168.077 ↓ 0.0 0 100,846 / 3

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

  • 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.005..0.005 rows=0 loops=34,164
  • Worker 1: actual time=0.006..0.006 rows=0 loops=34,106
19. 143.768 3,641.042 ↑ 1.3 33,267 3 / 3

Parallel Hash (cost=8,705.29..8,705.29 rows=41,934 width=21) (actual time=3,641.042..3,641.042 rows=33,267 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,600kB
  • Worker 0: actual time=3,643.857..3643.857 rows=99,800 loops=1
  • Worker 1: actual time=3,575.695..3575.695 rows=0 loops=1
20. 3,497.274 3,497.274 ↑ 1.3 33,267 3 / 3

Parallel Seq Scan on public.routes_airport_medias md (cost=0.00..8,705.29 rows=41,934 width=21) (actual time=3,423.735..3,497.274 rows=33,267 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: 158,669
  • Worker 0: actual time=3,175.192..3395.811 rows=99,800 loops=1
  • Worker 1: actual time=3,392.479..3392.479 rows=0 loops=1
21. 8,370.218 8,370.218 ↑ 1.0 1,583 100,846 / 3

Seq Scan on public.airlines airl (cost=0.00..30.83 rows=1,583 width=6) (actual time=0.007..0.249 rows=1,583 loops=100,846)

  • Output: airl.id, airl.name, airl.iata, airl.icao, airl.remarks, airl.created_at, airl.active, airl.user_insert_id
  • Worker 0: actual time=0.008..0.252 rows=1,583 loops=34,164
  • Worker 1: actual time=0.007..0.246 rows=1,583 loops=34,106
22. 8,235.757 8,235.757 ↑ 1.0 1,583 100,846 / 3

Seq Scan on public.airlines airl_d (cost=0.00..30.83 rows=1,583 width=6) (actual time=0.006..0.245 rows=1,583 loops=100,846)

  • Output: airl_d.id, airl_d.name, airl_d.iata, airl_d.icao, airl_d.remarks, airl_d.created_at, airl_d.active, airl_d.user_insert_id
  • Worker 0: actual time=0.006..0.241 rows=1,583 loops=34,164
  • Worker 1: actual time=0.006..0.243 rows=1,583 loops=34,106
23. 0.052 0.120 ↓ 1.0 300 3 / 3

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

  • Output: fleet_mode.id, fleet_mode.code
  • Buckets: 1,024 Batches: 1 Memory Usage: 21kB
  • Worker 0: actual time=0.114..0.114 rows=300 loops=1
  • Worker 1: actual time=0.114..0.114 rows=300 loops=1
24. 0.068 0.068 ↓ 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.020..0.068 rows=300 loops=3)

  • Output: fleet_mode.id, fleet_mode.code
  • Worker 0: actual time=0.019..0.063 rows=300 loops=1
  • Worker 1: actual time=0.018..0.062 rows=300 loops=1
25. 0.052 0.099 ↓ 1.0 300 3 / 3

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

  • Output: aena_group.code, aena_group.id
  • Buckets: 1,024 Batches: 1 Memory Usage: 21kB
  • Worker 0: actual time=0.099..0.100 rows=300 loops=1
  • Worker 1: actual time=0.099..0.100 rows=300 loops=1
26. 0.047 0.047 ↓ 1.0 300 3 / 3

Seq Scan on public.generic_types aena_group (cost=0.00..6.99 rows=299 width=10) (actual time=0.011..0.047 rows=300 loops=3)

  • Output: aena_group.code, aena_group.id
  • Worker 0: actual time=0.013..0.049 rows=300 loops=1
  • Worker 1: actual time=0.013..0.049 rows=300 loops=1
27. 0.000 0.000 ↑ 4.0 1 100,846 / 3

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

  • Output: s.service_name
28. 0.020 0.020 ↑ 4.0 1 1 / 3

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.057..0.061 rows=1 loops=1)

  • Output: s.service_name
  • Index Cond: (s.route_id = 499,371)
  • Filter: ((s.active = 1) AND ((s.service_type)::text = 'BOW'::text))
  • Rows Removed by Filter: 7
Planning time : 6.881 ms
Execution time : 38,871.586 ms