explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gkQy

Settings
# exclusive inclusive rows x rows loops node
1. 6.137 3,590.375 ↓ 204.0 204 1

Group (cost=285,323.36..286,409.46 rows=1 width=292) (actual time=1,936.831..3,590.375 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. 35.160 1,369.937 ↓ 1.6 69,087 1

Unique (cost=229,536.96..230,622.76 rows=43,432 width=1,330) (actual time=1,319.053..1,369.937 rows=69,087 loops=1)

4. 59.496 1,334.777 ↓ 1.6 69,087 1

Sort (cost=229,536.96..229,645.54 rows=43,432 width=1,330) (actual time=1,319.051..1,334.777 rows=69,087 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,256kB
5. 18.239 1,275.281 ↓ 1.6 69,087 1

Append (cost=13,651.45..211,408.74 rows=43,432 width=1,330) (actual time=212.979..1,275.281 rows=69,087 loops=1)

6. 21.505 362.701 ↓ 1.5 64,991 1

Hash Left Join (cost=13,651.45..173,100.43 rows=43,428 width=311) (actual time=212.977..362.701 rows=64,991 loops=1)

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

Hash Left Join (cost=13,650.27..172,985.24 rows=43,428 width=37) (actual time=212.953..341.183 rows=64,991 loops=1)

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

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

9. 0.206 170.770 ↓ 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=170.602..170.770 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.268 170.564 ↑ 1.1 14 1

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

12. 170.296 170.296 ↑ 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..170.296 rows=1,489 loops=1)

  • Filter: ((NOT deleted) AND (client_id = 1,002))
  • Rows Removed by Filter: 11,317
13. 78.709 83.106 ↑ 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.013..3.078 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 42.295 ↓ 1.0 551 1

Hash (cost=847.09..847.09 rows=549 width=17) (actual time=42.295..42.295 rows=551 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 36kB
18. 42.073 42.073 ↓ 1.0 551 1

Index Scan using pk_app_user on app_user u (cost=0.28..847.09 rows=549 width=17) (actual time=0.012..42.073 rows=551 loops=1)

19. 0.005 0.013 ↑ 1.0 8 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
20. 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)

21. 2.327 386.928 ↓ 813.5 1,627 1

Nested Loop Left Join (cost=947.35..18,924.01 rows=2 width=311) (actual time=88.463..386.928 rows=1,627 loops=1)

22. 2.343 384.601 ↓ 813.5 1,627 1

Nested Loop Left Join (cost=947.22..18,923.71 rows=2 width=37) (actual time=88.457..384.601 rows=1,627 loops=1)

23. 4.252 97.533 ↓ 813.5 1,627 1

Hash Join (cost=946.94..18,923.04 rows=2 width=24) (actual time=88.130..97.533 rows=1,627 loops=1)

  • Hash Cond: (et.contract_id = du_2.entity_id)
24. 47.252 47.252 ↑ 1.0 12,806 1

Seq Scan on action_item_mgmt et (cost=0.00..17,928.06 rows=12,806 width=8) (actual time=0.007..47.252 rows=12,806 loops=1)

25. 5.853 46.029 ↓ 395.6 20,573 1

Hash (cost=946.29..946.29 rows=52 width=24) (actual time=46.028..46.029 rows=20,573 loops=1)

  • Buckets: 32,768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1,301kB
26. 9.969 40.176 ↓ 395.6 20,573 1

Merge Join (cost=6.97..946.29 rows=52 width=24) (actual time=0.069..40.176 rows=20,573 loops=1)

  • Merge Cond: (prg.id = crg.flowdown_rolegroup)
27. 6.019 27.109 ↑ 1.7 20,573 1

Nested Loop (cost=0.57..132,314.22 rows=35,532 width=20) (actual time=0.034..27.109 rows=20,573 loops=1)

28. 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.010..0.112 rows=17 loops=1)

  • Filter: (entity_type_id = 61)
  • Rows Removed by Filter: 211
29. 17.440 20.978 ↑ 3.0 1,210 17

Index Scan using idx_domain_user_3 on domain_user du_2 (cost=0.42..7,313.07 rows=3,670 width=16) (actual time=0.213..1.234 rows=1,210 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,042
30.          

SubPlan (for Index Scan)

31. 0.000 3.538 ↓ 0.0 0 3,538

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

32. 3.538 3.538 ↓ 0.0 0 3,538

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

  • Filter: (id = du_2.user_group_id)
  • Rows Removed by Filter: 8
33. 3.072 3.098 ↓ 6,858.3 20,575 1

Sort (cost=6.40..6.40 rows=3 width=16) (actual time=0.031..3.098 rows=20,575 loops=1)

  • Sort Key: crg.flowdown_rolegroup
  • Sort Method: quicksort Memory: 25kB
34. 0.009 0.026 ↑ 1.0 3 1

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

  • Recheck Cond: ((entity_type_id = 18) AND (client_id = 1,002))
  • Heap Blocks: exact=5
35. 0.002 0.017 ↓ 0.0 0 1

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

36. 0.007 0.007 ↓ 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.007..0.007 rows=22 loops=1)

  • Index Cond: (entity_type_id = 18)
37. 0.008 0.008 ↓ 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.008..0.008 rows=74 loops=1)

  • Index Cond: (client_id = 1,002)
38. 284.725 284.725 ↑ 1.0 1 1,627

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

  • Index Cond: (du_2.user_id = id)
39. 0.000 0.000 ↓ 0.0 0 1,627

Index Scan using pk_user_group on user_group ug_4 (cost=0.13..0.15 rows=1 width=278) (actual time=0.000..0.000 rows=0 loops=1,627)

  • Index Cond: (du_2.user_group_id = id)
40. 3.300 507.413 ↓ 1,234.5 2,469 1

Nested Loop Left Join (cost=756.15..18,732.81 rows=2 width=311) (actual time=29.310..507.413 rows=2,469 loops=1)

41. 2.156 504.113 ↓ 1,234.5 2,469 1

Nested Loop Left Join (cost=756.02..18,732.51 rows=2 width=37) (actual time=29.304..504.113 rows=2,469 loops=1)

42. 5.954 55.068 ↓ 1,234.5 2,469 1

Hash Join (cost=755.74..18,731.85 rows=2 width=24) (actual time=28.914..55.068 rows=2,469 loops=1)

  • Hash Cond: (et_1.relation_id = du_3.entity_id)
43. 45.580 45.580 ↑ 1.0 12,806 1

Seq Scan on action_item_mgmt et_1 (cost=0.00..17,928.06 rows=12,806 width=8) (actual time=0.006..45.580 rows=12,806 loops=1)

44. 0.020 3.534 ↓ 1.6 63 1

Hash (cost=755.24..755.24 rows=40 width=24) (actual time=3.533..3.534 rows=63 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
45. 0.039 3.514 ↓ 1.6 63 1

Merge Join (cost=6.97..755.24 rows=40 width=24) (actual time=0.095..3.514 rows=63 loops=1)

  • Merge Cond: (prg_1.id = crg_1.flowdown_rolegroup)
46. 0.032 3.422 ↑ 438.7 63 1

Nested Loop (cost=0.57..105,406.73 rows=27,636 width=20) (actual time=0.048..3.422 rows=63 loops=1)

47. 0.114 0.114 ↑ 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.012..0.114 rows=13 loops=1)

  • Filter: (entity_type_id = 1)
  • Rows Removed by Filter: 215
48. 2.943 3.276 ↑ 734.0 5 13

Index Scan using idx_domain_user_3 on domain_user du_3 (cost=0.42..7,491.05 rows=3,670 width=16) (actual time=0.054..0.252 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: 422
49.          

SubPlan (for Index Scan)

50. 0.000 0.333 ↓ 0.0 0 333

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

51. 0.333 0.333 ↓ 0.0 0 333

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=333)

  • Filter: (id = du_3.user_group_id)
  • Rows Removed by Filter: 8
52. 0.016 0.053 ↓ 21.3 64 1

Sort (cost=6.40..6.40 rows=3 width=16) (actual time=0.043..0.053 rows=64 loops=1)

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

Bitmap Heap Scan on role_group crg_1 (cost=3.18..6.37 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
54. 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)

55. 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)
56. 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)
57. 446.889 446.889 ↑ 1.0 1 2,469

Index Scan using pk_app_user on app_user u_2 (cost=0.28..0.33 rows=1 width=17) (actual time=0.181..0.181 rows=1 loops=2,469)

  • Index Cond: (du_3.user_id = id)
58. 0.000 0.000 ↓ 0.0 0 2,469

Index Scan using pk_user_group on user_group ug_5 (cost=0.13..0.15 rows=1 width=278) (actual time=0.000..0.000 rows=0 loops=2,469)

  • Index Cond: (du_3.user_group_id = id)
59. 0.549 1,928.982 ↓ 302.0 302 1

Sort (cost=54,700.60..54,700.60 rows=1 width=168) (actual time=1,928.837..1,928.982 rows=302 loops=1)

  • Sort Key: aim.id, r.name, ets.description, c.name, c.document_title
  • Sort Method: quicksort Memory: 93kB
60. 196.390 1,928.433 ↓ 302.0 302 1

Nested Loop (cost=9.73..54,700.59 rows=1 width=168) (actual time=1,414.087..1,928.433 rows=302 loops=1)

  • Join Filter: (aim.status_id = ets.id)
  • Rows Removed by Join Filter: 1,218,268
61. 0.277 1,526.985 ↓ 302.0 302 1

Nested Loop Left Join (cost=9.73..54,550.22 rows=1 width=150) (actual time=1,412.646..1,526.985 rows=302 loops=1)

62. 0.261 1,525.484 ↓ 204.0 204 1

Nested Loop Left Join (cost=9.31..54,547.56 rows=1 width=150) (actual time=1,412.631..1,525.484 rows=204 loops=1)

63. 0.300 1,524.815 ↓ 204.0 204 1

Nested Loop Left Join (cost=9.02..54,545.06 rows=1 width=104) (actual time=1,412.619..1,524.815 rows=204 loops=1)

64. 1.954 1,523.903 ↓ 204.0 204 1

Nested Loop (cost=8.73..54,542.56 rows=1 width=57) (actual time=1,412.606..1,523.903 rows=204 loops=1)

65. 1,395.774 1,395.774 ↓ 5.6 1,225 1

CTE Scan on domain_user_with_flowdown_rgs du (cost=0.00..41,640.43 rows=218 width=8) (actual time=1,319.093..1,395.774 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: 67,862
66.          

SubPlan (for CTE Scan)

67. 0.000 0.000 ↓ 0.0 0

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

68. 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)
69. 109.010 126.175 ↓ 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.103..0.103 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
70. 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)

71. 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)
72. 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)
73.          

SubPlan (for Bitmap Heap Scan)

74. 0.015 0.015 ↑ 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.011..0.015 rows=1 loops=1)

  • Index Cond: (id = 2,000)
75. 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)
76. 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)
77. 1.224 1.224 ↑ 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.005..0.006 rows=1 loops=204)

  • Index Cond: ((entity_id = aim.id) AND (entity_type_id = 18))
  • Heap Fetches: 300
78. 205.058 205.058 ↑ 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.679 rows=4,035 loops=302)

79.          

SubPlan (for Group)

80. 0.816 1,655.256 ↑ 1.0 1 204

Aggregate (cost=1,085.81..1,085.82 rows=1 width=32) (actual time=8.114..8.114 rows=1 loops=204)

81. 1,654.440 1,654.440 ↑ 1.0 1 204

CTE Scan on domain_user_with_flowdown_rgs du_4 (cost=0.00..1,085.80 rows=1 width=1,310) (actual time=3.363..8.110 rows=1 loops=204)

  • Filter: ((entity_id = aim.id) AND (entity_type_id = 18))
  • Rows Removed by Filter: 69,086
Planning time : 4.831 ms
Execution time : 3,592.652 ms