explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fILp

Settings
# exclusive inclusive rows x rows loops node
1. 10.956 8,224.931 ↑ 2.0 1 1

Gather (cost=17,256.50..26,023.51 rows=2 width=1,487) (actual time=8,223.069..8,224.931 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 7,986.414 ↓ 0.0 0 1

Limit (cost=0.00..74.85 rows=1 width=576) (actual time=7,986.414..7,986.414 rows=0 loops=1)

  • Output: requests.data
4. 0.010 7,986.412 ↓ 0.0 0 1

Append (cost=0.00..34,055.17 rows=455 width=576) (actual time=7,986.411..7,986.412 rows=0 loops=1)

5. 465.964 465.964 ↓ 0.0 0 1

Seq Scan on public.requests (cost=0.00..16,102.17 rows=2 width=167) (actual time=465.964..465.964 rows=0 loops=1)

  • Output: requests.data
  • Filter: ((requests.active = 1) AND (((requests.data)::json ->> 'station'::text) = 'TFN'::text))
  • Rows Removed by Filter: 452
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.006 0.006 ↓ 0.0 0 1

Seq Scan on public.requests_door_to_door (cost=0.00..14.05 rows=1 width=32) (actual time=0.006..0.006 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.003 0.003 ↓ 0.0 0 1

Seq Scan on public.requests_flights_notifications (cost=0.00..14.05 rows=1 width=32) (actual time=0.003..0.003 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. 47.162 47.162 ↓ 0.0 0 1

Seq Scan on public.requests_incidents (cost=0.00..428.11 rows=35 width=189) (actual time=47.162..47.162 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. 13.522 13.522 ↓ 0.0 0 1

Seq Scan on public.requests_notifications (cost=0.00..32.68 rows=1 width=584) (actual time=13.522..13.522 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.013 0.013 ↓ 0.0 0 1

Seq Scan on public.requests_service_requirements (cost=0.00..14.05 rows=1 width=32) (actual time=0.012..0.013 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.073 0.073 ↓ 0.0 0 1

Seq Scan on public.requests_work_orders (cost=0.00..14.05 rows=1 width=32) (actual time=0.073..0.073 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. 7,459.654 7,459.654 ↓ 0.0 0 1

Seq Scan on public.requests_admin_close (cost=0.00..17,421.94 rows=412 width=617) (actual time=7,459.654..7,459.654 rows=0 loops=1)

  • Output: requests_admin_close.data
  • Filter: ((requests_admin_close.active = 1) AND (((requests_admin_close.data)::json ->> 'station'::text) = 'TFN'::text))
  • Rows Removed by Filter: 82,486
14. 0.059 227.561 ↓ 0.0 0 3 / 3

Nested Loop Left Join (cost=16,181.65..24,948.47 rows=1 width=1,487) (actual time=201.063..227.561 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=149.142..228.635 rows=1 loops=1
  • Worker 1: actual time=219.302..219.302 rows=0 loops=1
15. 45.808 227.457 ↓ 0.0 0 3 / 3

Parallel Hash Join (cost=16,181.37..24,948.13 rows=1 width=1,387) (actual time=200.960..227.457 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=148.835..228.325 rows=1 loops=1
  • Worker 1: actual time=219.301..219.302 rows=0 loops=1
16. 65.485 65.485 ↑ 1.2 130,487 3 / 3

Parallel Seq Scan on public.routes_services_cabin rsc (cost=0.00..8,155.63 rows=162,963 width=12) (actual time=0.036..65.485 rows=130,487 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.051..80.723 rows=137,262 loops=1
  • Worker 1: actual time=0.041..42.565 rows=134,844 loops=1
17. 1.310 116.164 ↓ 0.0 0 3 / 3

Parallel Hash (cost=16,181.36..16,181.36 rows=1 width=1,383) (actual time=116.163..116.164 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=119.708..119.708 rows=0 loops=1
  • Worker 1: actual time=106.688..106.688 rows=0 loops=1
18. 5.767 114.854 ↓ 0.0 0 3 / 3

Hash Semi Join (cost=9.75..16,181.36 rows=1 width=1,383) (actual time=114.311..114.854 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=115.899..115.899 rows=0 loops=1
  • Worker 1: actual time=106.634..106.635 rows=0 loops=1
19. 109.039 109.039 ↑ 1.2 27,452 3 / 3

Parallel Seq Scan on public.requests_admin_close r (cost=0.00..16,081.54 rows=34,308 width=1,379) (actual time=0.027..109.039 rows=27,452 loops=3)

  • Output: r.id, r.service_type, r.type, r.sub_type, r.data, r.concept, r.status, r.status_date, r.publish_date, r.due_date, r.created_at, r.active, r.user_insert_id, r.customers_contracts_services_id, r.concept_id, r.parent_id
  • Filter: (((r.type)::text = 'ADMIN_CLOSE'::text) AND (r.active = 1))
  • Rows Removed by Filter: 44
  • Worker 0: actual time=0.027..109.767 rows=27,559 loops=1
  • Worker 1: actual time=0.032..102.116 rows=21,554 loops=1
20. 0.007 0.048 ↑ 2.7 3 3 / 3

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

  • Output: routes_services_cabin.id
  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Worker 0: actual time=0.059..0.059 rows=3 loops=1
  • Worker 1: actual time=0.042..0.043 rows=3 loops=1
21. 0.041 0.041 ↑ 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.039..0.041 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.048..0.051 rows=3 loops=1
  • Worker 1: actual time=0.032..0.035 rows=3 loops=1
22. 0.045 0.045 ↑ 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.136..0.136 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 0: actual time=0.136..0.136 rows=1 loops=1
Planning time : 13.430 ms
Execution time : 8,225.208 ms