explain.depesz.com

PostgreSQL's explain analyze made readable

Result: y4w8

Settings
# exclusive inclusive rows x rows loops node
1. 0.182 14,587.993 ↓ 57.0 57 1

Sort (cost=87,815.72..87,815.72 rows=1 width=753) (actual time=14,587.977..14,587.993 rows=57 loops=1)

  • Sort Key: cd.expected_end_date
  • Sort Method: quicksort Memory: 71kB
2. 0.104 14,587.811 ↓ 57.0 57 1

Nested Loop Left Join (cost=16,356.95..87,815.71 rows=1 width=753) (actual time=12,747.650..14,587.811 rows=57 loops=1)

3. 0.072 14,587.593 ↓ 57.0 57 1

Nested Loop (cost=16,356.67..87,815.39 rows=1 width=1,090) (actual time=12,747.633..14,587.593 rows=57 loops=1)

4. 0.103 14,587.236 ↓ 57.0 57 1

Nested Loop (cost=16,356.25..87,814.60 rows=1 width=1,080) (actual time=12,747.620..14,587.236 rows=57 loops=1)

5. 0.067 14,587.076 ↓ 57.0 57 1

Nested Loop (cost=16,356.11..87,814.34 rows=1 width=1,080) (actual time=12,747.611..14,587.076 rows=57 loops=1)

6. 0.278 14,586.895 ↓ 57.0 57 1

Nested Loop (cost=16,355.97..87,814.18 rows=1 width=1,044) (actual time=12,747.601..14,586.895 rows=57 loops=1)

7. 0.329 14,585.437 ↓ 236.0 236 1

Nested Loop (cost=16,355.69..87,813.83 rows=1 width=618) (actual time=12,747.530..14,585.437 rows=236 loops=1)

8. 0.373 14,584.400 ↓ 236.0 236 1

Nested Loop (cost=16,355.26..87,810.93 rows=1 width=610) (actual time=12,747.518..14,584.400 rows=236 loops=1)

9. 0.319 14,582.847 ↓ 236.0 236 1

Nested Loop (cost=16,354.84..87,802.84 rows=1 width=262) (actual time=12,747.498..14,582.847 rows=236 loops=1)

10. 3.397 14,536.432 ↓ 20.6 268 1

Nested Loop (cost=16,354.41..87,738.59 rows=13 width=204) (actual time=12,747.022..14,536.432 rows=268 loops=1)

11. 0.076 2.574 ↓ 8.2 33 1

Bitmap Heap Scan on process_service_mapping psm (cost=9.78..20.48 rows=4 width=57) (actual time=2.505..2.574 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 2.498 ↓ 0.0 0 1

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

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

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

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

  • Index Cond: (edit_type = 'pes'::service_edit_type)
15. 392.832 14,530.461 ↑ 1.2 8 33

Bitmap Heap Scan on component_detail cd (cost=16,344.63..21,929.43 rows=10 width=147) (actual time=439.669..440.317 rows=8 loops=33)

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

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

17. 75.537 75.537 ↓ 1.8 21,935 33

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

  • Index Cond: (process_service_mapping_id = psm.id)
18. 14,048.332 14,048.332 ↓ 1.2 608,498 31

Bitmap Index Scan on component_detail_wb_user_id_idx (cost=0.00..15,996.39 rows=494,394 width=0) (actual time=453.172..453.172 rows=608,498 loops=31)

  • Index Cond: (wb_user_id IS NULL)
19. 46.096 46.096 ↑ 1.0 1 268

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.172..0.172 rows=1 loops=268)

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

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

  • 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.708 0.708 ↑ 1.0 1 236

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

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

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

  • 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.114 0.114 ↑ 1.0 1 57

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

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

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

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

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

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

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

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