explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hfdsH

Settings
# exclusive inclusive rows x rows loops node
1. 8.855 4,658.558 ↓ 203.0 203 1

Group (cost=245,297.33..246,320.46 rows=1 width=292) (actual time=1,109.357..4,658.558 rows=203 loops=1)

  • Group Key: aim.id, r.name, ets.description, c.name, c.document_title
2.          

CTE domain_user_with_flowdown_rgs

3. 71.995 508.522 ↓ 1.6 68,859 1

HashAggregate (cost=189,784.92..190,218.39 rows=43,347 width=24) (actual time=479.793..508.522 rows=68,859 loops=1)

  • Group Key: du_1.entity_id, du_1.role_group_id, du_1.user_group_id, du_1.user_id, rg.client_id, rg.entity_type_id
4. 20.378 436.527 ↓ 1.6 68,859 1

Append (cost=12,008.04..189,134.71 rows=43,347 width=24) (actual time=147.244..436.527 rows=68,859 loops=1)

5. 22.348 271.679 ↓ 1.5 64,820 1

Nested Loop (cost=12,008.04..157,255.68 rows=43,343 width=24) (actual time=147.243..271.679 rows=64,820 loops=1)

6. 0.224 147.406 ↓ 1.4 27 1

Index Scan using pk_role_group on role_group rg (cost=12,007.62..12,025.38 rows=20 width=12) (actual time=147.216..147.406 rows=27 loops=1)

  • Filter: ((client_id = 1,002) AND ((hashed SubPlan 1) OR (entity_type_id = 18)))
  • Rows Removed by Filter: 201
7.          

SubPlan (for Index Scan)

8. 0.266 147.182 ↑ 1.1 14 1

Unique (cost=0.41..12,007.43 rows=15 width=4) (actual time=0.016..147.182 rows=14 loops=1)

9. 146.916 146.916 ↑ 1.2 1,475 1

Index Scan using idx_action_item_mgmt_2 on action_item_mgmt am2 (cost=0.41..12,002.97 rows=1,786 width=4) (actual time=0.015..146.916 rows=1,475 loops=1)

  • Filter: ((NOT deleted) AND (client_id = 1,002))
  • Rows Removed by Filter: 11,225
10. 93.179 101.925 ↑ 1.5 2,401 27

Index Scan using idx_domain_user_3 on domain_user du_1 (cost=0.42..7,224.94 rows=3,657 width=16) (actual time=0.011..3.775 rows=2,401 loops=27)

  • Index Cond: (role_group_id = rg.id)
  • Filter: ((NOT entity_deleted) AND ((user_id = 1,044) OR (SubPlan 2)))
  • Rows Removed by Filter: 4,503
11.          

SubPlan (for Index Scan)

12. 4.373 8.746 ↓ 0.0 0 4,373

ProjectSet (cost=0.00..1.61 rows=100 width=4) (actual time=0.002..0.002 rows=0 loops=4,373)

13. 4.373 4.373 ↓ 0.0 0 4,373

Seq Scan on user_group ug (cost=0.00..1.10 rows=1 width=32) (actual time=0.001..0.001 rows=0 loops=4,373)

  • Filter: (id = du_1.user_group_id)
  • Rows Removed by Filter: 8
14. 3.584 96.333 ↓ 803.0 1,606 1

Hash Join (cost=876.05..15,713.70 rows=2 width=24) (actual time=87.327..96.333 rows=1,606 loops=1)

  • Hash Cond: (et.contract_id = du_2.entity_id)
15. 44.018 44.018 ↑ 1.0 12,700 1

Seq Scan on action_item_mgmt et (cost=0.00..14,790.00 rows=12,700 width=8) (actual time=0.006..44.018 rows=12,700 loops=1)

16. 6.426 48.731 ↓ 409.6 20,480 1

Hash (cost=875.43..875.43 rows=50 width=24) (actual time=48.731..48.731 rows=20,480 loops=1)

  • Buckets: 32,768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1,296kB
17. 10.216 42.305 ↓ 409.6 20,480 1

Merge Join (cost=6.91..875.43 rows=50 width=24) (actual time=0.073..42.305 rows=20,480 loops=1)

  • Merge Cond: (prg.id = crg.flowdown_rolegroup)
18. 6.110 28.931 ↑ 1.7 20,480 1

Nested Loop (cost=0.57..118,436.40 rows=34,675 width=20) (actual time=0.031..28.931 rows=20,480 loops=1)

19. 0.126 0.126 ↓ 1.1 17 1

Index Scan using pk_role_group on role_group prg (cost=0.14..16.83 rows=16 width=4) (actual time=0.015..0.126 rows=17 loops=1)

  • Filter: (entity_type_id = 61)
  • Rows Removed by Filter: 211
20. 19.179 22.695 ↑ 3.0 1,205 17

Index Scan using idx_domain_user_3 on domain_user du_2 (cost=0.42..7,364.65 rows=3,657 width=16) (actual time=0.210..1.335 rows=1,205 loops=17)

  • Index Cond: (role_group_id = prg.id)
  • Filter: ((NOT entity_deleted) AND ((user_id = 1,044) OR (SubPlan 3)))
  • Rows Removed by Filter: 1,036
21.          

SubPlan (for Index Scan)

22. 0.000 3.516 ↓ 0.0 0 3,516

ProjectSet (cost=0.00..1.61 rows=100 width=4) (actual time=0.001..0.001 rows=0 loops=3,516)

23. 3.516 3.516 ↓ 0.0 0 3,516

Seq Scan on user_group ug_1 (cost=0.00..1.10 rows=1 width=32) (actual time=0.001..0.001 rows=0 loops=3,516)

  • Filter: (id = du_2.user_group_id)
  • Rows Removed by Filter: 8
24. 3.129 3.158 ↓ 6,827.3 20,482 1

Sort (cost=6.34..6.35 rows=3 width=16) (actual time=0.036..3.158 rows=20,482 loops=1)

  • Sort Key: crg.flowdown_rolegroup
  • Sort Method: quicksort Memory: 25kB
25. 0.008 0.029 ↑ 1.0 3 1

Bitmap Heap Scan on role_group crg (cost=3.14..6.32 rows=3 width=16) (actual time=0.026..0.029 rows=3 loops=1)

  • Recheck Cond: ((entity_type_id = 18) AND (client_id = 1,002))
  • Heap Blocks: exact=5
26. 0.003 0.021 ↓ 0.0 0 1

BitmapAnd (cost=3.14..3.14 rows=3 width=0) (actual time=0.020..0.021 rows=0 loops=1)

27. 0.009 0.009 ↓ 1.4 22 1

Bitmap Index Scan on role_group_entity_type_id_idx (cost=0.00..1.37 rows=16 width=0) (actual time=0.009..0.009 rows=22 loops=1)

  • Index Cond: (entity_type_id = 18)
28. 0.009 0.009 ↓ 1.8 66 1

Bitmap Index Scan on idx_role_grp_client_id (cost=0.00..1.52 rows=37 width=0) (actual time=0.009..0.009 rows=66 loops=1)

  • Index Cond: (client_id = 1,002)
29. 5.161 48.137 ↓ 1,216.5 2,433 1

Hash Join (cost=677.49..15,515.14 rows=2 width=24) (actual time=36.581..48.137 rows=2,433 loops=1)

  • Hash Cond: (et_1.relation_id = du_3.entity_id)
30. 39.436 39.436 ↑ 1.0 12,700 1

Seq Scan on action_item_mgmt et_1 (cost=0.00..14,790.00 rows=12,700 width=8) (actual time=0.010..39.436 rows=12,700 loops=1)

31. 0.018 3.540 ↓ 1.6 59 1

Hash (cost=677.02..677.02 rows=38 width=24) (actual time=3.540..3.540 rows=59 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
32. 0.039 3.522 ↓ 1.6 59 1

Merge Join (cost=6.91..677.02 rows=38 width=24) (actual time=0.094..3.522 rows=59 loops=1)

  • Merge Cond: (prg_1.id = crg_1.flowdown_rolegroup)
33. 0.024 3.430 ↑ 440.8 59 1

Nested Loop (cost=0.57..91,622.35 rows=26,006 width=20) (actual time=0.044..3.430 rows=59 loops=1)

34. 0.117 0.117 ↓ 1.1 13 1

Index Scan using pk_role_group on role_group prg_1 (cost=0.14..16.83 rows=12 width=4) (actual time=0.016..0.117 rows=13 loops=1)

  • Filter: (entity_type_id = 1)
  • Rows Removed by Filter: 215
35. 2.957 3.289 ↑ 731.4 5 13

Index Scan using idx_domain_user_3 on domain_user du_3 (cost=0.42..7,597.22 rows=3,657 width=16) (actual time=0.053..0.253 rows=5 loops=13)

  • Index Cond: (role_group_id = prg_1.id)
  • Filter: ((NOT entity_deleted) AND ((user_id = 1,044) OR (SubPlan 4)))
  • Rows Removed by Filter: 420
36.          

SubPlan (for Index Scan)

37. 0.000 0.332 ↓ 0.0 0 332

ProjectSet (cost=0.00..1.61 rows=100 width=4) (actual time=0.001..0.001 rows=0 loops=332)

38. 0.332 0.332 ↓ 0.0 0 332

Seq Scan on user_group ug_2 (cost=0.00..1.10 rows=1 width=32) (actual time=0.001..0.001 rows=0 loops=332)

  • Filter: (id = du_3.user_group_id)
  • Rows Removed by Filter: 8
39. 0.016 0.053 ↓ 20.0 60 1

Sort (cost=6.34..6.35 rows=3 width=16) (actual time=0.044..0.053 rows=60 loops=1)

  • Sort Key: crg_1.flowdown_rolegroup
  • Sort Method: quicksort Memory: 25kB
40. 0.016 0.037 ↑ 1.0 3 1

Bitmap Heap Scan on role_group crg_1 (cost=3.14..6.32 rows=3 width=16) (actual time=0.031..0.037 rows=3 loops=1)

  • Recheck Cond: ((entity_type_id = 18) AND (client_id = 1,002))
  • Heap Blocks: exact=5
41. 0.003 0.021 ↓ 0.0 0 1

BitmapAnd (cost=3.14..3.14 rows=3 width=0) (actual time=0.021..0.021 rows=0 loops=1)

42. 0.009 0.009 ↓ 1.4 22 1

Bitmap Index Scan on role_group_entity_type_id_idx (cost=0.00..1.37 rows=16 width=0) (actual time=0.009..0.009 rows=22 loops=1)

  • Index Cond: (entity_type_id = 18)
43. 0.009 0.009 ↓ 1.8 66 1

Bitmap Index Scan on idx_role_grp_client_id (cost=0.00..1.52 rows=37 width=0) (actual time=0.009..0.009 rows=66 loops=1)

  • Index Cond: (client_id = 1,002)
44. 0.633 1,090.707 ↓ 301.0 301 1

Sort (cost=55,078.94..55,078.95 rows=1 width=168) (actual time=1,090.509..1,090.707 rows=301 loops=1)

  • Sort Key: aim.id, r.name, ets.description, c.name, c.document_title
  • Sort Method: quicksort Memory: 93kB
45. 201.316 1,090.074 ↓ 301.0 301 1

Nested Loop (cost=9.58..55,078.93 rows=1 width=168) (actual time=483.061..1,090.074 rows=301 loops=1)

  • Join Filter: (aim.status_id = ets.id)
  • Rows Removed by Join Filter: 1,214,234
46. 0.365 680.165 ↓ 301.0 301 1

Nested Loop Left Join (cost=9.58..54,928.56 rows=1 width=150) (actual time=481.555..680.165 rows=301 loops=1)

47. 0.266 678.379 ↓ 203.0 203 1

Nested Loop Left Join (cost=9.16..54,925.91 rows=1 width=150) (actual time=481.537..678.379 rows=203 loops=1)

48. 0.246 677.504 ↓ 203.0 203 1

Nested Loop Left Join (cost=8.87..54,923.40 rows=1 width=104) (actual time=481.531..677.504 rows=203 loops=1)

49. 1.282 676.446 ↓ 203.0 203 1

Nested Loop (cost=8.58..54,920.90 rows=1 width=57) (actual time=481.517..676.446 rows=203 loops=1)

50. 539.980 539.980 ↓ 5.6 1,207 1

CTE Scan on domain_user_with_flowdown_rgs du (cost=0.00..41,558.94 rows=217 width=8) (actual time=479.835..539.980 rows=1,207 loops=1)

  • Filter: (((role_group_id = 2,008) OR (role_group_id = 2,000)) AND ((user_id = 1,044) OR (SubPlan 7)))
  • Rows Removed by Filter: 67,652
51.          

SubPlan (for CTE Scan)

52. 0.000 0.000 ↓ 0.0 0

ProjectSet (cost=0.00..1.61 rows=100 width=4) (never executed)

53. 0.000 0.000 ↓ 0.0 0

Seq Scan on user_group ug_4 (cost=0.00..1.10 rows=1 width=32) (never executed)

  • Filter: (id = du.user_group_id)
54. 117.067 135.184 ↓ 0.0 0 1,207

Bitmap Heap Scan on action_item_mgmt aim (cost=8.58..61.57 rows=1 width=65) (actual time=0.111..0.112 rows=0 loops=1,207)

  • Recheck Cond: ((du.entity_id = id) OR (du.entity_id = entity_id))
  • Filter: ((NOT deleted) AND (client_id = 1,002) AND (status_id = ANY ('{1561,1,1558,2,4,1560,1559}'::integer[])) AND (((du.entity_id = id) AND (du.role_group_id = 2,008)) OR ((du.entity_id = entity_id) AND (hashed SubPlan 8) AND (du.role_group_id = 2,000))) AND ((change_tz(planned_completion_date, 'UTC'::character varying, 'IST'::character varying))::date >= '2020-07-24 00:00:00'::timestamp without time zone) AND ((change_tz(planned_completion_date, 'UTC'::character varying, 'IST'::character varying))::date <= '2020-09-15 00:00:00'::timestamp without time zone))
  • Rows Removed by Filter: 2
  • Heap Blocks: exact=72,099
55. 1.207 18.105 ↓ 0.0 0 1,207

BitmapOr (cost=6.22..6.22 rows=37 width=0) (actual time=0.015..0.015 rows=0 loops=1,207)

56. 9.656 9.656 ↓ 50.0 50 1,207

Bitmap Index Scan on action_item_mgmt_id_idx (cost=0.00..1.44 rows=1 width=0) (actual time=0.008..0.008 rows=50 loops=1,207)

  • Index Cond: (du.entity_id = id)
57. 7.242 7.242 ↓ 1.7 62 1,207

Bitmap Index Scan on idx_action_item_mgmt_9 (cost=0.00..4.78 rows=36 width=0) (actual time=0.006..0.006 rows=62 loops=1,207)

  • Index Cond: (du.entity_id = entity_id)
58.          

SubPlan (for Bitmap Heap Scan)

59. 0.012 0.012 ↑ 1.0 1 1

Index Scan using pk_role_group on role_group (cost=0.14..2.36 rows=1 width=4) (actual time=0.009..0.012 rows=1 loops=1)

  • Index Cond: (id = 2,000)
60. 0.812 0.812 ↑ 1.0 1 203

Index Scan using pk_relation on relation r (cost=0.28..2.50 rows=1 width=55) (actual time=0.004..0.004 rows=1 loops=203)

  • Index Cond: (id = aim.relation_id)
61. 0.609 0.609 ↑ 1.0 1 203

Index Scan using pk_contract on contract c (cost=0.29..2.51 rows=1 width=54) (actual time=0.003..0.003 rows=1 loops=203)

  • Index Cond: (id = aim.contract_id)
62. 1.421 1.421 ↑ 1.0 1 203

Index Only Scan using idx_link_entity_function_6 on link_entity_function lef (cost=0.42..2.64 rows=1 width=4) (actual time=0.006..0.007 rows=1 loops=203)

  • Index Cond: ((entity_id = aim.id) AND (entity_type_id = 18))
  • Heap Fetches: 299
63. 208.593 208.593 ↑ 1.0 4,035 301

Seq Scan on work_flow_status ets (cost=0.00..99.61 rows=4,061 width=22) (actual time=0.002..0.693 rows=4,035 loops=301)

64.          

SubPlan (for Group)

65. 1.218 3,558.996 ↑ 1.0 1 203

Aggregate (cost=1,022.84..1,022.85 rows=1 width=32) (actual time=17.532..17.532 rows=1 loops=203)

66. 1.034 3,557.778 ↑ 16.0 1 203

Nested Loop Left Join (cost=11.76..1,022.68 rows=16 width=291) (actual time=9.960..17.526 rows=1 loops=203)

  • Join Filter: (du_4.user_group_id = ug_3.id)
  • Rows Removed by Join Filter: 12
67. 1.744 3,556.154 ↑ 16.0 1 203

Nested Loop Left Join (cost=11.76..1,019.66 rows=16 width=21) (actual time=9.954..17.518 rows=1 loops=203)

68. 0.974 1,388.520 ↑ 16.0 1 203

Hash Join (cost=10.82..986.70 rows=16 width=8) (actual time=2.838..6.840 rows=1 loops=203)

  • Hash Cond: (du_4.role_group_id = rg_1.id)
69. 1,387.505 1,387.505 ↑ 72.3 3 203

CTE Scan on domain_user_with_flowdown_rgs du_4 (cost=0.00..975.31 rows=217 width=12) (actual time=1.824..6.835 rows=3 loops=203)

  • Filter: (entity_id = aim.id)
  • Rows Removed by Filter: 68,856
70. 0.006 0.041 ↓ 1.1 17 1

Hash (cost=10.62..10.62 rows=16 width=4) (actual time=0.041..0.041 rows=17 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
71. 0.024 0.035 ↓ 1.1 17 1

Bitmap Heap Scan on role_group rg_1 (cost=1.37..10.62 rows=16 width=4) (actual time=0.018..0.035 rows=17 loops=1)

  • Recheck Cond: (entity_type_id = 18)
  • Heap Blocks: exact=9
72. 0.011 0.011 ↓ 1.4 22 1

Bitmap Index Scan on role_group_entity_type_id_idx (cost=0.00..1.37 rows=16 width=0) (actual time=0.011..0.011 rows=22 loops=1)

  • Index Cond: (entity_type_id = 18)
73. 1,975.615 2,165.890 ↑ 1.0 1 295

Bitmap Heap Scan on app_user u (cost=0.95..2.06 rows=1 width=17) (actual time=7.342..7.342 rows=1 loops=295)

  • Recheck Cond: (du_4.user_id = id)
  • Heap Blocks: exact=1,486,800
74. 190.275 190.275 ↓ 5,044.0 5,044 295

Bitmap Index Scan on pk_app_user (cost=0.00..0.95 rows=1 width=0) (actual time=0.645..0.645 rows=5,044 loops=295)

  • Index Cond: (du_4.user_id = id)
75. 0.582 0.590 ↑ 1.0 8 295

Materialize (cost=0.00..1.12 rows=8 width=278) (actual time=0.000..0.002 rows=8 loops=295)

76. 0.008 0.008 ↑ 1.0 8 1

Seq Scan on user_group ug_3 (cost=0.00..1.08 rows=8 width=278) (actual time=0.006..0.008 rows=8 loops=1)

Planning time : 3.503 ms
Execution time : 4,659.735 ms