explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4BKU

Settings
# exclusive inclusive rows x rows loops node
1. 0.137 1,803.911 ↓ 55.0 55 1

Sort (cost=87,811.04..87,811.05 rows=1 width=753) (actual time=1,803.895..1,803.911 rows=55 loops=1)

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

Nested Loop Left Join (cost=16,356.94..87,811.03 rows=1 width=753) (actual time=53.506..1,803.774 rows=55 loops=1)

3. 0.103 1,803.583 ↓ 55.0 55 1

Nested Loop (cost=16,356.66..87,810.71 rows=1 width=1,090) (actual time=53.497..1,803.583 rows=55 loops=1)

4. 0.088 1,803.260 ↓ 55.0 55 1

Nested Loop (cost=16,356.24..87,809.93 rows=1 width=1,080) (actual time=53.485..1,803.260 rows=55 loops=1)

5. 0.053 1,803.117 ↓ 55.0 55 1

Nested Loop (cost=16,356.11..87,809.67 rows=1 width=1,080) (actual time=53.479..1,803.117 rows=55 loops=1)

6. 0.132 1,802.954 ↓ 55.0 55 1

Nested Loop (cost=16,355.96..87,809.50 rows=1 width=1,044) (actual time=53.471..1,802.954 rows=55 loops=1)

7. 0.270 1,802.123 ↓ 233.0 233 1

Nested Loop (cost=16,355.68..87,809.15 rows=1 width=618) (actual time=53.433..1,802.123 rows=233 loops=1)

8. 0.335 1,801.154 ↓ 233.0 233 1

Nested Loop (cost=16,355.26..87,806.25 rows=1 width=610) (actual time=53.423..1,801.154 rows=233 loops=1)

9. 0.214 1,799.887 ↓ 233.0 233 1

Nested Loop (cost=16,354.83..87,798.16 rows=1 width=262) (actual time=53.407..1,799.887 rows=233 loops=1)

10. 3.899 1,798.902 ↓ 21.4 257 1

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

11. 0.072 0.100 ↓ 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.100 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.028 ↓ 0.0 0 1

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

13. 0.014 0.014 ↓ 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.014..0.014 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. 389.268 1,794.903 ↑ 1.2 8 33

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

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

17. 62.535 62.535 ↓ 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.895..1.895 rows=21,935 loops=33)

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

Bitmap Index Scan on component_detail_wb_user_id_idx (cost=0.00..15,996.39 rows=494,394 width=0) (actual time=42.909..42.909 rows=608,501 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.220 0.220 ↑ 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.004..0.004 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.802 ms
Execution time : 1,804.871 ms