explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9Hab

Settings
# exclusive inclusive rows x rows loops node
1. 0.405 1,566.000 ↓ 4.5 9 1

Group (cost=277,828.79..279,409.21 rows=2 width=225) (actual time=1,564.624..1,566.000 rows=9 loops=1)

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

CTE domain_user_with_flowdown_rgs

3. 0.523 1,499.324 ↑ 35.2 868 1

Unique (cost=235,510.05..236,274.85 rows=30,592 width=1,330) (actual time=1,498.711..1,499.324 rows=868 loops=1)

4. 1.280 1,498.801 ↑ 33.1 924 1

Sort (cost=235,510.05..235,586.53 rows=30,592 width=1,330) (actual time=1,498.710..1,498.801 rows=924 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: quicksort Memory: 154kB
5. 0.128 1,497.521 ↑ 33.1 924 1

Append (cost=1,750.11..215,454.31 rows=30,592 width=1,330) (actual time=30.729..1,497.521 rows=924 loops=1)

6. 0.344 1,497.106 ↑ 33.1 924 1

Hash Left Join (cost=1,750.11..208,496.16 rows=30,590 width=315) (actual time=30.729..1,497.106 rows=924 loops=1)

  • Hash Cond: (du_1.user_group_id = ug_3.id)
7. 0.630 1,496.737 ↑ 33.1 924 1

Hash Left Join (cost=1,747.92..208,413.57 rows=30,590 width=41) (actual time=30.690..1,496.737 rows=924 loops=1)

  • Hash Cond: (du_1.user_id = u.id)
8. 0.390 1,494.647 ↑ 33.1 924 1

Nested Loop (cost=773.67..207,358.87 rows=30,590 width=24) (actual time=29.195..1,494.647 rows=924 loops=1)

9. 1.392 5.342 ↓ 1.4 123 1

Seq Scan on role_group rg (cost=773.25..881.01 rows=91 width=12) (actual time=3.994..5.342 rows=123 loops=1)

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

SubPlan (for Seq Scan)

11. 1.033 3.950 ↑ 1.0 14 1

HashAggregate (cost=773.07..773.21 rows=14 width=4) (actual time=3.948..3.950 rows=14 loops=1)

  • Group Key: am2.entity_type_id
12. 2.917 2.917 ↓ 1.0 4,556 1

Seq Scan on action_item_mgmt am2 (cost=0.00..761.69 rows=4,554 width=4) (actual time=0.006..2.917 rows=4,556 loops=1)

  • Filter: ((NOT deleted) AND (client_id = 1,005))
  • Rows Removed by Filter: 381
13. 218.212 1,488.915 ↑ 81.8 8 123

Index Scan using idx_domain_user_3 on domain_user du_1 (cost=0.42..2,262.45 rows=654 width=16) (actual time=3.880..12.105 rows=8 loops=123)

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

SubPlan (for Index Scan)

15. 181.529 1,270.703 ↓ 0.0 0 181,529

ProjectSet (cost=0.00..2.17 rows=100 width=4) (actual time=0.007..0.007 rows=0 loops=181,529)

16. 1,089.174 1,089.174 ↓ 0.0 0 181,529

Seq Scan on user_group ug_2 (cost=0.00..1.66 rows=1 width=32) (actual time=0.006..0.006 rows=0 loops=181,529)

  • Filter: (id = du_1.user_group_id)
  • Rows Removed by Filter: 53
17. 0.426 1.460 ↑ 1.0 2,189 1

Hash (cost=946.89..946.89 rows=2,189 width=21) (actual time=1.460..1.460 rows=2,189 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 151kB
18. 1.034 1.034 ↑ 1.0 2,189 1

Seq Scan on app_user u (cost=0.00..946.89 rows=2,189 width=21) (actual time=0.006..1.034 rows=2,189 loops=1)

19. 0.014 0.025 ↑ 1.0 53 1

Hash (cost=1.53..1.53 rows=53 width=278) (actual time=0.025..0.025 rows=53 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
20. 0.011 0.011 ↑ 1.0 53 1

Seq Scan on user_group ug_3 (cost=0.00..1.53 rows=53 width=278) (actual time=0.006..0.011 rows=53 loops=1)

21. 0.001 0.184 ↓ 0.0 0 1

Nested Loop Left Join (cost=2,477.07..3,244.99 rows=1 width=315) (actual time=0.184..0.184 rows=0 loops=1)

22. 0.000 0.183 ↓ 0.0 0 1

Nested Loop Left Join (cost=2,476.93..3,244.83 rows=1 width=41) (actual time=0.183..0.183 rows=0 loops=1)

23. 0.053 0.183 ↓ 0.0 0 1

Hash Join (cost=2,476.65..3,244.51 rows=1 width=24) (actual time=0.183..0.183 rows=0 loops=1)

  • Hash Cond: (et.contract_id = du_2.entity_id)
24. 0.007 0.007 ↑ 4,935.0 1 1

Seq Scan on action_item_mgmt et (cost=0.00..749.35 rows=4,935 width=8) (actual time=0.007..0.007 rows=1 loops=1)

  • Filter: (NOT deleted)
25. 0.000 0.123 ↓ 0.0 0 1

Hash (cost=2,476.56..2,476.56 rows=7 width=24) (actual time=0.123..0.123 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
26. 0.001 0.123 ↓ 0.0 0 1

Nested Loop (cost=79.91..2,476.56 rows=7 width=24) (actual time=0.123..0.123 rows=0 loops=1)

27. 0.010 0.122 ↓ 0.0 0 1

Hash Join (cost=79.49..186.18 rows=1 width=20) (actual time=0.122..0.122 rows=0 loops=1)

  • Hash Cond: (prg.id = crg.flowdown_rolegroup)
28. 0.037 0.037 ↑ 84.0 1 1

Seq Scan on role_group prg (cost=0.00..104.69 rows=84 width=4) (actual time=0.036..0.037 rows=1 loops=1)

  • Filter: (entity_type_id = 61)
29. 0.002 0.075 ↓ 0.0 0 1

Hash (cost=79.28..79.28 rows=17 width=16) (actual time=0.075..0.075 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
30. 0.030 0.073 ↑ 1.0 17 1

Bitmap Heap Scan on role_group crg (cost=34.52..79.28 rows=17 width=16) (actual time=0.050..0.073 rows=17 loops=1)

  • Recheck Cond: ((entity_type_id = 18) AND (client_id = 1,005))
  • Heap Blocks: exact=10
31. 0.004 0.043 ↓ 0.0 0 1

BitmapAnd (cost=34.52..34.52 rows=17 width=0) (actual time=0.043..0.043 rows=0 loops=1)

32. 0.019 0.019 ↑ 1.0 63 1

Bitmap Index Scan on role_group_entity_type_id_idx (cost=0.00..8.75 rows=63 width=0) (actual time=0.019..0.019 rows=63 loops=1)

  • Index Cond: (entity_type_id = 18)
33. 0.020 0.020 ↑ 1.0 165 1

Bitmap Index Scan on idx_role_grp_client_id (cost=0.00..25.51 rows=165 width=0) (actual time=0.020..0.020 rows=165 loops=1)

  • Index Cond: (client_id = 1,005)
34. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_domain_user_3 on domain_user du_2 (cost=0.42..2,283.83 rows=654 width=16) (never executed)

  • Index Cond: (role_group_id = prg.id)
  • Filter: ((NOT entity_deleted) AND ((user_id = 2,649) OR (SubPlan 2)))
35.          

SubPlan (for Index Scan)

36. 0.000 0.000 ↓ 0.0 0

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

37. 0.000 0.000 ↓ 0.0 0

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

  • Filter: (id = du_2.user_group_id)
38. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_app_user on app_user u_1 (cost=0.28..0.32 rows=1 width=21) (never executed)

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

Index Scan using pk_user_group on user_group ug_4 (cost=0.14..0.16 rows=1 width=278) (never executed)

  • Index Cond: (du_2.user_group_id = id)
40. 0.000 0.103 ↓ 0.0 0 1

Nested Loop Left Join (cost=2,639.32..3,407.24 rows=1 width=315) (actual time=0.103..0.103 rows=0 loops=1)

41. 0.001 0.103 ↓ 0.0 0 1

Nested Loop Left Join (cost=2,639.18..3,407.08 rows=1 width=41) (actual time=0.103..0.103 rows=0 loops=1)

42. 0.007 0.102 ↓ 0.0 0 1

Hash Join (cost=2,638.90..3,406.77 rows=1 width=24) (actual time=0.102..0.102 rows=0 loops=1)

  • Hash Cond: (et_1.relation_id = du_3.entity_id)
43. 0.005 0.005 ↑ 4,935.0 1 1

Seq Scan on action_item_mgmt et_1 (cost=0.00..749.35 rows=4,935 width=8) (actual time=0.005..0.005 rows=1 loops=1)

  • Filter: (NOT deleted)
44. 0.000 0.090 ↓ 0.0 0 1

Hash (cost=2,638.85..2,638.85 rows=4 width=24) (actual time=0.090..0.090 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
45. 0.001 0.090 ↓ 0.0 0 1

Nested Loop (cost=119.34..2,638.85 rows=4 width=24) (actual time=0.090..0.090 rows=0 loops=1)

46. 0.010 0.089 ↓ 0.0 0 1

Hash Join (cost=88.12..170.45 rows=1 width=20) (actual time=0.089..0.089 rows=0 loops=1)

  • Hash Cond: (prg_1.id = crg_1.flowdown_rolegroup)
47. 0.004 0.017 ↑ 46.0 1 1

Bitmap Heap Scan on role_group prg_1 (cost=8.63..89.85 rows=46 width=4) (actual time=0.017..0.017 rows=1 loops=1)

  • Recheck Cond: (entity_type_id = 1)
  • Heap Blocks: exact=1
48. 0.013 0.013 ↑ 1.0 46 1

Bitmap Index Scan on role_group_entity_type_id_idx (cost=0.00..8.62 rows=46 width=0) (actual time=0.013..0.013 rows=46 loops=1)

  • Index Cond: (entity_type_id = 1)
49. 0.002 0.062 ↓ 0.0 0 1

Hash (cost=79.28..79.28 rows=17 width=16) (actual time=0.062..0.062 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
50. 0.020 0.060 ↑ 1.0 17 1

Bitmap Heap Scan on role_group crg_1 (cost=34.52..79.28 rows=17 width=16) (actual time=0.044..0.060 rows=17 loops=1)

  • Recheck Cond: ((entity_type_id = 18) AND (client_id = 1,005))
  • Heap Blocks: exact=10
51. 0.002 0.040 ↓ 0.0 0 1

BitmapAnd (cost=34.52..34.52 rows=17 width=0) (actual time=0.040..0.040 rows=0 loops=1)

52. 0.018 0.018 ↑ 1.0 63 1

Bitmap Index Scan on role_group_entity_type_id_idx (cost=0.00..8.75 rows=63 width=0) (actual time=0.018..0.018 rows=63 loops=1)

  • Index Cond: (entity_type_id = 18)
53. 0.020 0.020 ↑ 1.0 165 1

Bitmap Index Scan on idx_role_grp_client_id (cost=0.00..25.51 rows=165 width=0) (actual time=0.020..0.020 rows=165 loops=1)

  • Index Cond: (client_id = 1,005)
54. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on domain_user du_3 (cost=31.22..2,461.86 rows=654 width=16) (never executed)

  • Recheck Cond: (role_group_id = prg_1.id)
  • Filter: ((NOT entity_deleted) AND ((user_id = 2,649) OR (SubPlan 1)))
55. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_domain_user_3 (cost=0.00..31.06 rows=1,615 width=0) (never executed)

  • Index Cond: (role_group_id = prg_1.id)
56.          

SubPlan (for Bitmap Heap Scan)

57. 0.000 0.000 ↓ 0.0 0

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

58. 0.000 0.000 ↓ 0.0 0

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

  • Filter: (id = du_3.user_group_id)
59. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_app_user on app_user u_2 (cost=0.28..0.32 rows=1 width=21) (never executed)

  • Index Cond: (du_3.user_id = id)
60. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_user_group on user_group ug_5 (cost=0.14..0.16 rows=1 width=278) (never executed)

  • Index Cond: (du_3.user_group_id = id)
61. 0.041 1,564.290 ↓ 5.0 10 1

Sort (cost=41,553.94..41,553.94 rows=2 width=101) (actual time=1,564.286..1,564.290 rows=10 loops=1)

  • Sort Key: aim.id, r.name, ets.description, c.name, c.document_title
  • Sort Method: quicksort Memory: 26kB
62. 6.140 1,564.249 ↓ 5.0 10 1

Nested Loop (cost=9.00..41,553.93 rows=2 width=101) (actual time=1,551.245..1,564.249 rows=10 loops=1)

  • Join Filter: (aim.status_id = ets.id)
  • Rows Removed by Join Filter: 50,880
63. 0.875 0.875 ↑ 1.0 5,089 1

Seq Scan on work_flow_status ets (cost=0.00..123.89 rows=5,089 width=22) (actual time=0.014..0.875 rows=5,089 loops=1)

64. 6.289 1,557.234 ↓ 5.0 10 5,089

Materialize (cost=9.00..41,277.37 rows=2 width=83) (actual time=0.304..0.306 rows=10 loops=5,089)

65. 0.008 1,550.945 ↓ 5.0 10 1

Nested Loop Left Join (cost=9.00..41,277.36 rows=2 width=83) (actual time=1,548.834..1,550.945 rows=10 loops=1)

66. 0.017 1,550.865 ↓ 4.5 9 1

Nested Loop Left Join (cost=8.58..41,260.46 rows=2 width=83) (actual time=1,548.818..1,550.865 rows=9 loops=1)

67. 0.560 1,550.812 ↓ 4.5 9 1

Nested Loop Left Join (cost=8.29..41,246.99 rows=2 width=53) (actual time=1,548.798..1,550.812 rows=9 loops=1)

  • Join Filter: (r.id = aim.relation_id)
  • Rows Removed by Join Filter: 4,843
68. 0.094 1,549.496 ↓ 4.5 9 1

Nested Loop (cost=8.29..41,179.91 rows=2 width=42) (actual time=1,548.738..1,549.496 rows=9 loops=1)

  • Join Filter: ((du.entity_id = aim.id) OR ((du.entity_id = aim.entity_id) AND (hashed SubPlan 8)))
  • Rows Removed by Join Filter: 321
69. 1,499.662 1,499.662 ↑ 2.6 30 1

CTE Scan on domain_user_with_flowdown_rgs du (cost=0.00..37,857.60 rows=77 width=4) (actual time=1,498.947..1,499.662 rows=30 loops=1)

  • Filter: ((role_group_id = 2,192) AND ((user_id = 2,649) OR (SubPlan 7)))
  • Rows Removed by Filter: 838
70.          

SubPlan (for CTE Scan)

71. 0.000 0.000 ↓ 0.0 0

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

72. 0.000 0.000 ↓ 0.0 0

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

  • Filter: (id = du.user_group_id)
73. 0.065 49.740 ↑ 2.1 11 30

Materialize (cost=0.00..3,278.65 rows=23 width=50) (actual time=0.039..1.658 rows=11 loops=30)

74. 49.675 49.675 ↑ 2.1 11 1

Seq Scan on action_item_mgmt aim (cost=0.00..3,278.54 rows=23 width=50) (actual time=1.161..49.675 rows=11 loops=1)

  • Filter: ((NOT deleted) AND (client_id = 1,005) AND ((change_tz(planned_completion_date, 'UTC'::character varying, 'Asia/Kolkata'::character varying))::date >= '2020-08-24 00:00:00'::timestamp without time zone) AND ((change_tz(planned_completion_date, 'UTC'::character varying, 'Asia/Kolkata'::character varying))::date <= '2020-10-16 00:00:00'::timestamp without time zone))
  • Rows Removed by Filter: 4,926
75.          

SubPlan (for Nested Loop)

76. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (id = 2,192)
77. 0.497 0.756 ↑ 1.6 539 9

Materialize (cost=0.00..43.73 rows=849 width=19) (actual time=0.001..0.084 rows=539 loops=9)

78. 0.259 0.259 ↑ 1.0 818 1

Seq Scan on relation r (cost=0.00..39.49 rows=849 width=19) (actual time=0.010..0.259 rows=818 loops=1)

79. 0.036 0.036 ↑ 1.0 1 9

Index Scan using pk_contract on contract c (cost=0.28..6.73 rows=1 width=38) (actual time=0.004..0.004 rows=1 loops=9)

  • Index Cond: (id = aim.contract_id)
80. 0.072 0.072 ↑ 1.0 1 9

Index Only Scan using idx_link_entity_function_6 on link_entity_function lef (cost=0.42..8.44 rows=1 width=4) (actual time=0.007..0.008 rows=1 loops=9)

  • Index Cond: ((entity_id = aim.id) AND (entity_type_id = 18))
  • Heap Fetches: 10
81.          

SubPlan (for Group)

82. 0.045 1.305 ↑ 1.0 1 9

Aggregate (cost=789.93..789.94 rows=1 width=32) (actual time=0.145..0.145 rows=1 loops=9)

83. 0.029 1.260 ↑ 16.0 1 9

Hash Join (cost=101.04..789.76 rows=16 width=1,310) (actual time=0.057..0.140 rows=1 loops=9)

  • Hash Cond: (du_4.role_group_id = rg_1.id)
84. 1.143 1.143 ↑ 153.0 1 9

CTE Scan on domain_user_with_flowdown_rgs du_4 (cost=0.00..688.32 rows=153 width=1,314) (actual time=0.044..0.127 rows=1 loops=9)

  • Filter: (entity_id = aim.id)
  • Rows Removed by Filter: 867
85. 0.017 0.088 ↑ 1.0 63 1

Hash (cost=100.25..100.25 rows=63 width=4) (actual time=0.088..0.088 rows=63 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
86. 0.046 0.071 ↑ 1.0 63 1

Bitmap Heap Scan on role_group rg_1 (cost=8.76..100.25 rows=63 width=4) (actual time=0.033..0.071 rows=63 loops=1)

  • Recheck Cond: (entity_type_id = 18)
  • Heap Blocks: exact=15
87. 0.025 0.025 ↑ 1.0 63 1

Bitmap Index Scan on role_group_entity_type_id_idx (cost=0.00..8.75 rows=63 width=0) (actual time=0.025..0.025 rows=63 loops=1)

  • Index Cond: (entity_type_id = 18)
Planning time : 9.345 ms
Execution time : 1,567.013 ms