explain.depesz.com

PostgreSQL's explain analyze made readable

Result: huLj : Optimization for: plan #wKs

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.175 169.069 ↓ 56.0 56 1

Nested Loop Left Join (cost=92,529.93..92,647.05 rows=1 width=686) (actual time=150.189..169.069 rows=56 loops=1)

2.          

CTE cdd

3. 0.000 152.490 ↑ 3.2 1,196 1

Gather (cost=1,000.00..92,517.74 rows=3,823 width=296) (actual time=142.679..152.490 rows=1,196 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
4. 161.896 161.896 ↑ 4.0 239 5 / 5

Parallel Seq Scan on component_detail (cost=0.00..91,135.44 rows=956 width=296) (actual time=120.915..161.896 rows=239 loops=5)

  • Filter: ((wb_user_id IS NULL) AND (expected_end_date > '2019-09-26 12:01:44'::timestamp without time zone))
  • Rows Removed by Filter: 755,029
5. 0.128 168.782 ↓ 56.0 56 1

Nested Loop (cost=11.91..128.99 rows=1 width=1,023) (actual time=150.167..168.782 rows=56 loops=1)

6. 0.188 168.318 ↓ 56.0 56 1

Nested Loop (cost=11.48..128.21 rows=1 width=1,013) (actual time=150.144..168.318 rows=56 loops=1)

  • Join Filter: (s.service_segment = ss.id)
  • Rows Removed by Join Filter: 112
7. 0.142 168.018 ↓ 56.0 56 1

Nested Loop (cost=11.48..127.00 rows=1 width=1,013) (actual time=150.130..168.018 rows=56 loops=1)

8. 0.362 167.764 ↓ 56.0 56 1

Nested Loop (cost=11.34..126.84 rows=1 width=977) (actual time=150.112..167.764 rows=56 loops=1)

9. 0.309 166.462 ↓ 235.0 235 1

Nested Loop (cost=11.06..126.49 rows=1 width=551) (actual time=149.686..166.462 rows=235 loops=1)

10. 0.517 164.978 ↓ 235.0 235 1

Nested Loop (cost=10.63..123.59 rows=1 width=543) (actual time=149.666..164.978 rows=235 loops=1)

11. 0.392 163.521 ↓ 235.0 235 1

Nested Loop (cost=10.21..115.14 rows=1 width=485) (actual time=149.648..163.521 rows=235 loops=1)

12. 5.394 161.162 ↓ 281.0 281 1

Nested Loop (cost=9.78..106.69 rows=1 width=137) (actual time=149.618..161.162 rows=281 loops=1)

  • Join Filter: (cd.process_service_mapping_id = psm.id)
  • Rows Removed by Join Filter: 10,162
13. 151.840 151.840 ↓ 163.7 491 1

CTE Scan on cdd cd (cost=0.00..86.02 rows=3 width=80) (actual time=149.425..151.840 rows=491 loops=1)

  • Filter: (active AND (wb_user_id IS NULL) AND (expected_end_date > '2019-09-26 12:01:44'::timestamp without time zone))
  • Rows Removed by Filter: 705
14. 3.819 3.928 ↓ 5.2 21 491

Materialize (cost=9.78..20.50 rows=4 width=57) (actual time=0.001..0.008 rows=21 loops=491)

15. 0.057 0.109 ↓ 8.2 33 1

Bitmap Heap Scan on process_service_mapping psm (cost=9.78..20.48 rows=4 width=57) (actual time=0.062..0.109 rows=33 loops=1)

  • Recheck Cond: ((process_id = 26,627) AND (edit_type = 'pes'::service_edit_type))
  • Filter: active
  • Heap Blocks: exact=13
16. 0.006 0.052 ↓ 0.0 0 1

BitmapAnd (cost=9.78..9.78 rows=4 width=0) (actual time=0.051..0.052 rows=0 loops=1)

17. 0.029 0.029 ↓ 1.1 66 1

Bitmap Index Scan on process_service_mapping_process_id_index (cost=0.00..4.74 rows=62 width=0) (actual time=0.029..0.029 rows=66 loops=1)

  • Index Cond: (process_id = 26,627)
18. 0.017 0.017 ↓ 1.0 70 1

Bitmap Index Scan on psm_edit_type_idx (cost=0.00..4.79 rows=69 width=0) (actual time=0.016..0.017 rows=70 loops=1)

  • Index Cond: (edit_type = 'pes'::service_edit_type)
19. 1.967 1.967 ↑ 1.0 1 281

Index Scan using component_pkey on component c (cost=0.43..8.46 rows=1 width=352) (actual time=0.007..0.007 rows=1 loops=281)

  • Index Cond: (id = cd.component_id)
  • Filter: (active AND (title IS NOT NULL) AND (file_type = ANY ('{default,tex}'::allocation_file_type[])) AND (status <> 'abandoned'::component_status) AND (component_type = 'job'::component_entity_type) AND (allocation_type = 'job_board'::allocation_type))
  • Rows Removed by Filter: 0
20. 0.940 0.940 ↑ 1.0 1 235

Index Scan using component_detail_extended_component_detail_id_index on component_detail_extended cde (cost=0.42..8.44 rows=1 width=62) (actual time=0.004..0.004 rows=1 loops=235)

  • Index Cond: (component_detail_id = cd.id)
  • Filter: active
21. 1.175 1.175 ↑ 1.0 1 235

Index Scan using enquiry_pkey on enquiry e (cost=0.43..2.90 rows=1 width=12) (actual time=0.005..0.005 rows=1 loops=235)

  • Index Cond: (id = c.enquiry_id)
  • Filter: active
22. 0.940 0.940 ↓ 0.0 0 235

Index Scan using subject_area_pkey on subject_area sa (cost=0.28..0.35 rows=1 width=434) (actual time=0.004..0.004 rows=0 loops=235)

  • Index Cond: (id = c.subject_area_id)
  • Filter: (active AND (((data ->> 'sa1_6'::text))::integer = ANY ('{68,67,135,70,69,71,72,157,156,65,146,131,147,132,73,130,78,74,76,77,149,150,79,129,159,75,133,66,155,148,134}'::integer[])))
  • Rows Removed by Filter: 1
23. 0.112 0.112 ↑ 1.0 1 56

Index Scan using service_pkey on service s (cost=0.14..0.16 rows=1 width=40) (actual time=0.002..0.002 rows=1 loops=56)

  • Index Cond: (id = c.service_id)
  • Filter: active
24. 0.112 0.112 ↑ 3.0 3 56

Seq Scan on service_segment ss (cost=0.00..1.09 rows=9 width=4) (actual time=0.001..0.002 rows=3 loops=56)

  • Filter: active
25. 0.336 0.336 ↑ 1.0 1 56

Index Scan using eos_user_pkey on eos_user eu (cost=0.42..0.78 rows=1 width=14) (actual time=0.006..0.006 rows=1 loops=56)

  • Index Cond: (id = e.eos_user_id)
  • Filter: active
26. 0.112 0.112 ↓ 0.0 0 56

Index Scan using booking_enquiry_id_index on booking b (cost=0.28..0.30 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=56)

  • Index Cond: (enquiry_id = e.id)
  • Filter: (active AND ((status)::text = 'confirmed'::text) AND (accepted_by = 23,180))
  • Rows Removed by Filter: 0
Planning time : 2.965 ms
Execution time : 171.192 ms