explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tECj

Settings
# exclusive inclusive rows x rows loops node
1. 0.063 1,868.273 ↓ 19.0 19 1

Sort (cost=88,035.21..88,035.22 rows=1 width=753) (actual time=1,868.267..1,868.273 rows=19 loops=1)

  • Sort Key: cd.expected_end_date
  • Sort Method: quicksort Memory: 40kB
2. 0.044 1,868.210 ↓ 19.0 19 1

Nested Loop Left Join (cost=16,412.94..88,035.20 rows=1 width=753) (actual time=50.469..1,868.210 rows=19 loops=1)

3. 0.039 1,868.128 ↓ 19.0 19 1

Nested Loop (cost=16,412.66..88,034.88 rows=1 width=1,102) (actual time=50.459..1,868.128 rows=19 loops=1)

4. 0.023 1,867.994 ↓ 19.0 19 1

Nested Loop (cost=16,412.24..88,034.10 rows=1 width=1,092) (actual time=50.448..1,867.994 rows=19 loops=1)

5. 0.030 1,867.933 ↓ 19.0 19 1

Nested Loop (cost=16,412.11..88,033.84 rows=1 width=1,092) (actual time=50.441..1,867.933 rows=19 loops=1)

6. 0.085 1,867.865 ↓ 19.0 19 1

Nested Loop (cost=16,411.96..88,033.67 rows=1 width=1,056) (actual time=50.433..1,867.865 rows=19 loops=1)

7. 0.248 1,867.249 ↓ 177.0 177 1

Nested Loop (cost=16,411.68..88,033.35 rows=1 width=618) (actual time=50.421..1,867.249 rows=177 loops=1)

8. 0.124 1,866.470 ↓ 177.0 177 1

Nested Loop (cost=16,411.26..88,030.72 rows=1 width=610) (actual time=50.412..1,866.470 rows=177 loops=1)

9. 0.190 1,865.461 ↓ 177.0 177 1

Nested Loop (cost=16,410.83..88,022.62 rows=1 width=262) (actual time=50.398..1,865.461 rows=177 loops=1)

10. 3.919 1,864.548 ↓ 20.1 241 1

Nested Loop (cost=16,410.41..87,962.56 rows=12 width=204) (actual time=50.386..1,864.548 rows=241 loops=1)

11. 0.067 0.089 ↓ 8.2 33 1

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

  • Recheck Cond: ((process_id = 26627) AND (edit_type = 'pes'::service_edit_type))
  • Filter: active
  • Heap Blocks: exact=13
12. 0.002 0.022 ↓ 0.0 0 1

BitmapAnd (cost=9.78..9.78 rows=4 width=0) (actual time=0.022..0.022 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.010..0.011 rows=66 loops=1)

  • Index Cond: (process_id = 26627)
14. 0.009 0.009 ↓ 1.0 70 1

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

  • Index Cond: (edit_type = 'pes'::service_edit_type)
15. 397.122 1,860.540 ↑ 1.4 7 33

Bitmap Heap Scan on component_detail cd (cost=16,400.62..21,985.42 rows=10 width=147) (actual time=55.719..56.380 rows=7 loops=33)

  • Recheck Cond: ((process_service_mapping_id = psm.id) AND (wb_user_id IS NULL))
  • Rows Removed by Index Recheck: 47948
  • Filter: (active AND (expected_end_date > '2019-09-27 12:01:29'::timestamp without time zone))
  • Rows Removed by Filter: 6049
  • Heap Blocks: exact=93513 lossy=32929
16. 20.552 1,463.418 ↓ 0.0 0 33

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

17. 62.436 62.436 ↓ 1.8 22,211 33

Bitmap Index Scan on component_detail_process_service_mapping_idx (cost=0.00..351.21 rows=12,237 width=0) (actual time=1.892..1.892 rows=22,211 loops=33)

  • Index Cond: (process_service_mapping_id = psm.id)
18. 1,380.430 1,380.430 ↓ 1.3 622,118 31

Bitmap Index Scan on component_detail_wb_user_id_idx (cost=0.00..16,048.39 rows=494,394 width=0) (actual time=44.530..44.530 rows=622,118 loops=31)

  • Index Cond: (wb_user_id IS NULL)
19. 0.723 0.723 ↑ 1.0 1 241

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

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

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

  • 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.531 0.531 ↑ 1.0 1 177

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

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

Index Scan using subject_area_pkey on subject_area sa (cost=0.28..0.32 rows=1 width=446) (actual time=0.003..0.003 rows=0 loops=177)

  • Index Cond: (id = c.subject_area_id)
  • Filter: (active AND (((data ->> 'sa1_6'::text))::integer = ANY ('{68,67,135,70,69,71,72,157}'::integer[])))
  • Rows Removed by Filter: 1
23. 0.038 0.038 ↑ 1.0 1 19

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

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

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

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

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

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

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

  • Index Cond: (enquiry_id = e.id)
  • Filter: (active AND ((status)::text = 'confirmed'::text) AND (accepted_by = 23985))
Planning time : 3.612 ms
Execution time : 1,868.457 ms