explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jECU

Settings
# exclusive inclusive rows x rows loops node
1. 12.612 5,680.958 ↑ 2.0 1 1

Gather (cost=17,747.34..26,510.33 rows=2 width=1,508) (actual time=5,678.367..5,680.958 rows=1 loops=1)

  • Output: (((((r.data)::json ->> 'flight_data'::text))::json ->> 'fid_a'::text)), (CASE WHEN (((((r.data)::json ->> 'signature'::text))::json ->> 'signature'::text) IS NOT NULL) THEN 1 ELSE 0 END), r.id, r.data, r.concept, r.concept_id, (timezone('Atlantic/Canary'::text, r.status_date)), r.user_insert_id, r.status, ((((u.name)::text || ', '::text) || (u.surname)::text)), ($0), (timezone('Atlantic/Canary'::text, rsc.end_date))
  • Workers Planned: 2
  • Params Evaluated: $0
  • Workers Launched: 2
2.          

Initplan (for Gather)

3. 0.002 5,531.376 ↓ 0.0 0 1

Limit (cost=0.00..40.63 rows=1 width=554) (actual time=5,531.376..5,531.376 rows=0 loops=1)

  • Output: requests.data
4. 0.006 5,531.374 ↓ 0.0 0 1

Append (cost=0.00..18,488.20 rows=455 width=554) (actual time=5,531.374..5,531.374 rows=0 loops=1)

5. 5,492.593 5,492.593 ↓ 0.0 0 1

Seq Scan on public.requests (cost=0.00..17,957.13 rows=414 width=591) (actual time=5,492.593..5,492.593 rows=0 loops=1)

  • Output: requests.data
  • Filter: ((requests.active = 1) AND (((requests.data)::json ->> 'station'::text) = 'TFN'::text))
  • Rows Removed by Filter: 82,910
6. 0.005 0.005 ↓ 0.0 0 1

Seq Scan on public.requests_claims (cost=0.00..11.80 rows=1 width=32) (actual time=0.005..0.005 rows=0 loops=1)

  • Output: requests_claims.data
  • Filter: ((requests_claims.active = 1) AND (((requests_claims.data)::json ->> 'station'::text) = 'TFN'::text))
7. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on public.requests_door_to_door (cost=0.00..14.05 rows=1 width=32) (actual time=0.003..0.003 rows=0 loops=1)

  • Output: requests_door_to_door.data
  • Filter: ((requests_door_to_door.active = 1) AND (((requests_door_to_door.data)::json ->> 'station'::text) = 'TFN'::text))
8. 0.013 0.013 ↓ 0.0 0 1

Seq Scan on public.requests_flights_notifications (cost=0.00..14.05 rows=1 width=32) (actual time=0.013..0.013 rows=0 loops=1)

  • Output: requests_flights_notifications.data
  • Filter: ((requests_flights_notifications.active = 1) AND (((requests_flights_notifications.data)::json ->> 'station'::text) = 'TFN'::text))
9. 34.869 34.869 ↓ 0.0 0 1

Seq Scan on public.requests_incidents (cost=0.00..428.11 rows=35 width=189) (actual time=34.869..34.869 rows=0 loops=1)

  • Output: requests_incidents.data
  • Filter: ((requests_incidents.active = 1) AND (((requests_incidents.data)::json ->> 'station'::text) = 'TFN'::text))
  • Rows Removed by Filter: 7,136
10. 3.815 3.815 ↓ 0.0 0 1

Seq Scan on public.requests_notifications (cost=0.00..32.68 rows=1 width=584) (actual time=3.815..3.815 rows=0 loops=1)

  • Output: requests_notifications.data
  • Filter: ((requests_notifications.active = 1) AND (((requests_notifications.data)::json ->> 'station'::text) = 'TFN'::text))
  • Rows Removed by Filter: 297
11. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on public.requests_service_requirements (cost=0.00..14.05 rows=1 width=32) (actual time=0.003..0.003 rows=0 loops=1)

  • Output: requests_service_requirements.data
  • Filter: ((requests_service_requirements.active = 1) AND (((requests_service_requirements.data)::json ->> 'station'::text) = 'TFN'::text))
12. 0.067 0.067 ↓ 0.0 0 1

Seq Scan on public.requests_work_orders (cost=0.00..14.05 rows=1 width=32) (actual time=0.067..0.067 rows=0 loops=1)

  • Output: requests_work_orders.data
  • Filter: ((requests_work_orders.active = 1) AND (((requests_work_orders.data)::json ->> 'station'::text) = 'TFN'::text))
  • Rows Removed by Filter: 33
13. 0.063 136.970 ↓ 0.0 0 3 / 3

Nested Loop Left Join (cost=16,706.71..25,469.49 rows=1 width=1,508) (actual time=120.636..136.970 rows=0 loops=3)

  • Output: ((((r.data)::json ->> 'flight_data'::text))::json ->> 'fid_a'::text), CASE WHEN (((((r.data)::json ->> 'signature'::text))::json ->> 'signature'::text) IS NOT NULL) THEN 1 ELSE 0 END, r.id, r.data, r.concept, r.concept_id, timezone('Atlantic/Canary'::text, r.status_date), r.user_insert_id, r.status, (((u.name)::text || ', '::text) || (u.surname)::text), $0, timezone('Atlantic/Canary'::text, rsc.end_date)
  • Inner Unique: true
  • Worker 0: actual time=134.924..134.924 rows=0 loops=1
  • Worker 1: actual time=81.128..130.130 rows=1 loops=1
14. 33.729 136.874 ↓ 0.0 0 3 / 3

Parallel Hash Join (cost=16,706.43..25,469.15 rows=1 width=1,408) (actual time=120.545..136.874 rows=0 loops=3)

  • Output: r.data, r.id, r.concept, r.concept_id, r.status_date, r.user_insert_id, r.status, rsc.end_date
  • Hash Cond: (rsc.id = r.concept_id)
  • Worker 0: actual time=134.923..134.923 rows=0 loops=1
  • Worker 1: actual time=80.858..129.841 rows=1 loops=1
15. 34.027 34.027 ↑ 1.2 130,414 3 / 3

Parallel Seq Scan on public.routes_services_cabin rsc (cost=0.00..8,151.88 rows=162,888 width=12) (actual time=0.044..34.027 rows=130,414 loops=3)

  • Output: rsc.id, rsc.route_id, rsc.type, rsc.service_id, rsc.service_name, rsc.service_type, rsc.user_insert_id, rsc.remarks, rsc.created_at, rsc.active, rsc.service_mask, rsc.service_old_id, rsc.service_old_name, rsc.ini_date, rsc.end_date, rsc.status, rsc.status_date, rsc.requested_by, rsc.location, rsc.assingment_date, rsc.leaving_date, rsc.arriving_date, rsc.start_loading_date, rsc.end_loading_date, rsc.security, rsc.route_etd, rsc.parent_id
  • Worker 0: actual time=0.042..36.853 rows=185,000 loops=1
  • Worker 1: actual time=0.070..34.993 rows=91,209 loops=1
16. 0.099 69.118 ↓ 0.0 0 3 / 3

Parallel Hash (cost=16,706.42..16,706.42 rows=1 width=1,404) (actual time=69.118..69.118 rows=0 loops=3)

  • Output: r.data, r.id, r.concept, r.concept_id, r.status_date, r.user_insert_id, r.status, routes_services_cabin.id
  • Buckets: 1,024 Batches: 1 Memory Usage: 40kB
  • Worker 0: actual time=67.265..67.265 rows=0 loops=1
  • Worker 1: actual time=62.508..62.508 rows=1 loops=1
17. 6.648 69.019 ↓ 0.0 0 3 / 3

Hash Semi Join (cost=9.75..16,706.42 rows=1 width=1,404) (actual time=68.487..69.019 rows=0 loops=3)

  • Output: r.data, r.id, r.concept, r.concept_id, r.status_date, r.user_insert_id, r.status, routes_services_cabin.id
  • Hash Cond: (r.concept_id = routes_services_cabin.id)
  • Worker 0: actual time=67.225..67.225 rows=0 loops=1
  • Worker 1: actual time=60.770..62.365 rows=1 loops=1
18. 62.310 62.310 ↑ 1.3 27,442 3 / 3

Parallel Seq Scan on public.requests r (cost=0.00..16,606.48 rows=34,351 width=1,400) (actual time=0.025..62.310 rows=27,442 loops=3)

  • Output: r.data, r.id, r.concept, r.concept_id, r.status_date, r.user_insert_id, r.status
  • Filter: (((r.type)::text = 'ADMIN_CLOSE'::text) AND (r.active = 1))
  • Rows Removed by Filter: 194
  • Worker 0: actual time=0.026..59.053 rows=18,333 loops=1
  • Worker 1: actual time=0.034..58.632 rows=20,348 loops=1
19. 0.011 0.061 ↑ 2.7 3 3 / 3

Hash (cost=9.65..9.65 rows=8 width=4) (actual time=0.061..0.061 rows=3 loops=3)

  • Output: routes_services_cabin.id
  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Worker 0: actual time=0.074..0.075 rows=3 loops=1
  • Worker 1: actual time=0.056..0.057 rows=3 loops=1
20. 0.050 0.050 ↑ 2.7 3 3 / 3

Index Scan using routes_services_cabin_idx_route_id on public.routes_services_cabin (cost=0.42..9.65 rows=8 width=4) (actual time=0.046..0.050 rows=3 loops=3)

  • Output: routes_services_cabin.id
  • Index Cond: (routes_services_cabin.route_id = 514,725)
  • Filter: (routes_services_cabin.active = 1)
  • Worker 0: actual time=0.061..0.064 rows=3 loops=1
  • Worker 1: actual time=0.044..0.047 rows=3 loops=1
21. 0.033 0.033 ↑ 1.0 1 1 / 3

Index Scan using users_pkey on public.users u (cost=0.28..0.30 rows=1 width=32) (actual time=0.098..0.098 rows=1 loops=1)

  • Output: u.id, u.name, u.surname, u.login, u.password, u.password_change_date, u.ini_date, u.end_date, u.type, u.parent_id, u.card_id, u.card_type, u.address, u.telephone, u.email, u.station_id, u.station, u.post_code, u.code, u.login_method, u.concept_id, u.roles, u.created_at, u.user_insert_id, u.remarks, u.active, u.seniority, u.time_zone, u.status, u.last_login, u.change_password, u.function_id, u.clock_ini_date, u.clock_end_date, u.department_id, u.next_of_kin_name, u.next_of_kin_phone, u.break_in, u.actual_shift_id, u.function_name, u.break_out, u.rol_id, u.status_mask, u.vehicle_id, u.user_location_id, u.user_location_name, u.city, u.country, u.iso_code, u.province, u.break_duration
  • Index Cond: (u.id = r.user_insert_id)
  • Worker 1: actual time=0.098..0.098 rows=1 loops=1
Planning time : 5.208 ms
Execution time : 5,681.375 ms