explain.depesz.com

PostgreSQL's explain analyze made readable

Result: s4Be

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 53.637 ↑ 1.0 1 1

Limit (cost=13,538.70..13,538.74 rows=1 width=152) (actual time=53.636..53.637 rows=1 loops=1)

2. 0.000 53.635 ↑ 1.0 1 1

Unique (cost=13,538.70..13,538.74 rows=1 width=152) (actual time=53.635..53.635 rows=1 loops=1)

3. 0.016 53.635 ↑ 1.0 1 1

Sort (cost=13,538.70..13,538.71 rows=1 width=152) (actual time=53.634..53.635 rows=1 loops=1)

  • Sort Method: quicksort Memory: 25kB
  • Sort Key: (change_tz(am.planned_completion_date, 'UTC'::character varying, tz.time_zone)), am.id, am.client_entity_seq_id, am.name, am.status_id, ct.id, ct.name, rn.id, rn.name, tz.id, aj_contract_type.contracttypes, aj_contract_sub_type.contractsubtypes
4. 0.131 53.619 ↑ 1.0 1 1

Group (cost=13,538.42..13,538.69 rows=1 width=152) (actual time=53.619..53.619 rows=1 loops=1)

  • Group Key: am.id, tz.id, ct.id, rn.id, aj_contract_type.contracttypes, aj_contract_sub_type.contractsubtypes
5. 0.011 53.488 ↑ 1.0 1 1

Sort (cost=13,538.42..13,538.43 rows=1 width=418) (actual time=53.487..53.488 rows=1 loops=1)

  • Sort Method: quicksort Memory: 25kB
  • Sort Key: am.id, tz.id, ct.id, rn.id, aj_contract_type.contracttypes, aj_contract_sub_type.contractsubtypes
6. 0.050 53.477 ↑ 1.0 1 1

Nested Loop Left Join (cost=7,645.61..13,538.41 rows=1 width=418) (actual time=51.700..53.477 rows=1 loops=1)

  • Rows Removed by Join Filter: 48
  • Rows Removed by Filter: 58
  • Join Filter: (du.role_group_id = ANY (wftc.task_owners))
  • Filter: (((du.entity_id = am.id) AND (du.role_group_id = 2059) AND (am.status_id = ANY ('{7435,7342,2236,7437,7436,7225,7284,7260,2,4,2238,2237}'::integer[])) AND ((du.user_id = 2649) OR ('{2649}'::integer[] <@ ug.user_ids)) AND (du.entity_id = am.id) AND (wftc.task_id = ANY (am.next_manual_task_ids))) OR ((du.entity_id = am.id) AND (du.role_group_id = 2059) AND (am.status_id = ANY ('{7435,7342,2236,7437,7436,7225,7284,7260,2,4,2238,2237}'::integer[])) AND ((du.user_id = 2649) OR ('{2649}'::integer[] <@ ug.user_ids)) AND (du.entity_id = am.id) AND (wftc.task_id = ANY (am.next_manual_task_ids))) OR ((du.entity_id = am.entity_id) AND (hashed SubPlan 2) AND (du.role_group_id = 2299) AND (am.status_id = ANY ('{}'::integer[]))) OR ((du.entity_id = am.entity_id) AND (hashed SubPlan 3) AND (du.role_group_id = 2299) AND (am.status_id = ANY ('{}'::integer[]))))
7. 53.309 53.309 ↓ 1.4 59 1

Nested Loop Left Join (cost=7,628.60..13,483.12 rows=43 width=499) (actual time=51.535..53.309 rows=59 loops=1)

8. 0.118 0.118 ↑ 1.0 1 59

Index Scan using idx_work_flow_task_configuration_2 on work_flow_task_configuration wftc (cost=0.42..0.78 rows=1 width=21) (actual time=0.002..0.002 rows=1 loops=59)

9. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_role_group on role_group role_group_1 (cost=0.27..8.29 rows=1 width=4) (never executed)

10. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_role_group on role_group (cost=0.27..8.29 rows=1 width=4) (never executed)

  • Index Cond: (wft.id = task_id)
11. 53.269 53.269 ↑ 1.0 1 1

Nested Loop Left Join (cost=7,628.18..13,450.50 rows=1 width=499) (actual time=51.523..53.269 rows=1 loops=1)

12. 0.000 0.028 ↓ 1.3 59 1

Index Scan using work_flow_task_wfid_idx on work_flow_task wft (cost=0.42..32.16 rows=45 width=8) (actual time=0.011..0.028 rows=59 loops=1)

  • Index Cond: (id = 2299)
  • Index Cond: (id = 2299)
  • Index Cond: (work_flow_id = wf.id)
13. 53.261 53.261 ↑ 1.0 1 1

Nested Loop Left Join (cost=7,627.90..13,450.10 rows=1 width=499) (actual time=51.514..53.261 rows=1 loops=1)

14. 0.000 0.007 ↑ 1.0 1 1

Index Scan using pk_work_flow on work_flow wf (cost=0.28..0.40 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=1)

  • Rows Removed by Filter: 4284
  • Index Cond: (id = am.work_flow_id)
  • Filter: ((entity_type_id = 18) AND (client_id = 1005))
  • Filter: ((du.user_id = 2649) OR ('{2649}'::integer[] <@ ug.user_ids))
15. 51.828 51.828 ↓ 20.1 4,285 1

Nested Loop Left Join (cost=7,627.76..13,413.10 rows=213 width=471) (actual time=31.220..51.828 rows=4,285 loops=1)

16. 0.000 0.000 ↓ 0.0 0 4,285

Index Scan using pk_user_group on user_group ug (cost=0.14..0.16 rows=1 width=36) (actual time=0.000..0.000 rows=0 loops=4,285)

  • Index Cond: (id = du.user_group_id)
17. 47.151 47.151 ↓ 20.1 4,285 1

Nested Loop Left Join (cost=7,627.62..13,379.02 rows=213 width=197) (actual time=31.215..47.151 rows=4,285 loops=1)

18. 0.000 4.285 ↑ 1.0 1 4,285

Index Scan using pk_time_zone on time_zone tz (cost=0.14..0.16 rows=1 width=278) (actual time=0.001..0.001 rows=1 loops=4,285)

  • Index Cond: (id = am.time_zone_id)
19. 41.143 41.143 ↓ 20.1 4,285 1

Nested Loop Left Join (cost=7,627.35..13,310.67 rows=213 width=181) (actual time=31.209..41.143 rows=4,285 loops=1)

20. 0.000 4.285 ↑ 1.0 1 4,285

Index Scan using pk_relation on relation rn (cost=0.28..0.32 rows=1 width=20) (actual time=0.001..0.001 rows=1 loops=4,285)

  • Index Cond: (id = am.relation_id)
21. 0.000 0.000 ↓ 0.0 0 4,285

Index Scan using pk_contract on contract ct (cost=0.28..0.58 rows=1 width=22) (actual time=0.000..0.000 rows=0 loops=4,285)

22. 1.265 38.513 ↓ 20.1 4,285 1

Hash Join (cost=7,627.07..13,187.65 rows=213 width=163) (actual time=31.195..38.513 rows=4,285 loops=1)

  • Index Cond: (id = am.contract_id)
  • Hash Cond: (du.role_group_id = rg.id)
23. 34.899 34.899 ↓ 3.8 4,285 1

Hash Left Join (cost=7,030.00..12,587.58 rows=1,133 width=163) (actual time=28.836..34.899 rows=4,285 loops=1)

24. 0.000 2.349 ↓ 1.2 102 1

Hash (cost=596.00..596.00 rows=85 width=4) (actual time=2.349..2.349 rows=102 loops=1)

  • Hash Cond: (am.id = aj_contract_sub_type.id)
  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
25. 2.330 2.330 ↓ 1.2 102 1

Seq Scan on role_group rg (cost=576.07..596.00 rows=85 width=4) (actual time=2.221..2.330 rows=102 loops=1)

26. 20.497 20.497 ↓ 5.3 4,285 1

Hash Left Join (cost=2,646.99..8,202.43 rows=812 width=131) (actual time=15.820..20.497 rows=4,285 loops=1)

27. 0.000 12.982 ↑ 1.4 4,601 1

Hash (cost=4,301.84..4,301.84 rows=6,494 width=36) (actual time=12.982..12.982 rows=4,601 loops=1)

  • Rows Removed by Filter: 361
  • Hash Cond: (am.id = aj_contract_type.id)
  • Filter: ((client_id = 1005) AND ((hashed SubPlan 1) OR (entity_type_id = 18)))
  • Buckets: 8192 Batches: 1 Memory Usage: 281kB
28. 12.208 12.208 ↑ 1.4 4,601 1

Subquery Scan on aj_contract_sub_type (cost=4,084.43..4,301.84 rows=6,494 width=36) (actual time=3.516..12.208 rows=4,601 loops=1)

29. 7.740 7.740 ↓ 5.4 4,285 1

Hash Join (cost=547.66..6,101.02 rows=795 width=99) (actual time=4.435..7.740 rows=4,285 loops=1)

30. 9.157 11.353 ↑ 1.0 4,601 1

Hash (cost=2,039.89..2,039.89 rows=4,755 width=36) (actual time=11.353..11.353 rows=4,601 loops=1)

31. 0.000 2.196 ↑ 1.0 12 1

Unique (cost=0.28..576.04 rows=12 width=4) (actual time=0.013..2.196 rows=12 loops=1)

  • Hash Cond: (du.entity_id = am.id)
  • Buckets: 8192 Batches: 1 Memory Usage: 264kB
32. 10.587 10.587 ↑ 1.0 4,601 1

Subquery Scan on aj_contract_type (cost=1,882.76..2,039.89 rows=4,755 width=36) (actual time=3.076..10.587 rows=4,601 loops=1)

33. 3.972 3.972 ↑ 1.0 4,041 1

Hash (cost=299.18..299.18 rows=4,085 width=83) (actual time=3.971..3.972 rows=4,041 loops=1)

34. 11.773 11.773 ↑ 1.4 4,601 1

GroupAggregate (cost=4,084.43..4,236.90 rows=6,494 width=36) (actual time=3.515..11.773 rows=4,601 loops=1)

35. 0.000 1.635 ↑ 2.1 4,660 1

Bitmap Heap Scan on domain_user du (cost=197.43..5,725.06 rows=9,800 width=16) (actual time=0.446..1.635 rows=4,660 loops=1)

36. 0.000 1.985 ↑ 1.0 4,041 1

Index Scan using idx_action_item_mgmt_5 on action_item_mgmt am2 (cost=0.28..565.83 rows=4,085 width=4) (actual time=0.012..1.985 rows=4,041 loops=1)

  • Recheck Cond: ((role_group_id = 2059) OR (role_group_id = 2059) OR (role_group_id = 2299) OR (role_group_id = 2299))
  • Heap Blocks: exact=444
  • Group Key: les.entity_id
  • Buckets: 4096 Batches: 1 Memory Usage: 514kB
37. 3.918 3.918 ↓ 1.1 7,874 1

Sort (cost=4,084.43..4,102.79 rows=7,341 width=8) (actual time=3.494..3.918 rows=7,874 loops=1)

38. 2.629 2.629 ↑ 1.0 4,041 1

Seq Scan on action_item_mgmt am (cost=0.00..299.18 rows=4,085 width=83) (actual time=0.005..2.629 rows=4,041 loops=1)

39. 10.138 10.138 ↑ 1.0 4,601 1

GroupAggregate (cost=1,882.76..1,992.34 rows=4,755 width=36) (actual time=3.075..10.138 rows=4,601 loops=1)

40. 0.000 0.394 ↓ 0.0 0 1

BitmapOr (cost=197.43..197.43 rows=9,857 width=0) (actual time=0.394..0.394 rows=0 loops=1)

  • Rows Removed by Filter: 575
  • Filter: ((NOT deleted) AND (client_id = 1005))
  • Sort Method: quicksort Memory: 562kB
  • Sort Key: les.entity_id
  • Rows Removed by Filter: 575
  • Group Key: lef.entity_id
  • Filter: ((NOT deleted) AND (client_id = 1005))
41. 3.331 3.331 ↑ 1.0 5,037 1

Sort (cost=1,882.76..1,895.51 rows=5,100 width=8) (actual time=3.052..3.331 rows=5,037 loops=1)

42. 0.238 0.238 ↑ 1.0 4,660 1

Bitmap Index Scan on idx_domain_user_3 (cost=0.00..87.51 rows=4,678 width=0) (actual time=0.238..0.238 rows=4,660 loops=1)

43. 0.150 0.150 ↑ 1.0 4,660 1

Bitmap Index Scan on idx_domain_user_3 (cost=0.00..87.51 rows=4,678 width=0) (actual time=0.150..0.150 rows=4,660 loops=1)

44. 0.004 0.004 ↓ 0.0 0 1

Bitmap Index Scan on idx_domain_user_3 (cost=0.00..6.31 rows=251 width=0) (actual time=0.004..0.004 rows=0 loops=1)

45. 0.001 0.001 ↓ 0.0 0 1

Bitmap Index Scan on idx_domain_user_3 (cost=0.00..6.31 rows=251 width=0) (actual time=0.001..0.001 rows=0 loops=1)

46. 0.000 1.986 ↓ 1.1 7,874 1

Bitmap Heap Scan on link_entity_service les (cost=141.32..3,613.08 rows=7,341 width=8) (actual time=0.429..1.986 rows=7,874 loops=1)

  • Sort Method: quicksort Memory: 429kB
  • Sort Key: lef.entity_id
  • Recheck Cond: (entity_type_id = 18)
  • Index Cond: (role_group_id = 2299)
  • Index Cond: (role_group_id = 2299)
  • Index Cond: (role_group_id = 2059)
  • Index Cond: (role_group_id = 2059)
  • Heap Blocks: exact=523
47. 0.366 0.366 ↓ 1.1 7,874 1

Bitmap Index Scan on idx_link_entity_service_5 (cost=0.00..139.48 rows=7,341 width=0) (actual time=0.366..0.366 rows=7,874 loops=1)

48. 1.624 1.885 ↑ 1.0 5,037 1

Bitmap Heap Scan on link_entity_function lef (cost=99.95..1,568.69 rows=5,100 width=8) (actual time=0.304..1.885 rows=5,037 loops=1)

  • Recheck Cond: (entity_type_id = 18)
  • Index Cond: (entity_type_id = 18)
  • Heap Blocks: exact=396
49. 0.261 0.261 ↑ 1.0 5,037 1

Bitmap Index Scan on idx_link_entity_function_4 (cost=0.00..98.67 rows=5,100 width=0) (actual time=0.261..0.261 rows=5,037 loops=1)

  • Index Cond: (entity_type_id = 18)
Planning time : 9.697 ms
Execution time : 53.987 ms