explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IMF2

Settings
# exclusive inclusive rows x rows loops node
1. 0.231 1,189.433 ↓ 4.5 9 1

Group (cost=277,828.79..279,358.99 rows=2 width=225) (actual time=1,188.577..1,189.433 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.301 1,142.082 ↑ 35.2 868 1

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

4. 1.130 1,141.781 ↑ 33.1 924 1

Sort (cost=235,510.05..235,586.53 rows=30,592 width=1,330) (actual time=1,141.730..1,141.781 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.124 1,140.651 ↑ 33.1 924 1

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

6. 0.304 1,140.201 ↑ 33.1 924 1

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

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

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

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

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

9. 0.524 3.234 ↓ 1.4 123 1

Seq Scan on role_group rg (cost=773.25..881.01 rows=91 width=12) (actual time=2.760..3.234 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. 0.693 2.710 ↑ 1.0 14 1

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

  • Group Key: am2.entity_type_id
12. 2.017 2.017 ↓ 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.007..2.017 rows=4,556 loops=1)

  • Filter: ((NOT deleted) AND (client_id = 1,005))
  • Rows Removed by Filter: 381
13. 226.538 1,134.183 ↑ 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=2.755..9.221 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 907.645 ↓ 0.0 0 181,529

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

16. 726.116 726.116 ↓ 0.0 0 181,529

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

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

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 151kB
18. 1.089 1.089 ↑ 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.089 rows=2,189 loops=1)

19. 0.012 0.022 ↑ 1.0 53 1

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

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

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

21. 0.001 0.211 ↓ 0.0 0 1

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

22. 0.001 0.210 ↓ 0.0 0 1

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

23. 0.051 0.209 ↓ 0.0 0 1

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

  • Hash Cond: (et.contract_id = du_2.entity_id)
24. 0.015 0.015 ↑ 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.015..0.015 rows=1 loops=1)

  • Filter: (NOT deleted)
25. 0.001 0.143 ↓ 0.0 0 1

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

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

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

27. 0.014 0.142 ↓ 0.0 0 1

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

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

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

  • Filter: (entity_type_id = 61)
29. 0.003 0.087 ↓ 0.0 0 1

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

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

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

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

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

32. 0.029 0.029 ↑ 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.029..0.029 rows=63 loops=1)

  • Index Cond: (entity_type_id = 18)
33. 0.018 0.018 ↑ 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.018..0.018 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.115 ↓ 0.0 0 1

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

41. 0.001 0.115 ↓ 0.0 0 1

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

42. 0.011 0.114 ↓ 0.0 0 1

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

  • Hash Cond: (et_1.relation_id = du_3.entity_id)
43. 0.007 0.007 ↑ 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.007..0.007 rows=1 loops=1)

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

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

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

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

46. 0.012 0.095 ↓ 0.0 0 1

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

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

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

  • Recheck Cond: (entity_type_id = 1)
  • Heap Blocks: exact=1
48. 0.012 0.012 ↑ 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.012..0.012 rows=46 loops=1)

  • Index Cond: (entity_type_id = 1)
49. 0.001 0.068 ↓ 0.0 0 1

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

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

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

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

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

52. 0.029 0.029 ↑ 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.029..0.029 rows=63 loops=1)

  • Index Cond: (entity_type_id = 18)
53. 0.015 0.015 ↑ 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.015..0.015 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.049 1,188.302 ↓ 5.0 10 1

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

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

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

  • Join Filter: (aim.status_id = ets.id)
  • Rows Removed by Join Filter: 50,880
63. 1.138 1.138 ↑ 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.012..1.138 rows=5,089 loops=1)

64. 4.179 1,180.648 ↓ 5.0 10 5,089

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

65. 0.002 1,176.469 ↓ 5.0 10 1

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

66. 0.010 1,176.431 ↓ 4.5 9 1

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

67. 0.322 1,176.394 ↓ 4.5 9 1

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

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

Nested Loop (cost=8.29..41,179.91 rows=2 width=42) (actual time=1,175.225..1,175.631 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,142.251 1,142.251 ↑ 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,141.869..1,142.251 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.040 33.330 ↑ 2.1 11 30

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

74. 33.290 33.290 ↑ 2.1 11 1

Seq Scan on action_item_mgmt aim (cost=0.00..3,278.54 rows=23 width=50) (actual time=0.712..33.290 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.307 0.441 ↑ 1.6 539 9

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

78. 0.134 0.134 ↑ 1.0 818 1

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

79. 0.027 0.027 ↑ 1.0 1 9

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

  • Index Cond: (id = aim.contract_id)
80. 0.036 0.036 ↑ 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.003..0.004 rows=1 loops=9)

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

SubPlan (for Group)

82. 0.027 0.900 ↑ 1.0 1 9

Aggregate (cost=764.81..764.82 rows=1 width=32) (actual time=0.100..0.100 rows=1 loops=9)

83. 0.873 0.873 ↑ 1.0 1 9

CTE Scan on domain_user_with_flowdown_rgs du_4 (cost=0.00..764.80 rows=1 width=1,310) (actual time=0.033..0.097 rows=1 loops=9)

  • Filter: ((entity_id = aim.id) AND (entity_type_id = 18))
  • Rows Removed by Filter: 867
Planning time : 6.847 ms
Execution time : 1,190.472 ms