explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wKs

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.139 1,843.784 ↓ 53.0 53 1

Sort (cost=87,799.68..87,799.69 rows=1 width=753) (actual time=1,843.769..1,843.784 rows=53 loops=1)

  • Sort Key: cd.expected_end_date
  • Sort Method: quicksort Memory: 71kB
2. 0.111 1,843.645 ↓ 53.0 53 1

Nested Loop Left Join (cost=16,352.95..87,799.67 rows=1 width=753) (actual time=55.508..1,843.645 rows=53 loops=1)

3. 0.096 1,843.428 ↓ 53.0 53 1

Nested Loop (cost=16,352.67..87,799.36 rows=1 width=1,090) (actual time=55.498..1,843.428 rows=53 loops=1)

4. 0.096 1,843.120 ↓ 53.0 53 1

Nested Loop (cost=16,352.25..87,798.57 rows=1 width=1,080) (actual time=55.486..1,843.120 rows=53 loops=1)

5. 0.063 1,842.971 ↓ 53.0 53 1

Nested Loop (cost=16,352.11..87,798.31 rows=1 width=1,080) (actual time=55.479..1,842.971 rows=53 loops=1)

6. 0.157 1,842.802 ↓ 53.0 53 1

Nested Loop (cost=16,351.97..87,798.15 rows=1 width=1,044) (actual time=55.472..1,842.802 rows=53 loops=1)

7. 0.331 1,841.952 ↓ 231.0 231 1

Nested Loop (cost=16,351.69..87,797.79 rows=1 width=618) (actual time=55.461..1,841.952 rows=231 loops=1)

8. 0.212 1,840.928 ↓ 231.0 231 1

Nested Loop (cost=16,351.26..87,794.90 rows=1 width=610) (actual time=55.452..1,840.928 rows=231 loops=1)

9. 0.265 1,839.561 ↓ 231.0 231 1

Nested Loop (cost=16,350.84..87,786.80 rows=1 width=262) (actual time=55.435..1,839.561 rows=231 loops=1)

10. 3.978 1,838.453 ↓ 21.6 281 1

Nested Loop (cost=16,350.41..87,722.59 rows=13 width=204) (actual time=55.254..1,838.453 rows=281 loops=1)

11. 0.075 0.104 ↓ 8.2 33 1

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

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

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

13. 0.015 0.015 ↓ 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.015..0.015 rows=66 loops=1)

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

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

  • Index Cond: (edit_type = 'pes'::service_edit_type)
15. 403.062 1,834.371 ↑ 1.1 9 33

Bitmap Heap Scan on component_detail cd (cost=16,340.63..21,925.43 rows=10 width=147) (actual time=54.939..55.587 rows=9 loops=33)

  • Recheck Cond: ((process_service_mapping_id = psm.id) AND (wb_user_id IS NULL))
  • Rows Removed by Index Recheck: 47,738
  • Filter: (active AND (expected_end_date > '2019-09-26 12:01:44'::timestamp without time zone))
  • Rows Removed by Filter: 6,016
  • Heap Blocks: exact=93,014 lossy=32,929
16. 13.028 1,431.309 ↓ 0.0 0 33

BitmapAnd (cost=16,340.63..16,340.63 rows=1,616 width=0) (actual time=43.373..43.373 rows=0 loops=33)

17. 64.449 64.449 ↓ 1.8 21,905 33

Bitmap Index Scan on component_detail_process_service_mapping_idx (cost=0.00..347.21 rows=12,237 width=0) (actual time=1.953..1.953 rows=21,905 loops=33)

  • Index Cond: (process_service_mapping_id = psm.id)
18. 1,353.832 1,353.832 ↓ 1.2 606,724 31

Bitmap Index Scan on component_detail_wb_user_id_idx (cost=0.00..15,992.39 rows=494,394 width=0) (actual time=43.672..43.672 rows=606,724 loops=31)

  • Index Cond: (wb_user_id IS NULL)
19. 0.843 0.843 ↑ 1.0 1 281

Index Scan using component_detail_extended_component_detail_id_index on component_detail_extended cde (cost=0.42..4.93 rows=1 width=62) (actual time=0.003..0.003 rows=1 loops=281)

  • Index Cond: (component_detail_id = cd.id)
  • Filter: active
20. 1.155 1.155 ↑ 1.0 1 231

Index Scan using component_pkey on component c (cost=0.43..8.09 rows=1 width=352) (actual time=0.005..0.005 rows=1 loops=231)

  • 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))
21. 0.693 0.693 ↑ 1.0 1 231

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

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

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

  • 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.106 0.106 ↑ 1.0 1 53

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=53)

  • Index Cond: (id = c.service_id)
  • Filter: active
24. 0.053 0.053 ↑ 1.0 1 53

Index Scan using service_segment_pkey on service_segment ss (cost=0.14..0.24 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=53)

  • Index Cond: (id = s.service_segment)
  • Filter: active
25. 0.212 0.212 ↑ 1.0 1 53

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

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

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=53)

  • Index Cond: (enquiry_id = e.id)
  • Filter: (active AND ((status)::text = 'confirmed'::text) AND (accepted_by = 23,180))
Planning time : 3.931 ms
Execution time : 1,844.769 ms