explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kEu

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 87,774.577 ↓ 0.0 0 1

Limit (cost=19,725.14..19,725.17 rows=1 width=216) (actual time=87,774.577..87,774.577 rows=0 loops=1)

2. 0.001 87,774.575 ↓ 0.0 0 1

Unique (cost=19,725.14..19,725.17 rows=1 width=216) (actual time=87,774.575..87,774.575 rows=0 loops=1)

3. 0.028 87,774.574 ↓ 0.0 0 1

Sort (cost=19,725.14..19,725.14 rows=1 width=216) (actual time=87,774.574..87,774.574 rows=0 loops=1)

  • Sort Key: (change_tz(sl.reporting_date, 'UTC'::character varying, tz.time_zone)), sl.id, sl.client_entity_seq_id, sl.name, sl.status_id, ct.id, ct.name, rn.id, rn.name, tz.id, (array_to_string(array_agg(DISTINCT ct_1.name), ', '::text)), (arr (...)
  • Sort Method: quicksort Memory: 25kB
4. 0.001 87,774.546 ↓ 0.0 0 1

Group (cost=19,724.86..19,725.13 rows=1 width=216) (actual time=87,774.546..87,774.546 rows=0 loops=1)

  • Group Key: sl.id, tz.id, ct.id, rn.id, (array_to_string(array_agg(DISTINCT ct_1.name), ', '::text)), (array_to_string(array_agg(DISTINCT cst.name), ', '::text))
5. 0.010 87,774.545 ↓ 0.0 0 1

Sort (cost=19,724.86..19,724.86 rows=1 width=224) (actual time=87,774.545..87,774.545 rows=0 loops=1)

  • Sort Key: sl.id, tz.id, ct.id, rn.id, (array_to_string(array_agg(DISTINCT ct_1.name), ', '::text)), (array_to_string(array_agg(DISTINCT cst.name), ', '::text))
  • Sort Method: quicksort Memory: 25kB
6. 0.001 87,774.535 ↓ 0.0 0 1

Nested Loop Left Join (cost=18,059.30..19,724.85 rows=1 width=224) (actual time=87,774.535..87,774.535 rows=0 loops=1)

  • Join Filter: (du.role_group_id = ANY (wftc.task_owners))
  • Filter: (((du.entity_id = sl.id) AND (du.role_group_id = 2729) AND (sl.status_id = ANY ('{3870}'::integer[])) AND (sla.status_id = ANY ('{5}'::integer[])) AND ((du.user_id = 28019) OR ('{28019}'::integer[] <@ ug.user_ids)) A (...)
7. 0.001 87,774.534 ↓ 0.0 0 1

Nested Loop Left Join (cost=18,058.88..19,684.62 rows=72 width=325) (actual time=87,774.534..87,774.534 rows=0 loops=1)

8. 0.001 87,774.533 ↓ 0.0 0 1

Nested Loop Left Join (cost=18,058.46..19,676.03 rows=1 width=325) (actual time=87,774.533..87,774.533 rows=0 loops=1)

9. 0.001 87,774.532 ↓ 0.0 0 1

Nested Loop Left Join (cost=18,058.19..19,673.04 rows=1 width=325) (actual time=87,774.532..87,774.532 rows=0 loops=1)

  • Join Filter: (ug.id = du.user_group_id)
  • Filter: ((du.user_id = 28019) OR ('{28019}'::integer[] <@ ug.user_ids))
10. 0.001 87,774.531 ↓ 0.0 0 1

Nested Loop (cost=18,058.19..19,672.01 rows=1 width=280) (actual time=87,774.531..87,774.531 rows=0 loops=1)

11. 0.001 87,774.530 ↓ 0.0 0 1

Nested Loop (cost=18,057.75..19,514.63 rows=59 width=268) (actual time=87,774.530..87,774.530 rows=0 loops=1)

12. 4.490 87,774.529 ↓ 0.0 0 1

Nested Loop Left Join (cost=18,055.92..19,480.42 rows=1 width=264) (actual time=87,774.529..87,774.529 rows=0 loops=1)

  • Join Filter: (tz.id = sl.time_zone_id)
  • Rows Removed by Join Filter: 1860
  • Filter: ((change_tz(sl.reporting_date, 'UTC'::character varying, tz.time_zone))::date = (change_tz(now(), 'UTC'::character varying, tz.time_zone))::date)
  • Rows Removed by Filter: 60
13. 5,185.633 87,769.019 ↓ 60.0 60 1

Nested Loop Left Join (cost=18,055.92..19,457.58 rows=1 width=248) (actual time=2,270.672..87,769.019 rows=60 loops=1)

  • Join Filter: (les.entity_id = sl.id)
  • Rows Removed by Join Filter: 12610707
14. 5,280.405 44,098.426 ↓ 60.0 60 1

Nested Loop Left Join (cost=6,297.90..7,408.24 rows=1 width=216) (actual time=1,136.018..44,098.426 rows=60 loops=1)

  • Join Filter: (lef.entity_id = sl.id)
  • Rows Removed by Join Filter: 12610707
15. 0.239 6.001 ↓ 60.0 60 1

Nested Loop Left Join (cost=31.69..732.03 rows=1 width=184) (actual time=0.483..6.001 rows=60 loops=1)

16. 0.236 5.402 ↓ 60.0 60 1

Nested Loop Left Join (cost=31.40..729.53 rows=1 width=161) (actual time=0.474..5.402 rows=60 loops=1)

17. 0.310 4.746 ↓ 60.0 60 1

Nested Loop Left Join (cost=31.12..727.02 rows=1 width=126) (actual time=0.467..4.746 rows=60 loops=1)

18. 3.539 3.836 ↓ 60.0 60 1

Bitmap Heap Scan on child_sla sl (cost=30.83..724.52 rows=1 width=122) (actual time=0.458..3.836 rows=60 loops=1)

  • Recheck Cond: ((status_id = ANY ('{3870}'::integer[])) OR (status_id = ANY ('{}'::integer[])))
  • Filter: ((NOT deleted) AND supplier_access AND (client_id = 1012) AND (vendor_id = 10618))
  • Rows Removed by Filter: 1008
  • Heap Blocks: exact=1196
19. 0.002 0.297 ↓ 0.0 0 1

BitmapOr (cost=30.83..30.83 rows=624 width=0) (actual time=0.297..0.297 rows=0 loops=1)

20. 0.295 0.295 ↓ 2.1 1,306 1

Bitmap Index Scan on idx_child_sla_6 (cost=0.00..29.30 rows=624 width=0) (actual time=0.295..0.295 rows=1,306 loops=1)

  • Index Cond: (status_id = ANY ('{3870}'::integer[]))
21. 0.000 0.000 ↓ 0.0 0 1

Bitmap Index Scan on idx_child_sla_6 (cost=0.00..1.53 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=1)

  • Index Cond: (status_id = ANY ('{}'::integer[]))
22. 0.600 0.600 ↑ 1.0 1 60

Index Scan using pk_sla on sla (cost=0.29..2.50 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=60)

  • Index Cond: (sl.slaid = id)
23. 0.420 0.420 ↑ 1.0 1 60

Index Scan using pk_contract on contract ct (cost=0.29..2.50 rows=1 width=39) (actual time=0.007..0.007 rows=1 loops=60)

  • Index Cond: (id = sl.contract_id)
24. 0.360 0.360 ↑ 1.0 1 60

Index Scan using pk_relation on relation rn (cost=0.29..2.50 rows=1 width=27) (actual time=0.006..0.006 rows=1 loops=60)

  • Index Cond: (id = sl.relation_id)
25. 32,694.120 38,812.020 ↓ 23.1 210,179 60

GroupAggregate (cost=6,266.22..6,471.21 rows=9,111 width=36) (actual time=8.049..646.867 rows=210,179 loops=60)

  • Group Key: lef.entity_id
26. 5,779.244 6,117.900 ↓ 23.1 210,180 60

Sort (cost=6,266.22..6,288.99 rows=9,111 width=21) (actual time=8.040..101.965 rows=210,180 loops=60)

  • Sort Key: lef.entity_id
  • Sort Method: quicksort Memory: 23950kB
27. 338.628 338.656 ↓ 25.4 231,540 1

Nested Loop (cost=0.42..5,667.01 rows=9,111 width=21) (actual time=0.021..338.656 rows=231,540 loops=1)

  • -> Index Scan using idx_link_entity_function_3 on link_entity_function lef (cost=0.42..1362.17 rows=5361 width=8) (actual time=0.022..40.554 rows=57885 loop (...)
28. 0.028 0.028 ↑ 1.0 4 1

Seq Scan on contract_type ct_1 (cost=0.00..3.91 rows=4 width=21) (actual time=0.010..0.028 rows=4 loops=1)

  • Filter: (client_id = 1012)
  • Rows Removed by Filter: 149
  • Index Cond: (function_id = ct_1.id)
  • Filter: (entity_type_id = 15)
  • Rows Removed by Filter: 4431
29. 32,357.940 38,484.960 ↓ 32.5 210,179 60

GroupAggregate (cost=11,758.01..11,903.68 rows=6,474 width=36) (actual time=7.925..641.416 rows=210,179 loops=60)

  • Group Key: les.entity_id
30. 5,791.247 6,127.020 ↓ 32.5 210,391 60

Sort (cost=11,758.01..11,774.20 rows=6,474 width=35) (actual time=7.919..102.117 rows=210,391 loops=60)

  • Sort Key: les.entity_id
  • Sort Method: quicksort Memory: 24253kB
31. 173.522 335.773 ↓ 35.8 231,846 1

Nested Loop (cost=0.71..11,348.20 rows=6,474 width=35) (actual time=0.038..335.773 rows=231,846 loops=1)

32. 0.041 0.041 ↑ 1.0 30 1

Index Scan using idx_contract_sub_type_client_id on contract_sub_type cst (cost=0.28..16.37 rows=30 width=35) (actual time=0.012..0.041 rows=30 loops=1)

  • Index Cond: (client_id = 1012)
33. 162.210 162.210 ↓ 8.2 7,728 30

Index Scan using idx_link_entity_service_3 on link_entity_service les (cost=0.43..368.26 rows=947 width=8) (actual time=0.108..5.407 rows=7,728 loops=30)

  • Index Cond: (service_id = cst.id)
  • Filter: (entity_type_id = 15)
  • Rows Removed by Filter: 1024
34. 1.020 1.020 ↑ 1.3 32 60

Seq Scan on time_zone tz (cost=0.00..1.41 rows=41 width=20) (actual time=0.004..0.017 rows=32 loops=60)

35. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on role_group rg (cost=1.83..33.62 rows=59 width=4) (never executed)

  • Recheck Cond: (entity_type_id = 15)
36. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on role_group_entity_type_id_idx (cost=0.00..1.82 rows=59 width=0) (never executed)

  • Index Cond: (entity_type_id = 15)
37. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_domain_user_entity_id_role_group_id on domain_user du (cost=0.43..2.66 rows=1 width=16) (never executed)

  • Index Cond: ((entity_id = sl.id) AND (role_group_id = rg.id))
  • Filter: ((role_group_id = 2729) OR (role_group_id = 2723))
38. 0.000 0.000 ↓ 0.0 0

Seq Scan on user_group ug (cost=0.00..1.01 rows=1 width=53) (never executed)

39. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_work_flow on work_flow wf (cost=0.28..2.50 rows=1 width=4) (never executed)

  • Index Cond: (id = sl.work_flow_id)
  • Filter: ((entity_type_id = 15) AND (client_id = 1012))
40. 0.000 0.000 ↓ 0.0 0

Index Scan using work_flow_task_wfid_idx on work_flow_task wft (cost=0.42..7.86 rows=73 width=8) (never executed)

  • Index Cond: (work_flow_id = wf.id)
41. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_work_flow_task_configuration_2 on work_flow_task_configuration wftc (cost=0.42..0.50 rows=1 width=21) (never executed)

  • Index Cond: (wft.id = task_id)