explain.depesz.com

PostgreSQL's explain analyze made readable

Result: zK3

Settings
# exclusive inclusive rows x rows loops node
1. 0.138 1,870.823 ↓ 55.0 55 1

Sort (cost=87,811.06..87,811.06 rows=1 width=753) (actual time=1,870.806..1,870.823 rows=55 loops=1)

  • Sort Key: cd.expected_end_date
  • Sort Method: quicksort Memory: 69kB
2. 0.080 1,870.685 ↓ 55.0 55 1

Nested Loop Left Join (cost=16,356.94..87,811.05 rows=1 width=753) (actual time=54.258..1,870.685 rows=55 loops=1)

3. 0.058 1,870.495 ↓ 55.0 55 1

Nested Loop (cost=16,356.66..87,810.73 rows=1 width=1,090) (actual time=54.247..1,870.495 rows=55 loops=1)

4. 0.093 1,870.162 ↓ 55.0 55 1

Nested Loop (cost=16,356.24..87,809.95 rows=1 width=1,080) (actual time=54.235..1,870.162 rows=55 loops=1)

5. 0.051 1,870.014 ↓ 55.0 55 1

Nested Loop (cost=16,356.11..87,809.68 rows=1 width=1,080) (actual time=54.227..1,870.014 rows=55 loops=1)

6. 0.134 1,869.853 ↓ 55.0 55 1

Nested Loop (cost=16,355.96..87,809.52 rows=1 width=1,044) (actual time=54.218..1,869.853 rows=55 loops=1)

7. 0.268 1,869.020 ↓ 233.0 233 1

Nested Loop (cost=16,355.68..87,809.17 rows=1 width=618) (actual time=54.179..1,869.020 rows=233 loops=1)

8. 0.329 1,868.053 ↓ 233.0 233 1

Nested Loop (cost=16,355.26..87,806.27 rows=1 width=610) (actual time=54.168..1,868.053 rows=233 loops=1)

9. 0.264 1,866.792 ↓ 233.0 233 1

Nested Loop (cost=16,354.83..87,798.17 rows=1 width=262) (actual time=54.150..1,866.792 rows=233 loops=1)

10. 4.144 1,865.757 ↓ 21.4 257 1

Nested Loop (cost=16,354.41..87,738.56 rows=12 width=204) (actual time=54.134..1,865.757 rows=257 loops=1)

11. 0.087 0.116 ↓ 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.116 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. 388.377 1,861.497 ↑ 1.2 8 33

Bitmap Heap Scan on component_detail cd (cost=16,344.62..21,929.42 rows=10 width=147) (actual time=55.782..56.409 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-27 13:01:44'::timestamp without time zone))
  • Rows Removed by Filter: 6,019
  • Heap Blocks: exact=93,054 lossy=32,929
16. 14.179 1,473.120 ↓ 0.0 0 33

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

17. 63.228 63.228 ↓ 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=1.916..1.916 rows=21,935 loops=33)

  • Index Cond: (process_service_mapping_id = psm.id)
18. 1,395.713 1,395.713 ↓ 1.2 608,504 31

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

  • Index Cond: (wb_user_id IS NULL)
19. 0.771 0.771 ↑ 1.0 1 257

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

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

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

  • 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.699 0.699 ↑ 1.0 1 233

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

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

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

  • 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.110 0.110 ↑ 1.0 1 55

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

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

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

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

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

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

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

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