explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cg53j

Settings
# exclusive inclusive rows x rows loops node
1. 4.480 2,690.337 ↓ 204.0 204 1

Group (cost=255,007.97..255,998.06 rows=1 width=292) (actual time=1,454.345..2,690.337 rows=204 loops=1)

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

CTE domain_user_with_flowdown_rgs

3. 32.212 899.632 ↓ 1.5 65,328 1

Unique (cost=199,223.54..200,309.29 rows=43,430 width=1,330) (actual time=852.903..899.632 rows=65,328 loops=1)

4. 51.901 867.420 ↓ 1.5 65,328 1

Sort (cost=199,223.54..199,332.12 rows=43,430 width=1,330) (actual time=852.902..867.420 rows=65,328 loops=1)

  • Sort 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, u.first_name, u.last_name, ug_3.name
  • Sort Method: external merge Disk: 3,080kB
5. 17.029 815.519 ↓ 1.5 65,328 1

Append (cost=13,653.72..181,095.49 rows=43,430 width=1,330) (actual time=220.418..815.519 rows=65,328 loops=1)

6. 21.616 375.385 ↓ 1.5 64,991 1

Hash Left Join (cost=13,653.72..173,102.70 rows=43,428 width=311) (actual time=220.417..375.385 rows=64,991 loops=1)

  • Hash Cond: (du_1.user_group_id = ug_3.id)
7. 25.274 353.753 ↓ 1.5 64,991 1

Hash Left Join (cost=13,652.54..172,987.51 rows=43,428 width=37) (actual time=220.388..353.753 rows=64,991 loops=1)

  • Hash Cond: (du_1.user_id = u.id)
8. 19.448 285.455 ↓ 1.5 64,991 1

Nested Loop (cost=12,796.32..172,016.42 rows=43,428 width=24) (actual time=177.349..285.455 rows=64,991 loops=1)

9. 0.220 177.474 ↓ 1.2 27 1

Index Scan using pk_role_group on role_group rg (cost=12,795.90..12,815.23 rows=22 width=12) (actual time=177.292..177.474 rows=27 loops=1)

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

SubPlan (for Index Scan)

11. 0.267 177.254 ↑ 1.1 14 1

Unique (cost=0.41..12,795.72 rows=15 width=4) (actual time=0.015..177.254 rows=14 loops=1)

12. 176.987 176.987 ↑ 1.2 1,489 1

Index Scan using idx_action_item_mgmt_2 on action_item_mgmt am2 (cost=0.41..12,791.20 rows=1,808 width=4) (actual time=0.015..176.987 rows=1,489 loops=1)

  • Filter: ((NOT deleted) AND (client_id = 1,002))
  • Rows Removed by Filter: 11,317
13. 84.136 88.533 ↑ 1.5 2,407 27

Index Scan using idx_domain_user_3 on domain_user du_1 (cost=0.42..7,199.72 rows=3,670 width=16) (actual time=0.015..3.279 rows=2,407 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,538
14.          

SubPlan (for Index Scan)

15. 0.000 4.397 ↓ 0.0 0 4,397

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

16. 4.397 4.397 ↓ 0.0 0 4,397

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,397)

  • Filter: (id = du_1.user_group_id)
  • Rows Removed by Filter: 8
17. 0.222 43.024 ↑ 1.0 551 1

Hash (cost=849.33..849.33 rows=551 width=17) (actual time=43.024..43.024 rows=551 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 36kB
18. 42.802 42.802 ↑ 1.0 551 1

Index Scan using pk_app_user on app_user u (cost=0.28..849.33 rows=551 width=17) (actual time=0.011..42.802 rows=551 loops=1)

19. 0.004 0.016 ↑ 1.0 8 1

Hash (cost=1.08..1.08 rows=8 width=278) (actual time=0.016..0.016 rows=8 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
20. 0.012 0.012 ↑ 1.0 8 1

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

21. 0.223 208.756 ↓ 256.0 256 1

Nested Loop Left Join (cost=7.24..4,193.27 rows=1 width=311) (actual time=53.655..208.756 rows=256 loops=1)

22. 0.236 162.197 ↓ 256.0 256 1

Merge Join (cost=6.97..4,192.94 rows=1 width=298) (actual time=53.261..162.197 rows=256 loops=1)

  • Merge Cond: (prg.id = crg.flowdown_rolegroup)
23. 0.615 161.845 ↓ 42.7 256 1

Nested Loop Left Join (cost=0.57..25,116.25 rows=6 width=294) (actual time=53.205..161.845 rows=256 loops=1)

  • Join Filter: (du_2.user_group_id = ug_4.id)
  • Rows Removed by Join Filter: 2,048
24. 4.411 160.974 ↓ 42.7 256 1

Nested Loop (cost=0.57..25,114.43 rows=6 width=20) (actual time=53.192..160.974 rows=256 loops=1)

  • Join Filter: (du_2.role_group_id = prg.id)
  • Rows Removed by Join Filter: 25,669
25. 0.112 0.112 ↑ 1.1 17 1

Index Scan using pk_role_group on role_group prg (cost=0.14..18.28 rows=18 width=4) (actual time=0.013..0.112 rows=17 loops=1)

  • Filter: (entity_type_id = 61)
  • Rows Removed by Filter: 211
26. 3.998 156.451 ↓ 20.6 1,525 17

Materialize (cost=0.42..25,076.35 rows=74 width=16) (actual time=2.407..9.203 rows=1,525 loops=17)

27. 2.026 152.453 ↓ 20.6 1,525 1

Nested Loop (cost=0.42..25,075.98 rows=74 width=16) (actual time=40.906..152.453 rows=1,525 loops=1)

28. 115.587 115.587 ↓ 27.2 1,742 1

Seq Scan on action_item_mgmt et (cost=0.00..24,459.12 rows=64 width=8) (actual time=37.459..115.587 rows=1,742 loops=1)

  • Filter: (((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: 11,064
29. 21.147 34.840 ↑ 2.0 1 1,742

Index Scan using idx_du_entity_id on domain_user du_2 (cost=0.42..9.62 rows=2 width=16) (actual time=0.015..0.020 rows=1 loops=1,742)

  • Index Cond: (entity_id = et.contract_id)
  • Filter: ((NOT entity_deleted) AND ((user_id = 1,044) OR (SubPlan 3)))
  • Rows Removed by Filter: 13
30.          

SubPlan (for Index Scan)

31. 0.000 13.693 ↓ 0.0 0 13,693

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

32. 13.693 13.693 ↓ 0.0 0 13,693

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=13,693)

  • Filter: (id = du_2.user_group_id)
  • Rows Removed by Filter: 8
33. 0.252 0.256 ↑ 1.0 8 256

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

34. 0.004 0.004 ↑ 1.0 8 1

Seq Scan on user_group ug_4 (cost=0.00..1.08 rows=8 width=278) (actual time=0.002..0.004 rows=8 loops=1)

35. 0.075 0.116 ↓ 86.0 258 1

Sort (cost=6.40..6.40 rows=3 width=16) (actual time=0.049..0.116 rows=258 loops=1)

  • Sort Key: crg.flowdown_rolegroup
  • Sort Method: quicksort Memory: 25kB
36. 0.018 0.041 ↑ 1.0 3 1

Bitmap Heap Scan on role_group crg (cost=3.18..6.37 rows=3 width=16) (actual time=0.034..0.041 rows=3 loops=1)

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

BitmapAnd (cost=3.18..3.18 rows=3 width=0) (actual time=0.023..0.023 rows=0 loops=1)

38. 0.010 0.010 ↓ 1.2 22 1

Bitmap Index Scan on role_group_entity_type_id_idx (cost=0.00..1.38 rows=18 width=0) (actual time=0.010..0.010 rows=22 loops=1)

  • Index Cond: (entity_type_id = 18)
39. 0.010 0.010 ↓ 1.8 74 1

Bitmap Index Scan on idx_role_grp_client_id (cost=0.00..1.55 rows=41 width=0) (actual time=0.010..0.010 rows=74 loops=1)

  • Index Cond: (client_id = 1,002)
40. 46.336 46.336 ↑ 1.0 1 256

Index Scan using pk_app_user on app_user u_1 (cost=0.28..0.33 rows=1 width=17) (actual time=0.181..0.181 rows=1 loops=256)

  • Index Cond: (du_2.user_id = id)
41. 0.110 214.349 ↓ 81.0 81 1

Nested Loop Left Join (cost=7.24..3,148.07 rows=1 width=311) (actual time=76.204..214.349 rows=81 loops=1)

42. 0.092 198.768 ↓ 81.0 81 1

Merge Join (cost=6.97..3,147.73 rows=1 width=298) (actual time=75.824..198.768 rows=81 loops=1)

  • Merge Cond: (prg_1.id = crg_1.flowdown_rolegroup)
43. 0.204 198.612 ↓ 10.1 81 1

Nested Loop Left Join (cost=0.57..25,126.50 rows=8 width=294) (actual time=75.778..198.612 rows=81 loops=1)

  • Join Filter: (du_3.user_group_id = ug_5.id)
  • Rows Removed by Join Filter: 648
44. 4.481 198.327 ↓ 10.1 81 1

Nested Loop (cost=0.57..25,124.44 rows=8 width=20) (actual time=75.763..198.327 rows=81 loops=1)

  • Join Filter: (du_3.role_group_id = prg_1.id)
  • Rows Removed by Join Filter: 26,270
45. 0.120 0.120 ↑ 1.1 13 1

Index Scan using pk_role_group on role_group prg_1 (cost=0.14..18.28 rows=14 width=4) (actual time=0.009..0.120 rows=13 loops=1)

  • Filter: (entity_type_id = 1)
  • Rows Removed by Filter: 215
46. 4.316 193.726 ↓ 14.3 2,027 13

Materialize (cost=0.42..25,076.69 rows=142 width=16) (actual time=3.059..14.902 rows=2,027 loops=13)

47. 2.528 189.410 ↓ 14.3 2,027 1

Nested Loop (cost=0.42..25,075.98 rows=142 width=16) (actual time=39.768..189.410 rows=2,027 loops=1)

48. 120.686 120.686 ↓ 27.2 1,742 1

Seq Scan on action_item_mgmt et_1 (cost=0.00..24,459.12 rows=64 width=8) (actual time=39.688..120.686 rows=1,742 loops=1)

  • Filter: (((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: 11,064
49. 38.062 66.196 ↑ 2.0 1 1,742

Index Scan using idx_du_entity_id on domain_user du_3 (cost=0.42..9.62 rows=2 width=16) (actual time=0.036..0.038 rows=1 loops=1,742)

  • Index Cond: (entity_id = et_1.relation_id)
  • Filter: ((NOT entity_deleted) AND ((user_id = 1,044) OR (SubPlan 4)))
  • Rows Removed by Filter: 22
50.          

SubPlan (for Index Scan)

51. 0.000 28.134 ↓ 0.0 0 28,134

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

52. 28.134 28.134 ↓ 0.0 0 28,134

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=28,134)

  • Filter: (id = du_3.user_group_id)
  • Rows Removed by Filter: 8
53. 0.077 0.081 ↑ 1.0 8 81

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

54. 0.004 0.004 ↑ 1.0 8 1

Seq Scan on user_group ug_5 (cost=0.00..1.08 rows=8 width=278) (actual time=0.002..0.004 rows=8 loops=1)

55. 0.029 0.064 ↓ 27.3 82 1

Sort (cost=6.40..6.40 rows=3 width=16) (actual time=0.041..0.064 rows=82 loops=1)

  • Sort Key: crg_1.flowdown_rolegroup
  • Sort Method: quicksort Memory: 25kB
56. 0.015 0.035 ↑ 1.0 3 1

Bitmap Heap Scan on role_group crg_1 (cost=3.18..6.37 rows=3 width=16) (actual time=0.029..0.035 rows=3 loops=1)

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

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

58. 0.008 0.008 ↓ 1.2 22 1

Bitmap Index Scan on role_group_entity_type_id_idx (cost=0.00..1.38 rows=18 width=0) (actual time=0.008..0.008 rows=22 loops=1)

  • Index Cond: (entity_type_id = 18)
59. 0.009 0.009 ↓ 1.8 74 1

Bitmap Index Scan on idx_role_grp_client_id (cost=0.00..1.55 rows=41 width=0) (actual time=0.009..0.009 rows=74 loops=1)

  • Index Cond: (client_id = 1,002)
60. 15.471 15.471 ↑ 1.0 1 81

Index Scan using pk_app_user on app_user u_2 (cost=0.28..0.33 rows=1 width=17) (actual time=0.191..0.191 rows=1 loops=81)

  • Index Cond: (du_3.user_id = id)
61. 0.450 1,448.393 ↓ 302.0 302 1

Sort (cost=54,698.68..54,698.69 rows=1 width=168) (actual time=1,448.276..1,448.393 rows=302 loops=1)

  • Sort Key: aim.id, r.name, ets.description, c.name, c.document_title
  • Sort Method: quicksort Memory: 93kB
62. 196.890 1,447.943 ↓ 302.0 302 1

Nested Loop (cost=9.73..54,698.67 rows=1 width=168) (actual time=940.045..1,447.943 rows=302 loops=1)

  • Join Filter: (aim.status_id = ets.id)
  • Rows Removed by Join Filter: 1,218,268
63. 0.337 1,047.203 ↓ 302.0 302 1

Nested Loop Left Join (cost=9.73..54,548.30 rows=1 width=150) (actual time=938.680..1,047.203 rows=302 loops=1)

64. 0.331 1,045.846 ↓ 204.0 204 1

Nested Loop Left Join (cost=9.31..54,545.65 rows=1 width=150) (actual time=938.665..1,045.846 rows=204 loops=1)

65. 0.264 1,045.107 ↓ 204.0 204 1

Nested Loop Left Join (cost=9.02..54,543.14 rows=1 width=104) (actual time=938.653..1,045.107 rows=204 loops=1)

66. 1.196 1,044.231 ↓ 204.0 204 1

Nested Loop (cost=8.73..54,540.64 rows=1 width=57) (actual time=938.641..1,044.231 rows=204 loops=1)

67. 922.985 922.985 ↓ 5.6 1,225 1

CTE Scan on domain_user_with_flowdown_rgs du (cost=0.00..41,638.51 rows=218 width=8) (actual time=852.941..922.985 rows=1,225 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: 64,103
68.          

SubPlan (for CTE Scan)

69. 0.000 0.000 ↓ 0.0 0

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

70. 0.000 0.000 ↓ 0.0 0

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

  • Filter: (id = du.user_group_id)
71. 102.886 120.050 ↓ 0.0 0 1,225

Bitmap Heap Scan on action_item_mgmt aim (cost=8.73..59.17 rows=1 width=65) (actual time=0.098..0.098 rows=0 loops=1,225)

  • 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=91,550
72. 1.225 17.150 ↓ 0.0 0 1,225

BitmapOr (cost=6.37..6.37 rows=34 width=0) (actual time=0.014..0.014 rows=0 loops=1,225)

73. 8.575 8.575 ↓ 63.0 63 1,225

Bitmap Index Scan on action_item_mgmt_id_idx (cost=0.00..1.45 rows=1 width=0) (actual time=0.007..0.007 rows=63 loops=1,225)

  • Index Cond: (du.entity_id = id)
74. 7.350 7.350 ↓ 2.3 77 1,225

Bitmap Index Scan on idx_action_item_mgmt_9 (cost=0.00..4.92 rows=33 width=0) (actual time=0.006..0.006 rows=77 loops=1,225)

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

SubPlan (for Bitmap Heap Scan)

76. 0.014 0.014 ↑ 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.010..0.014 rows=1 loops=1)

  • Index Cond: (id = 2,000)
77. 0.612 0.612 ↑ 1.0 1 204

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

  • Index Cond: (id = aim.relation_id)
78. 0.408 0.408 ↑ 1.0 1 204

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

  • Index Cond: (id = aim.contract_id)
79. 1.020 1.020 ↑ 1.0 1 204

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.004..0.005 rows=1 loops=204)

  • Index Cond: ((entity_id = aim.id) AND (entity_type_id = 18))
  • Heap Fetches: 300
80. 203.850 203.850 ↑ 1.0 4,035 302

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

81.          

SubPlan (for Group)

82. 0.816 1,237.464 ↑ 1.0 1 204

Aggregate (cost=989.80..989.81 rows=1 width=32) (actual time=6.066..6.066 rows=1 loops=204)

83. 0.566 1,236.648 ↑ 16.0 1 204

Hash Join (cost=11.88..989.64 rows=16 width=1,310) (actual time=2.339..6.062 rows=1 loops=204)

  • Hash Cond: (du_4.role_group_id = rg_1.id)
84. 1,236.036 1,236.036 ↑ 72.3 3 204

CTE Scan on domain_user_with_flowdown_rgs du_4 (cost=0.00..977.17 rows=217 width=1,314) (actual time=2.336..6.059 rows=3 loops=204)

  • Filter: (entity_id = aim.id)
  • Rows Removed by Filter: 65,325
85. 0.005 0.046 ↑ 1.1 17 1

Hash (cost=11.66..11.66 rows=18 width=4) (actual time=0.046..0.046 rows=17 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
86. 0.028 0.041 ↑ 1.1 17 1

Bitmap Heap Scan on role_group rg_1 (cost=1.38..11.66 rows=18 width=4) (actual time=0.021..0.041 rows=17 loops=1)

  • Recheck Cond: (entity_type_id = 18)
  • Heap Blocks: exact=9
87. 0.013 0.013 ↓ 1.2 22 1

Bitmap Index Scan on role_group_entity_type_id_idx (cost=0.00..1.38 rows=18 width=0) (actual time=0.012..0.013 rows=22 loops=1)

  • Index Cond: (entity_type_id = 18)
Planning time : 4.644 ms
Execution time : 2,692.176 ms