explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DJMP

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 322.298 ↑ 1.0 1 1

Aggregate (cost=26,997.19..26,997.20 rows=1 width=8) (actual time=322.298..322.298 rows=1 loops=1)

2.          

Initplan (forAggregate)

3. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.00 rows=0 width=0) (never executed)

  • One-Time Filter: false
4. 0.000 322.293 ↑ 42.8 24 1

Nested Loop Semi Join (cost=21,663.76..26,994.62 rows=1,028 width=16) (actual time=321.858..322.293 rows=24 loops=1)

  • Join Filter: (workflowhe0_.id = projects6_.generic_resource_id)
5. 10.198 322.175 ↑ 42.8 24 1

Hash Join (cost=21,663.20..26,348.47 rows=1,028 width=48) (actual time=321.841..322.175 rows=24 loops=1)

  • Hash Cond: (workflowhe0_1_.id = workflowhe0_.id)
  • Join Filter: ((workflowhe0_1_.owner = '5ae5c81c-4c7f-4495-8bb7-ff738673a584'::uuid) OR (hashed SubPlan 2) OR $3 OR (alternatives: SubPlan 4 or hashed SubPlan 5))
6. 1.249 31.567 ↑ 1.0 1,173 1

Gather (cost=2,835.66..7,517.85 rows=1,175 width=48) (actual time=31.353..31.567 rows=1,173 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
7. 10.338 30.318 ↑ 1.3 391 3

Hash Join (cost=1,835.66..6,400.35 rows=490 width=48) (actual time=21.699..30.318 rows=391 loops=3)

  • Hash Cond: (workflowhe0_1_.id = projects8_.generic_resource_id)
8. 10.824 10.824 ↑ 1.2 86,563 3

Parallel Seq Scan on generic_resource workflowhe0_1_ (cost=0.00..4,154.03 rows=108,203 width=32) (actual time=0.020..10.824 rows=86,563 loops=3)

9. 0.216 9.156 ↑ 1.0 1,173 3

Hash (cost=1,820.97..1,820.97 rows=1,175 width=16) (actual time=9.155..9.156 rows=1,173 loops=3)

  • Buckets: 2048 Batches: 1 Memory Usage: 71kB
10. 0.503 8.940 ↑ 1.0 1,173 3

HashAggregate (cost=1,809.22..1,820.97 rows=1,175 width=16) (actual time=8.744..8.940 rows=1,173 loops=3)

  • Group Key: projects8_.generic_resource_id
11. 0.122 8.437 ↑ 1.0 1,173 3

Nested Loop (cost=0.00..1,806.29 rows=1,175 width=16) (actual time=4.067..8.437 rows=1,173 loops=3)

12. 0.071 0.071 ↑ 1.0 1 3

Seq Scan on project project9_ (cost=0.00..8.11 rows=1 width=16) (actual time=0.057..0.071 rows=1 loops=3)

  • Filter: (id = 'd0d68bca-c76f-4767-b4b2-bdce0f7a3297'::uuid)
  • Rows Removed by Filter: 248
13. 8.244 8.244 ↑ 1.0 1,173 3

Seq Scan on generic_resource_projects projects8_ (cost=0.00..1,786.42 rows=1,175 width=32) (actual time=4.008..8.244 rows=1,173 loops=3)

  • Filter: (project_id = 'd0d68bca-c76f-4767-b4b2-bdce0f7a3297'::uuid)
  • Rows Removed by Filter: 81741
14. 0.692 1.369 ↑ 1.0 4,502 1

Hash (cost=88.02..88.02 rows=4,502 width=16) (actual time=1.369..1.369 rows=4,502 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 276kB
15. 0.677 0.677 ↑ 1.0 4,502 1

Seq Scan on workflow workflowhe0_ (cost=0.00..88.02 rows=4,502 width=16) (actual time=0.012..0.677 rows=4,502 loops=1)

16.          

SubPlan (forHash Join)

17. 3.685 279.041 ↓ 4.1 41,844 1

Subquery Scan on userresour1_ (cost=4,378.87..18,657.45 rows=10,318 width=16) (actual time=86.600..279.041 rows=41,844 loops=1)

18. 26.058 275.356 ↓ 4.1 41,844 1

GroupAggregate (cost=4,378.87..18,554.27 rows=10,318 width=37) (actual time=86.599..275.356 rows=41,844 loops=1)

  • Group Key: artgr.holder_id, grp.generic_resource_id
  • Filter: bool_or(CASE WHEN (d.id IS NOT NULL) THEN gr.read_data WHEN (dr.id IS NOT NULL) THEN gr.read_dyn_report WHEN (w.id IS NOT NULL) THEN gr.read_workflow WHEN (wj.id IS NOT NULL) THEN gr.read_wf_job WHEN (k.id IS NOT NULL) THEN gr.read_key WHEN (m.id IS NOT NULL) THEN gr.read_model WHEN (s.id IS NOT NULL) THEN gr.read_schedule WHEN (dbc.id IS NOT NULL) THEN gr.read_data_source_connection WHEN (pl.id IS NOT NULL) THEN gr.read_production_line ELSE NULL::boolean END)
  • Rows Removed by Filter: 1587
19. 10.251 249.298 ↓ 2.4 49,772 1

Merge Left Join (cost=4,378.87..18,193.14 rows=20,636 width=185) (actual time=86.581..249.298 rows=49,772 loops=1)

  • Merge Cond: (grp.generic_resource_id = pl.id)
20. 10.033 239.035 ↓ 2.4 49,772 1

Merge Left Join (cost=4,377.57..18,140.16 rows=20,636 width=169) (actual time=86.568..239.035 rows=49,772 loops=1)

  • Merge Cond: (grp.generic_resource_id = dbc.id)
21. 9.963 228.971 ↓ 2.4 49,772 1

Merge Left Join (cost=4,374.48..18,085.07 rows=20,636 width=153) (actual time=86.540..228.971 rows=49,772 loops=1)

  • Merge Cond: (grp.generic_resource_id = s.id)
22. 9.564 218.964 ↓ 2.4 49,772 1

Merge Left Join (cost=4,370.73..18,029.21 rows=20,636 width=137) (actual time=86.502..218.964 rows=49,772 loops=1)

  • Merge Cond: (grp.generic_resource_id = m.id)
23. 32.197 209.365 ↓ 2.4 49,772 1

Nested Loop Left Join (cost=4,367.54..17,974.01 rows=20,636 width=121) (actual time=86.470..209.365 rows=49,772 loops=1)

24. 9.533 127.396 ↓ 2.4 49,772 1

Merge Left Join (cost=4,367.12..5,023.34 rows=20,636 width=105) (actual time=86.453..127.396 rows=49,772 loops=1)

  • Merge Cond: (grp.generic_resource_id = w.id)
25. 9.630 115.767 ↓ 2.4 49,772 1

Merge Left Join (cost=4,366.84..4,630.80 rows=20,636 width=89) (actual time=86.438..115.767 rows=49,772 loops=1)

  • Merge Cond: (grp.generic_resource_id = dr.id)
26. 11.858 105.515 ↓ 2.4 49,772 1

Merge Left Join (cost=4,366.56..4,470.19 rows=20,636 width=73) (actual time=86.417..105.515 rows=49,772 loops=1)

  • Merge Cond: (grp.generic_resource_id = k.id)
27. 40.491 93.616 ↓ 2.4 49,772 1

Sort (cost=4,363.19..4,414.78 rows=20,636 width=57) (actual time=86.377..93.616 rows=49,772 loops=1)

  • Sort Key: grp.generic_resource_id
  • Sort Method: external merge Disk: 2928kB
28. 16.036 53.125 ↓ 2.4 49,772 1

Hash Left Join (cost=733.72..2,884.32 rows=20,636 width=57) (actual time=7.485..53.125 rows=49,772 loops=1)

  • Hash Cond: (grp.generic_resource_id = d.id)
29. 23.214 31.144 ↓ 2.4 49,772 1

Hash Join (cost=177.11..2,273.54 rows=20,636 width=41) (actual time=1.479..31.144 rows=49,772 loops=1)

  • Hash Cond: (grp.project_id = artgr.project_id)
30. 6.466 6.466 ↑ 1.0 82,914 1

Seq Scan on generic_resource_projects grp (cost=0.00..1,579.14 rows=82,914 width=32) (actual time=0.007..6.466 rows=82,914 loops=1)

31. 0.031 1.464 ↓ 1.8 103 1

Hash (cost=176.41..176.41 rows=56 width=41) (actual time=1.463..1.464 rows=103 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
32. 0.545 1.433 ↓ 1.8 103 1

Nested Loop (cost=136.00..176.41 rows=56 width=41) (actual time=0.681..1.433 rows=103 loops=1)

  • Join Filter: ((gr.group_role_id = artgr.role_id) OR (gr.viewer_role_id = (NULL::integer)))
  • Rows Removed by Join Filter: 2987
33. 0.071 0.579 ↓ 1.3 103 1

HashAggregate (cost=129.38..130.16 rows=78 width=52) (actual time=0.556..0.579 rows=103 loops=1)

  • Group Key: artgr.project_id, artgr.holder_id, artgr.role_id, (NULL::integer)
34. 0.011 0.508 ↓ 1.6 128 1

Append (cost=2.04..128.60 rows=78 width=52) (actual time=0.381..0.508 rows=128 loops=1)

35. 0.110 0.128 ↓ 0.0 0 1

Seq Scan on access_rel_to_group_roles artgr (cost=2.04..9.21 rows=1 width=52) (actual time=0.128..0.128 rows=0 loops=1)

  • Filter: ((NOT (hashed SubPlan 1)) AND (holder_id = '5ae5c81c-4c7f-4495-8bb7-ff738673a584'::uuid))
  • Rows Removed by Filter: 280
36.          

SubPlan (forSeq Scan)

37. 0.018 0.018 ↓ 1.0 86 1

Seq Scan on user_group (cost=0.00..1.83 rows=83 width=16) (actual time=0.009..0.018 rows=86 loops=1)

38. 0.058 0.369 ↓ 1.7 128 1

Hash Join (cost=80.69..118.22 rows=77 width=52) (actual time=0.250..0.369 rows=128 loops=1)

  • Hash Cond: (access_rel_to_group_roles.holder_id = domain_membership.domain_id)
39. 0.175 0.249 ↑ 5.4 288 1

HashAggregate (cost=67.18..82.66 rows=1,548 width=52) (actual time=0.180..0.249 rows=288 loops=1)

  • Group Key: access_rel_to_group_roles.project_id, access_rel_to_group_roles.holder_id, access_rel_to_group_roles.role_id, (NULL::integer)
40. 0.021 0.074 ↑ 5.4 288 1

Append (cost=0.00..51.70 rows=1,548 width=52) (actual time=0.005..0.074 rows=288 loops=1)

41. 0.048 0.048 ↓ 1.0 280 1

Seq Scan on access_rel_to_group_roles (cost=0.00..5.78 rows=278 width=52) (actual time=0.004..0.048 rows=280 loops=1)

42. 0.005 0.005 ↑ 158.8 8 1

Seq Scan on viewer_access_rel_to_viewer_role (cost=0.00..22.70 rows=1,270 width=52) (actual time=0.004..0.005 rows=8 loops=1)

43. 0.004 0.062 ↑ 1.0 10 1

Hash (cost=13.38..13.38 rows=10 width=32) (actual time=0.062..0.062 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
44. 0.002 0.058 ↑ 1.0 10 1

Append (cost=0.00..13.38 rows=10 width=32) (actual time=0.009..0.058 rows=10 loops=1)

45. 0.031 0.031 ↑ 1.0 2 1

Seq Scan on domain_membership (cost=0.00..4.90 rows=2 width=32) (actual time=0.008..0.031 rows=2 loops=1)

  • Filter: (user_id = '5ae5c81c-4c7f-4495-8bb7-ff738673a584'::uuid)
  • Rows Removed by Filter: 233
46. 0.012 0.025 ↑ 1.0 8 1

Bitmap Heap Scan on sub_group_membership (cost=4.33..8.43 rows=8 width=32) (actual time=0.019..0.025 rows=8 loops=1)

  • Recheck Cond: (user_id = '5ae5c81c-4c7f-4495-8bb7-ff738673a584'::uuid)
  • Heap Blocks: exact=3
47. 0.013 0.013 ↑ 1.0 8 1

Bitmap Index Scan on pk_sub_group_membership (cost=0.00..4.33 rows=8 width=0) (actual time=0.013..0.013 rows=8 loops=1)

  • Index Cond: (user_id = '5ae5c81c-4c7f-4495-8bb7-ff738673a584'::uuid)
48. 0.155 0.309 ↓ 1.1 30 103

Materialize (cost=6.62..7.32 rows=28 width=29) (actual time=0.001..0.003 rows=30 loops=103)

49. 0.007 0.154 ↓ 1.1 30 1

Subquery Scan on gr (cost=6.62..7.18 rows=28 width=29) (actual time=0.102..0.154 rows=30 loops=1)

50. 0.109 0.147 ↓ 1.1 30 1

HashAggregate (cost=6.62..6.90 rows=28 width=74) (actual time=0.101..0.147 rows=30 loops=1)

  • Group Key: group_role.id, (NULL::integer), group_role.read_data, group_role.write_data, group_role.create_data, group_role.delete_data, group_role.execute_data, group_role.share_data, group_role.read_workflow, group_role.write_workflow, group_role.create_workflow, group_role.delete_workflow, group_role.execute_workflow, group_role.share_workflow, group_role.read_wf_job, group_role.write_wf_job, group_role.create_wf_job, group_role.delete_wf_job, group_role.execute_wf_job, group_role.share_wf_job, group_role.read_dyn_report, group_role.write_dyn_report, group_role.create_dyn_report, group_role.delete_dyn_report, group_role.execute_dyn_report, group_role.share_dyn_report, group_role.read_key, group_role.write_key, group_role.create_key, group_role.delete_key, group_role.execute_key, group_role.share_key, group_role.read_model, group_role.write_model, group_role.create_model, group_role.delete_model, group_role.execute_model, group_role.share_model, group_role.read_schedule, group_role.write_schedule, group_role.create_schedule, group_role.delete_schedule, group_role.execute_schedule, group_role.share_schedule, group_role.read_data_source_connection, group_role.write_data_source_connection, group_role.create_data_source_connection, group_role.delete_data_source_connection, group_role.execute_data_source_connection, group_role.share_data_source_connection, group_role.read_production_line, group_role.write_production_line, group_role.create_production_line, group_role.delete_production_line, group_role.execute_production_line, group_role.share_production_line
51. 0.002 0.038 ↓ 1.1 30 1

Append (cost=0.00..2.70 rows=28 width=74) (actual time=0.007..0.038 rows=30 loops=1)

52. 0.027 0.027 ↓ 1.1 26 1

Seq Scan on group_role (cost=0.00..1.24 rows=24 width=74) (actual time=0.007..0.027 rows=26 loops=1)

53. 0.009 0.009 ↑ 1.0 4 1

Seq Scan on viewer_role (cost=0.00..1.04 rows=4 width=74) (actual time=0.006..0.009 rows=4 loops=1)

54. 2.995 5.945 ↑ 1.0 18,605 1

Hash (cost=324.05..324.05 rows=18,605 width=16) (actual time=5.945..5.945 rows=18,605 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1129kB
55. 2.950 2.950 ↑ 1.0 18,605 1

Seq Scan on data d (cost=0.00..324.05 rows=18,605 width=16) (actual time=0.007..2.950 rows=18,605 loops=1)

56. 0.027 0.041 ↑ 1.0 60 1

Sort (cost=3.37..3.52 rows=60 width=16) (actual time=0.034..0.041 rows=60 loops=1)

  • Sort Key: k.id
  • Sort Method: quicksort Memory: 27kB
57. 0.014 0.014 ↑ 1.0 60 1

Seq Scan on key k (cost=0.00..1.60 rows=60 width=16) (actual time=0.008..0.014 rows=60 loops=1)

58. 0.622 0.622 ↑ 1.0 1,432 1

Index Only Scan using pk_dynamic_report on dynamic_report dr (cost=0.28..101.75 rows=1,432 width=16) (actual time=0.017..0.622 rows=1,432 loops=1)

  • Heap Fetches: 1432
59. 2.096 2.096 ↑ 1.0 4,501 1

Index Only Scan using sys_idx_uq_wrkflw_d_10114 on workflow w (cost=0.28..319.79 rows=4,502 width=16) (actual time=0.014..2.096 rows=4,501 loops=1)

  • Heap Fetches: 4501
60. 49.772 49.772 ↑ 1.0 1 49,772

Index Only Scan using pk_workflow_job on workflow_job wj (cost=0.42..0.63 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=49,772)

  • Index Cond: (id = grp.generic_resource_id)
  • Heap Fetches: 41927
61. 0.021 0.035 ↑ 1.0 56 1

Sort (cost=3.19..3.33 rows=56 width=16) (actual time=0.029..0.035 rows=56 loops=1)

  • Sort Key: m.id
  • Sort Method: quicksort Memory: 27kB
62. 0.014 0.014 ↑ 1.0 56 1

Seq Scan on model m (cost=0.00..1.56 rows=56 width=16) (actual time=0.008..0.014 rows=56 loops=1)

63. 0.025 0.044 ↑ 1.0 68 1

Sort (cost=3.75..3.92 rows=68 width=16) (actual time=0.037..0.044 rows=68 loops=1)

  • Sort Key: s.id
  • Sort Method: quicksort Memory: 28kB
64. 0.019 0.019 ↑ 1.0 68 1

Seq Scan on schedule s (cost=0.00..1.68 rows=68 width=16) (actual time=0.012..0.019 rows=68 loops=1)

65. 0.021 0.031 ↑ 1.0 54 1

Sort (cost=3.09..3.23 rows=54 width=16) (actual time=0.025..0.031 rows=54 loops=1)

  • Sort Key: dbc.id
  • Sort Method: quicksort Memory: 27kB
66. 0.010 0.010 ↑ 1.0 54 1

Seq Scan on database_connection dbc (cost=0.00..1.54 rows=54 width=16) (actual time=0.005..0.010 rows=54 loops=1)

67. 0.006 0.012 ↑ 1.0 11 1

Sort (cost=1.30..1.33 rows=11 width=16) (actual time=0.010..0.012 rows=11 loops=1)

  • Sort Key: pl.id
  • Sort Method: quicksort Memory: 25kB
68. 0.006 0.006 ↑ 1.0 11 1

Seq Scan on production_line pl (cost=0.00..1.11 rows=11 width=16) (actual time=0.005..0.006 rows=11 loops=1)

69. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.42..12.56 rows=1 width=0) (never executed)

  • Join Filter: (projects4_.project_id = project5_.id)
70. 0.000 0.000 ↓ 0.0 0

Index Only Scan using pk_gnrc_rsrc_prjcts on generic_resource_projects projects4_ (cost=0.42..4.44 rows=1 width=16) (never executed)

  • Index Cond: (generic_resource_id = workflowhe0_.id)
  • Heap Fetches: 0
71. 0.000 0.000 ↓ 0.0 0

Seq Scan on project project5_ (cost=0.00..8.11 rows=1 width=16) (never executed)

  • Filter: (owner_id = '5ae5c81c-4c7f-4495-8bb7-ff738673a584'::uuid)
72. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=8.12..1,808.62 rows=333 width=16) (never executed)

  • Hash Cond: (projects4__1.project_id = project5__1.id)
73. 0.000 0.000 ↓ 0.0 0

Seq Scan on generic_resource_projects projects4__1 (cost=0.00..1,579.14 rows=82,914 width=32) (never executed)

74. 0.000 0.000 ↓ 0.0 0

Hash (cost=8.11..8.11 rows=1 width=16) (never executed)

75. 0.000 0.000 ↓ 0.0 0

Seq Scan on project project5__1 (cost=0.00..8.11 rows=1 width=16) (never executed)

  • Filter: (owner_id = '5ae5c81c-4c7f-4495-8bb7-ff738673a584'::uuid)
76. 0.024 0.120 ↑ 1.0 1 24

Nested Loop (cost=0.56..0.62 rows=1 width=16) (actual time=0.005..0.005 rows=1 loops=24)

77. 0.072 0.072 ↑ 1.0 1 24

Index Only Scan using pk_gnrc_rsrc_prjcts on generic_resource_projects projects6_ (cost=0.42..0.45 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=24)

  • Index Cond: (generic_resource_id = workflowhe0_1_.id)
  • Heap Fetches: 0
78. 0.024 0.024 ↑ 1.0 1 24

Index Scan using pk_project on project project7_ (cost=0.14..0.17 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=24)

  • Index Cond: (id = projects6_.project_id)
  • Filter: (domain_id = 'a26ee39b-ffe4-4158-a7f0-dc0f3e02cf7b'::uuid)
Planning time : 15.096 ms
Execution time : 323.400 ms