explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uB1

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.857 468.084 ↓ 293.0 293 1

Nested Loop (cost=89,945.62..95,845.18 rows=1 width=2,136) (actual time=465.258..468.084 rows=293 loops=1)

  • Output: c.title, c.unit_count, cd.unit_count, s.name, c.delivery_date, c.type, c.created_date, concat((wu.user_details ->> 'field_firstname'::text), ' ', "left"((wu.user_details ->> 'field_lastname'::text), 1)), concat((wu2.user_details ->> 'field_firstname'::text), ' ', "left"((wu2.user_details ->> 'field_lastname'::text), 1)), eu.client_type, temp1.last_allocation_date, temp1.no_of_attempts
2. 0.312 466.934 ↓ 293.0 293 1

Nested Loop (cost=89,945.48..95,844.59 rows=1 width=2,140) (actual time=465.227..466.934 rows=293 loops=1)

  • Output: eu.client_type, c.title, c.unit_count, c.delivery_date, c.type, c.created_date, cd.unit_count, wu.user_details, wu2.user_details, s.name, s.service_segment, temp1.last_allocation_date, temp1.no_of_attempts
3. 64.970 466.329 ↓ 293.0 293 1

Hash Join (cost=89,945.33..95,844.42 rows=1 width=2,110) (actual time=465.214..466.329 rows=293 loops=1)

  • Output: eu.client_type, c.title, c.unit_count, c.delivery_date, c.type, c.created_date, c.service_id, cd.unit_count, wu.user_details, wu2.user_details, temp1.last_allocation_date, temp1.no_of_attempts
  • Hash Cond: (temp1.component_detail_id = cd.id)
4. 77.683 77.683 ↑ 1.0 285,736 1

Seq Scan on pg_temp_2.temp1 (cost=0.00..4,786.60 rows=296,660 width=20) (actual time=0.034..77.683 rows=285,736 loops=1)

  • Output: temp1.last_allocation_date, temp1.no_of_attempts, temp1.component_detail_id
5. 0.458 323.676 ↓ 293.0 293 1

Hash (cost=89,945.32..89,945.32 rows=1 width=2,098) (actual time=323.676..323.676 rows=293 loops=1)

  • Output: eu.client_type, c.title, c.unit_count, c.delivery_date, c.type, c.created_date, c.service_id, cd.unit_count, cd.id, wu.user_details, wu2.user_details
  • Buckets: 1024 Batches: 1 Memory Usage: 149kB
6. 0.488 323.218 ↓ 293.0 293 1

Nested Loop (cost=6,651.10..89,945.32 rows=1 width=2,098) (actual time=157.182..323.218 rows=293 loops=1)

  • Output: eu.client_type, c.title, c.unit_count, c.delivery_date, c.type, c.created_date, c.service_id, cd.unit_count, cd.id, wu.user_details, wu2.user_details
7. 0.452 322.144 ↓ 293.0 293 1

Nested Loop (cost=6,650.81..89,944.59 rows=1 width=1,078) (actual time=157.162..322.144 rows=293 loops=1)

  • Output: eu.client_type, c.title, c.unit_count, c.delivery_date, c.type, c.created_date, c.cm_id, c.service_id, cd.unit_count, cd.id, wu2.user_details
8. 0.598 319.055 ↓ 36.6 293 1

Nested Loop (cost=6,650.39..89,938.97 rows=8 width=1,078) (actual time=157.136..319.055 rows=293 loops=1)

  • Output: e.eos_user_id, c.title, c.unit_count, c.delivery_date, c.type, c.created_date, c.cm_id, c.service_id, cd.unit_count, cd.id, wu2.user_details
  • Join Filter: ((e.eos_user_id = ((o.eos_users ->> 'eos_user'::text))::integer) OR (o.eos_users IS NULL))
  • Rows Removed by Join Filter: 16
9. 0.333 315.676 ↓ 34.3 309 1

Nested Loop (cost=6,649.97..89,924.88 rows=9 width=1,108) (actual time=157.106..315.676 rows=309 loops=1)

  • Output: c.title, c.unit_count, c.delivery_date, c.type, c.created_date, c.enquiry_id, c.cm_id, c.service_id, cd.unit_count, cd.id, o.eos_users, wu2.user_details
10. 0.251 314.103 ↓ 11.9 310 1

Nested Loop (cost=6,649.68..89,905.67 rows=26 width=88) (actual time=157.075..314.103 rows=310 loops=1)

  • Output: c.title, c.unit_count, c.delivery_date, c.type, c.created_date, c.enquiry_id, c.cm_id, c.pm_id, c.service_id, cd.unit_count, cd.id, o.eos_users
  • Join Filter: (cd.component_id = c.id)
11. 0.393 309.148 ↓ 1.7 392 1

Nested Loop (cost=6,649.25..89,336.84 rows=227 width=46) (actual time=57.100..309.148 rows=392 loops=1)

  • Output: cd.unit_count, cd.component_id, cd.id, o.component_id, o.eos_users
12. 0.385 305.425 ↓ 1.3 370 1

Hash Join (cost=6,648.83..88,999.06 rows=280 width=12) (actual time=57.072..305.425 rows=370 loops=1)

  • Output: cd.unit_count, cd.component_id, cd.id
  • Hash Cond: (cd.process_service_mapping_id = psm.id)
13. 259.209 304.677 ↑ 352.2 475 1

Bitmap Heap Scan on public.component_detail cd (cost=6,616.85..88,336.99 rows=167,279 width=16) (actual time=56.694..304.677 rows=475 loops=1)

  • Output: cd.id, cd.enquiry_id, cd.component_id, cd.wb_user_id, cd.process_service_mapping_id, cd.bucket_id, cd.unit_count, cd.instruction, cd.comment, cd.process_completion, cd.actual_start_date, cd.actual_end_date, cd.expected_start_date, cd.expected_end_date, cd.active, cd.created_by, cd.last_modified_by, cd.created_date, cd.last_modified_date, cd.unitcount
  • Recheck Cond: (cd.wb_user_id IS NULL)
  • Filter: (cd.active AND cd.active)
  • Rows Removed by Filter: 228182
  • Heap Blocks: exact=49892
14. 45.468 45.468 ↓ 1.6 375,938 1

Bitmap Index Scan on component_detail_wb_user_id_idx (cost=0.00..6,575.03 rows=228,614 width=0) (actual time=45.468..45.468 rows=375,938 loops=1)

  • Index Cond: (cd.wb_user_id IS NULL)
15. 0.018 0.363 ↓ 79.0 79 1

Hash (cost=31.96..31.96 rows=1 width=4) (actual time=0.363..0.363 rows=79 loops=1)

  • Output: psm.id
  • Buckets: 1024 Batches: 1 Memory Usage: 3kB
16. 0.345 0.345 ↓ 79.0 79 1

Seq Scan on public.process_service_mapping psm (cost=0.00..31.96 rows=1 width=4) (actual time=0.019..0.345 rows=79 loops=1)

  • Output: psm.id
  • Filter: (psm.active AND ("position"((psm.internal_name)::text, 'Edit'::text) > 0) AND ("position"((psm.internal_name)::text, 'Review'::text) = 0))
  • Rows Removed by Filter: 519
17. 3.330 3.330 ↑ 1.0 1 370

Index Scan using orders_component_id_index on public.orders o (cost=0.43..1.20 rows=1 width=34) (actual time=0.008..0.009 rows=1 loops=370)

  • Output: o.component_id, o.eos_users
  • Index Cond: (o.component_id = cd.component_id)
  • Filter: (o.active AND (o.status = 'quote-accepted'::order_status))
  • Rows Removed by Filter: 0
18. 4.704 4.704 ↑ 1.0 1 392

Index Scan using component_pkey on public.component c (cost=0.43..2.49 rows=1 width=54) (actual time=0.011..0.012 rows=1 loops=392)

  • Output: c.title, c.unit_count, c.delivery_date, c.type, c.created_date, c.enquiry_id, c.id, c.cm_id, c.pm_id, c.service_id
  • Index Cond: (c.id = o.component_id)
  • Filter: (c.active AND (c.sent_to_client_date IS NULL) AND (c.title IS NOT NULL) AND (c.type <> 'test'::component_type) AND (c.pm_id <> 130) AND (c.component_type = 'job'::component_entity_type))
  • Rows Removed by Filter: 0
19. 1.240 1.240 ↑ 1.0 1 310

Index Scan using wb_user_pkey on public.wb_user wu2 (cost=0.29..0.73 rows=1 width=1,028) (actual time=0.004..0.004 rows=1 loops=310)

  • Output: wu2.id, wu2.email_id, wu2.password, wu2.salt, wu2.roles, wu2.user_details, wu2.timezone, wu2.active, wu2.created_date, wu2.last_modified_date, wu2.status, wu2.nickname, wu2.language
  • Index Cond: (wu2.id = c.pm_id)
  • Filter: ("position"((wu2.roles ->> 'roles'::text), '"157"'::text) > 0)
  • Rows Removed by Filter: 0
20. 2.781 2.781 ↑ 1.0 1 309

Index Scan using enquiry_pkey on public.enquiry e (cost=0.42..1.54 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=309)

  • Output: e.id, e.title, e.eos_user_id, e.service_id, e.cm_id, e.pm_id, e.invoice_type, e.source_url, e.ip, e.language_style, e.status, e.data, e.client_type, e.partner_id, e.currency_code, e.active, e.created_by, e.created_date, e.last_modified_by, e.last_modified_date
  • Index Cond: (e.id = c.enquiry_id)
21. 2.637 2.637 ↑ 1.0 1 293

Index Scan using eos_user_pkey on public.eos_user eu (cost=0.42..0.69 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=293)

  • Output: eu.id, eu.email_id, eu.password, eu.salt, eu.partner_id, eu.network_id, eu.client_code, eu.client_type, eu.enquiry_count, eu.job_count, eu.question_count, eu.invoice_frequency, eu.roles, eu.user_profile, eu.active, eu.created_date, eu.last_modified_date, eu.status, eu.pub_profile, eu.timezone, eu.client_code_id, eu.language, eu.type
  • Index Cond: (eu.id = e.eos_user_id)
  • Filter: ("position"((eu.email_id)::text, 'mailinator.com'::text) = 0)
22. 0.586 0.586 ↑ 1.0 1 293

Index Scan using wb_user_pkey on public.wb_user wu (cost=0.29..0.72 rows=1 width=1,028) (actual time=0.002..0.002 rows=1 loops=293)

  • Output: wu.id, wu.email_id, wu.password, wu.salt, wu.roles, wu.user_details, wu.timezone, wu.active, wu.created_date, wu.last_modified_date, wu.status, wu.nickname, wu.language
  • Index Cond: (wu.id = c.cm_id)
23. 0.293 0.293 ↑ 1.0 1 293

Index Scan using service_pkey on public.service s (cost=0.14..0.16 rows=1 width=38) (actual time=0.001..0.001 rows=1 loops=293)

  • Output: s.id, s.name, s.weight, s.active, s.created_by, s.created_date, s.last_modified_by, s.last_modified_date, s.service_segment, s.data
  • Index Cond: (s.id = c.service_id)
  • Filter: s.active
24. 0.293 0.293 ↑ 1.0 1 293

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

  • Output: ss.id, ss.name, ss.weight, ss.active, ss.created_by, ss.created_date, ss.last_modified_by, ss.last_modified_date
  • Index Cond: (ss.id = s.service_segment)
  • Filter: ss.active
Planning time : 6.312 ms
Execution time : 468.720 ms