explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sJM

Settings
# exclusive inclusive rows x rows loops node
1. 8.883 7,750.764 ↓ 1,399.0 4,197 1

HashAggregate (cost=21,179.96..21,179.99 rows=3 width=1,212) (actual time=7,748.680..7,750.764 rows=4,197 loops=1)

  • Group Key: "*SELECT* 1".duedate, "*SELECT* 1".id, "*SELECT* 1".clientid, "*SELECT* 1".cliententityseqid, "*SELECT* 1".name, (61), "*SELECT* 1".status, "*SELECT* 1".relation, "*SELECT* 1".entitystatus, "*SELECT* 1".contract, "*SELECT* 1".stakeholder, (NULL::text)
2.          

CTE domain_user_with_flowdown_rgs

3. 16.224 218.660 ↓ 3.6 25,923 1

Unique (cost=105,923.98..106,102.18 rows=7,128 width=1,330) (actual time=197.410..218.660 rows=25,923 loops=1)

4. 16.858 202.436 ↓ 3.6 25,923 1

Sort (cost=105,923.98..105,941.80 rows=7,128 width=1,330) (actual time=197.408..202.436 rows=25,923 loops=1)

  • Sort Key: du_3.entity_id, du_3.role_group_id, du_3.user_group_id, du_3.user_id, rg.client_id, rg.entity_type_id, u.first_name, u.last_name, ug_2.name
  • Sort Method: quicksort Memory: 2,794kB
5. 6.791 185.578 ↓ 3.6 25,923 1

Append (cost=966.88..103,040.62 rows=7,128 width=1,330) (actual time=35.055..185.578 rows=25,923 loops=1)

6. 6.710 74.250 ↓ 3.2 20,505 1

Hash Left Join (cost=966.88..31,723.01 rows=6,505 width=311) (actual time=35.054..74.250 rows=20,505 loops=1)

  • Hash Cond: (du_3.user_group_id = ug_2.id)
7. 7.847 67.526 ↓ 3.2 20,505 1

Hash Left Join (cost=965.70..31,704.76 rows=6,505 width=37) (actual time=35.028..67.526 rows=20,505 loops=1)

  • Hash Cond: (du_3.user_id = u.id)
8. 6.142 26.688 ↓ 3.2 20,505 1

Nested Loop (cost=74.82..30,796.67 rows=6,505 width=24) (actual time=2.030..26.688 rows=20,505 loops=1)

9. 0.136 0.136 ↑ 1.5 2 1

Index Scan using pk_role_group on role_group rg (cost=0.14..17.37 rows=3 width=12) (actual time=0.016..0.136 rows=2 loops=1)

  • Filter: ((entity_type_id = 61) AND (client_id = 1,002))
  • Rows Removed by Filter: 226
10. 16.918 20.410 ↓ 2.8 10,252 2

Bitmap Heap Scan on domain_user du_3 (cost=74.67..10,223.17 rows=3,659 width=16) (actual time=1.011..10.205 rows=10,252 loops=2)

  • Recheck Cond: (role_group_id = rg.id)
  • Filter: ((NOT entity_deleted) AND ((user_id = 1,044) OR (SubPlan 1)))
  • Rows Removed by Filter: 7,825
  • Heap Blocks: exact=2,881
11. 1.666 1.666 ↓ 2.6 18,103 2

Bitmap Index Scan on idx_domain_user_3 (cost=0.00..73.76 rows=6,991 width=0) (actual time=0.832..0.833 rows=18,103 loops=2)

  • Index Cond: (role_group_id = rg.id)
12.          

SubPlan (for Bitmap Heap Scan)

13. 0.000 1.826 ↓ 0.0 0 1,826

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

14. 1.826 1.826 ↓ 0.0 0 1,826

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

  • Filter: (id = du_3.user_group_id)
  • Rows Removed by Filter: 8
15. 0.194 32.991 ↑ 1.0 551 1

Hash (cost=884.00..884.00 rows=551 width=17) (actual time=32.990..32.991 rows=551 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 36kB
16. 32.797 32.797 ↑ 1.0 551 1

Index Scan using pk_app_user on app_user u (cost=0.28..884.00 rows=551 width=17) (actual time=0.007..32.797 rows=551 loops=1)

17. 0.004 0.014 ↑ 1.0 8 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
18. 0.010 0.010 ↑ 1.0 8 1

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

19. 2.053 104.537 ↓ 8.7 5,418 1

Hash Left Join (cost=678.91..71,210.68 rows=623 width=311) (actual time=3.242..104.537 rows=5,418 loops=1)

  • Hash Cond: (du_4.user_group_id = ug_3.id)
20. 3.476 102.476 ↓ 8.7 5,418 1

Nested Loop Left Join (cost=677.73..71,207.87 rows=623 width=37) (actual time=3.226..102.476 rows=5,418 loops=1)

21. 7.707 66.492 ↓ 8.7 5,418 1

Hash Join (cost=677.45..71,005.55 rows=623 width=24) (actual time=3.209..66.492 rows=5,418 loops=1)

  • Hash Cond: ((unnest(contract.relation_ids)) = du_4.entity_id)
22. 38.461 55.603 ↑ 92.6 37,194 1

ProjectSet (cost=0.00..22,943.50 rows=3,445,700 width=8) (actual time=0.014..55.603 rows=37,194 loops=1)

23. 17.142 17.142 ↑ 1.0 34,457 1

Seq Scan on contract (cost=0.00..5,456.57 rows=34,457 width=29) (actual time=0.006..17.142 rows=34,457 loops=1)

24. 0.022 3.182 ↓ 1.9 71 1

Hash (cost=676.98..676.98 rows=38 width=24) (actual time=3.181..3.182 rows=71 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
25. 0.041 3.160 ↓ 1.9 71 1

Merge Join (cost=6.91..676.98 rows=38 width=24) (actual time=0.072..3.160 rows=71 loops=1)

  • Merge Cond: (prg.id = crg.flowdown_rolegroup)
26. 0.027 3.073 ↑ 366.5 71 1

Nested Loop (cost=0.57..91,662.32 rows=26,019 width=20) (actual time=0.032..3.073 rows=71 loops=1)

27. 0.069 0.069 ↓ 1.1 13 1

Index Scan using pk_role_group on role_group prg (cost=0.14..16.83 rows=12 width=4) (actual time=0.011..0.069 rows=13 loops=1)

  • Filter: (entity_type_id = 1)
  • Rows Removed by Filter: 215
28. 2.644 2.977 ↑ 731.8 5 13

Index Scan using idx_domain_user_3 on domain_user du_4 (cost=0.42..7,600.53 rows=3,659 width=16) (actual time=0.051..0.229 rows=5 loops=13)

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

SubPlan (for Index Scan)

30. 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)

31. 0.333 0.333 ↓ 0.0 0 333

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

  • Filter: (id = du_4.user_group_id)
  • Rows Removed by Filter: 8
32. 0.019 0.046 ↓ 24.0 72 1

Sort (cost=6.34..6.35 rows=3 width=16) (actual time=0.035..0.046 rows=72 loops=1)

  • Sort Key: crg.flowdown_rolegroup
  • Sort Method: quicksort Memory: 25kB
33. 0.008 0.027 ↑ 1.5 2 1

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

  • Recheck Cond: ((entity_type_id = 61) AND (client_id = 1,002))
  • Heap Blocks: exact=5
34. 0.002 0.019 ↓ 0.0 0 1

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

35. 0.008 0.008 ↓ 1.2 20 1

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

  • Index Cond: (entity_type_id = 61)
36. 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)
37. 32.508 32.508 ↑ 1.0 1 5,418

Index Scan using pk_app_user on app_user u_1 (cost=0.28..0.32 rows=1 width=17) (actual time=0.006..0.006 rows=1 loops=5,418)

  • Index Cond: (du_4.user_id = id)
38. 0.004 0.008 ↑ 1.0 8 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
39. 0.004 0.004 ↑ 1.0 8 1

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

40. 1.610 7,741.881 ↓ 1,399.0 4,197 1

Append (cost=6,882.47..21,179.87 rows=3 width=1,212) (actual time=483.833..7,741.881 rows=4,197 loops=1)

41. 2.009 7,569.595 ↓ 4,196.0 4,196 1

Subquery Scan on *SELECT* 1 (cost=6,882.47..7,054.08 rows=1 width=242) (actual time=483.832..7,569.595 rows=4,196 loops=1)

42. 63.839 7,567.586 ↓ 4,196.0 4,196 1

Group (cost=6,882.47..7,054.07 rows=1 width=267) (actual time=483.830..7,567.586 rows=4,196 loops=1)

  • Group Key: c.id, r.name, ets.description
43. 6.975 483.839 ↓ 8,359.0 8,359 1

Sort (cost=6,882.47..6,882.47 rows=1 width=143) (actual time=481.827..483.839 rows=8,359 loops=1)

  • Sort Key: c.id, r.name, ets.description
  • Sort Method: quicksort Memory: 1,562kB
44. 7.233 476.864 ↓ 8,359.0 8,359 1

Nested Loop (cost=1.27..6,882.46 rows=1 width=143) (actual time=198.627..476.864 rows=8,359 loops=1)

45. 4.745 461.272 ↓ 8,359.0 8,359 1

Nested Loop (cost=0.99..6,881.54 rows=1 width=125) (actual time=198.616..461.272 rows=8,359 loops=1)

46. 5.030 443.939 ↓ 4,196.0 4,196 1

Nested Loop Left Join (cost=0.57..6,878.08 rows=1 width=129) (actual time=198.601..443.939 rows=4,196 loops=1)

47. 13.247 430.535 ↓ 4,187.0 4,187 1

Nested Loop (cost=0.29..6,871.00 rows=1 width=103) (actual time=198.585..430.535 rows=4,187 loops=1)

48. 232.743 232.743 ↓ 1,139.2 20,505 1

CTE Scan on domain_user_with_flowdown_rgs du (cost=0.00..6,816.15 rows=18 width=4) (actual time=197.416..232.743 rows=20,505 loops=1)

  • Filter: ((role_group_id = 2,001) AND ((user_id = 1,044) OR (SubPlan 5)))
  • Rows Removed by Filter: 5,418
49.          

SubPlan (for CTE Scan)

50. 0.000 0.000 ↓ 0.0 0

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

51. 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)
52. 184.545 184.545 ↓ 0.0 0 20,505

Index Scan using pk_contract on contract c (cost=0.29..3.05 rows=1 width=99) (actual time=0.009..0.009 rows=0 loops=20,505)

  • Index Cond: (id = du.entity_id)
  • Filter: ((NOT deleted) AND (client_id = 1,002) AND (status_id = ANY ('{1610,1608,1606,1604,1607,1603,1605,1609,5,1515,1,1474,1473,1471,6,1517,1470,2,1472,1516}'::integer[])) AND ((change_tz(exp_date, 'UTC'::character varying, 'IST'::character varying))::date >= '2020-07-24 00:00:00'::timestamp without time zone) AND ((change_tz(exp_date, 'UTC'::character varying, 'IST'::character varying))::date <= '2020-09-15 00:00:00'::timestamp without time zone))
  • Rows Removed by Filter: 1
53. 8.374 8.374 ↑ 10.0 1 4,187

Index Scan using pk_relation on relation r (cost=0.28..6.98 rows=10 width=55) (actual time=0.002..0.002 rows=1 loops=4,187)

  • Index Cond: (id = ANY (c.relation_ids))
54. 12.588 12.588 ↑ 1.0 2 4,196

Index Only Scan using idx_link_entity_function_6 on link_entity_function lef (cost=0.42..3.44 rows=2 width=4) (actual time=0.002..0.003 rows=2 loops=4,196)

  • Index Cond: ((entity_id = c.id) AND (entity_type_id = 61))
  • Heap Fetches: 8,393
55. 8.359 8.359 ↑ 1.0 1 8,359

Index Scan using pk_work_flow_status on work_flow_status ets (cost=0.28..0.92 rows=1 width=22) (actual time=0.001..0.001 rows=1 loops=8,359)

  • Index Cond: (id = c.status_id)
56.          

SubPlan (for Group)

57. 8.392 7,019.908 ↑ 1.0 1 4,196

Aggregate (cost=171.32..171.33 rows=1 width=32) (actual time=1.673..1.673 rows=1 loops=4,196)

58. 8.359 7,011.516 ↑ 3.0 1 4,196

Hash Join (cost=10.82..171.29 rows=3 width=1,310) (actual time=1.455..1.671 rows=1 loops=4,196)

  • Hash Cond: (du_5.role_group_id = rg_1.id)
59. 7,003.124 7,003.124 ↑ 36.0 1 4,196

CTE Scan on domain_user_with_flowdown_rgs du_5 (cost=0.00..160.38 rows=36 width=1,314) (actual time=1.453..1.669 rows=1 loops=4,196)

  • Filter: (entity_id = c.id)
  • Rows Removed by Filter: 25,922
60. 0.005 0.033 ↓ 1.1 17 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
61. 0.019 0.028 ↓ 1.1 17 1

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

  • Recheck Cond: (entity_type_id = 61)
  • Heap Blocks: exact=5
62. 0.009 0.009 ↓ 1.2 20 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=20 loops=1)

  • Index Cond: (entity_type_id = 61)
63. 0.002 92.759 ↑ 1.0 1 1

Subquery Scan on *SELECT* 2 (cost=6,882.55..7,062.85 rows=1 width=242) (actual time=92.758..92.759 rows=1 loops=1)

64. 0.028 92.757 ↑ 1.0 1 1

Group (cost=6,882.55..7,062.84 rows=1 width=267) (actual time=92.756..92.757 rows=1 loops=1)

  • Group Key: c_1.id, r_1.name, ets_1.description
65. 0.014 80.574 ↑ 1.0 1 1

Sort (cost=6,882.55..6,882.56 rows=1 width=143) (actual time=80.574..80.574 rows=1 loops=1)

  • Sort Key: c_1.id, r_1.name, ets_1.description
  • Sort Method: quicksort Memory: 25kB
66. 0.003 80.560 ↑ 1.0 1 1

Nested Loop (cost=1.27..6,882.54 rows=1 width=143) (actual time=80.501..80.560 rows=1 loops=1)

67. 0.005 80.548 ↑ 1.0 1 1

Nested Loop (cost=0.99..6,881.62 rows=1 width=125) (actual time=80.489..80.548 rows=1 loops=1)

68. 0.005 80.530 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.57..6,878.16 rows=1 width=129) (actual time=80.473..80.530 rows=1 loops=1)

69. 11.852 80.512 ↑ 1.0 1 1

Nested Loop (cost=0.29..6,871.05 rows=1 width=103) (actual time=80.456..80.512 rows=1 loops=1)

70. 7.145 7.145 ↓ 1,139.2 20,505 1

CTE Scan on domain_user_with_flowdown_rgs du_1 (cost=0.00..6,816.15 rows=18 width=4) (actual time=0.002..7.145 rows=20,505 loops=1)

  • Filter: ((role_group_id = 2,001) AND ((user_id = 1,044) OR (SubPlan 7)))
  • Rows Removed by Filter: 5,418
71.          

SubPlan (for CTE Scan)

72. 0.000 0.000 ↓ 0.0 0

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

73. 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_1.user_group_id)
74. 61.515 61.515 ↓ 0.0 0 20,505

Index Scan using pk_contract on contract c_1 (cost=0.29..3.05 rows=1 width=99) (actual time=0.003..0.003 rows=0 loops=20,505)

  • Index Cond: (id = du_1.entity_id)
  • Filter: ((NOT deleted) AND (exp_date <> notice_lead_date) AND (client_id = 1,002) AND (status_id = ANY ('{1610,1608,1606,1604,1607,1603,1605,1609,5,1515,1,1474,1473,1471,6,1517,1470,2,1472,1516}'::integer[])) AND ((change_tz(notice_lead_date, 'UTC'::character varying, 'IST'::character varying))::date >= '2020-07-24 00:00:00'::timestamp without time zone) AND ((change_tz(notice_lead_date, 'UTC'::character varying, 'IST'::character varying))::date <= '2020-09-15 00:00:00'::timestamp without time zone))
  • Rows Removed by Filter: 1
75. 0.013 0.013 ↑ 10.0 1 1

Index Scan using pk_relation on relation r_1 (cost=0.28..7.01 rows=10 width=55) (actual time=0.012..0.013 rows=1 loops=1)

  • Index Cond: (id = ANY (c_1.relation_ids))
76. 0.013 0.013 ↑ 2.0 1 1

Index Only Scan using idx_link_entity_function_6 on link_entity_function lef_1 (cost=0.42..3.44 rows=2 width=4) (actual time=0.012..0.013 rows=1 loops=1)

  • Index Cond: ((entity_id = c_1.id) AND (entity_type_id = 61))
  • Heap Fetches: 1
77. 0.009 0.009 ↑ 1.0 1 1

Index Scan using pk_work_flow_status on work_flow_status ets_1 (cost=0.28..0.92 rows=1 width=22) (actual time=0.009..0.009 rows=1 loops=1)

  • Index Cond: (id = c_1.status_id)
78.          

SubPlan (for Group)

79. 0.008 12.155 ↑ 1.0 1 1

Aggregate (cost=180.00..180.01 rows=1 width=32) (actual time=12.155..12.155 rows=1 loops=1)

80. 0.008 12.147 ↑ 1.5 2 1

Nested Loop Left Join (cost=12.11..179.96 rows=3 width=291) (actual time=7.470..12.147 rows=2 loops=1)

  • Join Filter: (du_6.user_group_id = ug_5.id)
  • Rows Removed by Join Filter: 16
81. 0.009 12.123 ↑ 1.5 2 1

Nested Loop Left Join (cost=12.11..178.50 rows=3 width=21) (actual time=7.451..12.123 rows=2 loops=1)

82. 0.014 1.854 ↑ 1.5 2 1

Hash Join (cost=10.82..171.29 rows=3 width=8) (actual time=1.849..1.854 rows=2 loops=1)

  • Hash Cond: (du_6.role_group_id = rg_2.id)
83. 1.804 1.804 ↑ 18.0 2 1

CTE Scan on domain_user_with_flowdown_rgs du_6 (cost=0.00..160.38 rows=36 width=12) (actual time=1.801..1.804 rows=2 loops=1)

  • Filter: (entity_id = c_1.id)
  • Rows Removed by Filter: 25,921
84. 0.006 0.036 ↓ 1.1 17 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
85. 0.019 0.030 ↓ 1.1 17 1

Bitmap Heap Scan on role_group rg_2 (cost=1.37..10.62 rows=16 width=4) (actual time=0.019..0.030 rows=17 loops=1)

  • Recheck Cond: (entity_type_id = 61)
  • Heap Blocks: exact=5
86. 0.011 0.011 ↓ 1.2 20 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=20 loops=1)

  • Index Cond: (entity_type_id = 61)
87. 8.740 10.260 ↑ 1.0 1 2

Bitmap Heap Scan on app_user u_2 (cost=1.29..2.40 rows=1 width=17) (actual time=5.129..5.130 rows=1 loops=2)

  • Recheck Cond: (du_6.user_id = id)
  • Heap Blocks: exact=10,234
88. 1.520 1.520 ↓ 5,121.0 5,121 2

Bitmap Index Scan on pk_app_user (cost=0.00..1.29 rows=1 width=0) (actual time=0.760..0.760 rows=5,121 loops=2)

  • Index Cond: (du_6.user_id = id)
89. 0.007 0.016 ↑ 1.0 8 2

Materialize (cost=0.00..1.12 rows=8 width=278) (actual time=0.005..0.008 rows=8 loops=2)

90. 0.009 0.009 ↑ 1.0 8 1

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

91. 0.001 77.917 ↓ 0.0 0 1

Subquery Scan on *SELECT* 3 (cost=6,882.64..7,062.93 rows=1 width=242) (actual time=77.917..77.917 rows=0 loops=1)

92. 0.001 77.916 ↓ 0.0 0 1

Group (cost=6,882.64..7,062.92 rows=1 width=267) (actual time=77.916..77.916 rows=0 loops=1)

  • Group Key: c_2.id, r_2.name, ets_2.description
93. 0.009 77.915 ↓ 0.0 0 1

Sort (cost=6,882.64..6,882.64 rows=1 width=143) (actual time=77.915..77.915 rows=0 loops=1)

  • Sort Key: c_2.id, r_2.name, ets_2.description
  • Sort Method: quicksort Memory: 25kB
94. 0.001 77.906 ↓ 0.0 0 1

Nested Loop (cost=1.27..6,882.63 rows=1 width=143) (actual time=77.906..77.906 rows=0 loops=1)

95. 0.001 77.905 ↓ 0.0 0 1

Nested Loop (cost=0.99..6,881.70 rows=1 width=125) (actual time=77.905..77.905 rows=0 loops=1)

96. 0.001 77.904 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.57..6,878.24 rows=1 width=129) (actual time=77.904..77.904 rows=0 loops=1)

97. 9.211 77.903 ↓ 0.0 0 1

Nested Loop (cost=0.29..6,871.09 rows=1 width=103) (actual time=77.903..77.903 rows=0 loops=1)

98. 7.177 7.177 ↓ 1,139.2 20,505 1

CTE Scan on domain_user_with_flowdown_rgs du_2 (cost=0.00..6,816.15 rows=18 width=4) (actual time=0.002..7.177 rows=20,505 loops=1)

  • Filter: ((role_group_id = 2,001) AND ((user_id = 1,044) OR (SubPlan 9)))
  • Rows Removed by Filter: 5,418
99.          

SubPlan (for CTE Scan)

100. 0.000 0.000 ↓ 0.0 0

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

101. 0.000 0.000 ↓ 0.0 0

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

  • Filter: (id = du_2.user_group_id)
102. 61.515 61.515 ↓ 0.0 0 20,505

Index Scan using pk_contract on contract c_2 (cost=0.29..3.05 rows=1 width=99) (actual time=0.003..0.003 rows=0 loops=20,505)

  • Index Cond: (id = du_2.entity_id)
  • Filter: ((NOT deleted) AND (notice_date <> notice_lead_date) AND (notice_date <> exp_date) AND (client_id = 1,002) AND (status_id = ANY ('{1610,1608,1606,1604,1607,1603,1605,1609,5,1515,1,1474,1473,1471,6,1517,1470,2,1472,1516}'::integer[])) AND ((change_tz(notice_date, 'UTC'::character varying, 'IST'::character varying))::date >= '2020-07-24 00:00:00'::timestamp without time zone) AND ((change_tz(notice_date, 'UTC'::character varying, 'IST'::character varying))::date <= '2020-09-15 00:00:00'::timestamp without time zone))
  • Rows Removed by Filter: 1
103. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_relation on relation r_2 (cost=0.28..7.04 rows=10 width=55) (never executed)

  • Index Cond: (id = ANY (c_2.relation_ids))
104. 0.000 0.000 ↓ 0.0 0

Index Only Scan using idx_link_entity_function_6 on link_entity_function lef_2 (cost=0.42..3.44 rows=2 width=4) (never executed)

  • Index Cond: ((entity_id = c_2.id) AND (entity_type_id = 61))
  • Heap Fetches: 0
105. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_work_flow_status on work_flow_status ets_2 (cost=0.28..0.92 rows=1 width=22) (never executed)

  • Index Cond: (id = c_2.status_id)
106.          

SubPlan (for Group)

107. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=180.00..180.01 rows=1 width=32) (never executed)

108. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=12.11..179.96 rows=3 width=291) (never executed)

  • Join Filter: (du_7.user_group_id = ug_7.id)
109. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=12.11..178.50 rows=3 width=21) (never executed)

110. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=10.82..171.29 rows=3 width=8) (never executed)

  • Hash Cond: (du_7.role_group_id = rg_3.id)
111. 0.000 0.000 ↓ 0.0 0

CTE Scan on domain_user_with_flowdown_rgs du_7 (cost=0.00..160.38 rows=36 width=12) (never executed)

  • Filter: (entity_id = c_2.id)
112. 0.000 0.000 ↓ 0.0 0

Hash (cost=10.62..10.62 rows=16 width=4) (never executed)

113. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on role_group rg_3 (cost=1.37..10.62 rows=16 width=4) (never executed)

  • Recheck Cond: (entity_type_id = 61)
114. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on role_group_entity_type_id_idx (cost=0.00..1.37 rows=16 width=0) (never executed)

  • Index Cond: (entity_type_id = 61)
115. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on app_user u_3 (cost=1.29..2.40 rows=1 width=17) (never executed)

  • Recheck Cond: (du_7.user_id = id)
116. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on pk_app_user (cost=0.00..1.29 rows=1 width=0) (never executed)

  • Index Cond: (du_7.user_id = id)
117. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.00..1.12 rows=8 width=278) (never executed)

118. 0.000 0.000 ↓ 0.0 0

Seq Scan on user_group ug_7 (cost=0.00..1.08 rows=8 width=278) (never executed)

Planning time : 5.658 ms
Execution time : 7,752.772 ms