explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rB7d

Settings
# exclusive inclusive rows x rows loops node
1. 3.083 619.133 ↓ 296.0 296 1

Group (cost=20,412.17..20,415.37 rows=1 width=293) (actual time=601.891..619.133 rows=296 loops=1)

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

CTE domain_user_with_flowdown_rgs

3. 0.802 589.212 ↓ 148.0 592 1

HashAggregate (cost=20,380.41..20,380.45 rows=4 width=1,330) (actual time=588.913..589.212 rows=592 loops=1)

  • Group 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
4. 0.201 588.410 ↓ 148.0 592 1

Append (cost=75.66..20,380.32 rows=4 width=1,330) (actual time=238.771..588.410 rows=592 loops=1)

5. 0.280 289.601 ↓ 148.0 296 1

Nested Loop Left Join (cost=75.66..14,279.81 rows=2 width=311) (actual time=238.770..289.601 rows=296 loops=1)

6. 0.274 289.321 ↓ 148.0 296 1

Nested Loop Left Join (cost=75.53..14,279.51 rows=2 width=37) (actual time=238.766..289.321 rows=296 loops=1)

7. 11.028 286.679 ↓ 148.0 296 1

Nested Loop (cost=75.26..14,278.86 rows=2 width=24) (actual time=238.747..286.679 rows=296 loops=1)

8. 8.652 25.131 ↓ 11.6 25,052 1

Nested Loop (cost=74.84..12,099.93 rows=2,169 width=24) (actual time=2.766..25.131 rows=25,052 loops=1)

9. 0.137 0.137 ↓ 2.0 2 1

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

  • Filter: ((entity_type_id = 15) AND (client_id = 1,002))
  • Rows Removed by Filter: 226
10. 14.052 16.342 ↓ 3.4 12,526 2

Bitmap Heap Scan on domain_user du_1 (cost=74.70..12,045.96 rows=3,660 width=16) (actual time=1.376..8.171 rows=12,526 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: 3,300
  • Heap Blocks: exact=1,162
11. 1.372 1.372 ↓ 2.3 15,826 2

Bitmap Index Scan on idx_domain_user_3 (cost=0.00..73.78 rows=6,994 width=0) (actual time=0.686..0.686 rows=15,826 loops=2)

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

SubPlan (for Bitmap Heap Scan)

13. 0.000 0.918 ↓ 0.0 0 918

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

14. 0.918 0.918 ↓ 0.0 0 918

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

  • Filter: (id = du_1.user_group_id)
  • Rows Removed by Filter: 8
15. 250.520 250.520 ↓ 0.0 0 25,052

Index Scan using pk_child_sla on child_sla csla_1 (cost=0.41..1.00 rows=1 width=4) (actual time=0.010..0.010 rows=0 loops=25,052)

  • Index Cond: (id = du_1.entity_id)
  • Filter: ((NOT deleted) AND ((change_tz(reporting_date, 'UTC'::character varying, 'Asia/Kolkata'::character varying))::date >= '2020-07-24 00:00:00'::timestamp without time zone) AND ((change_tz(reporting_date, 'UTC'::character varying, 'Asia/Kolkata'::character varying))::date <= '2020-09-15 00:00:00'::timestamp without time zone))
  • Rows Removed by Filter: 1
16. 2.368 2.368 ↑ 1.0 1 296

Index Scan using pk_app_user on app_user u (cost=0.28..0.33 rows=1 width=17) (actual time=0.008..0.008 rows=1 loops=296)

  • Index Cond: (du_1.user_id = id)
17. 0.000 0.000 ↓ 0.0 0 296

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

  • Index Cond: (du_1.user_group_id = id)
18. 0.000 23.760 ↓ 0.0 0 1

Nested Loop Left Join (cost=1,593.30..2,858.28 rows=1 width=311) (actual time=23.760..23.760 rows=0 loops=1)

19. 0.001 23.760 ↓ 0.0 0 1

Nested Loop Left Join (cost=1,593.17..2,858.12 rows=1 width=37) (actual time=23.759..23.760 rows=0 loops=1)

20. 0.003 23.759 ↓ 0.0 0 1

Merge Join (cost=1,592.90..2,857.80 rows=1 width=24) (actual time=23.759..23.759 rows=0 loops=1)

  • Merge Cond: (prg.id = crg.flowdown_rolegroup)
21. 0.003 23.722 ↑ 30.0 1 1

Nested Loop (cost=1,588.68..39,535.30 rows=30 width=20) (actual time=23.722..23.722 rows=1 loops=1)

  • Join Filter: (du_2.role_group_id = prg.id)
22. 0.010 0.010 ↑ 16.0 1 1

Index Scan using pk_role_group on role_group prg (cost=0.14..16.83 rows=16 width=4) (actual time=0.010..0.010 rows=1 loops=1)

  • Filter: (entity_type_id = 61)
  • Rows Removed by Filter: 1
23. 0.003 23.709 ↑ 412.0 1 1

Materialize (cost=1,588.54..39,420.61 rows=412 width=16) (actual time=23.708..23.709 rows=1 loops=1)

24. 0.004 23.706 ↑ 412.0 1 1

Nested Loop (cost=1,588.54..39,418.55 rows=412 width=16) (actual time=23.706..23.706 rows=1 loops=1)

25. 9.629 23.668 ↑ 159.0 1 1

Bitmap Heap Scan on child_sla et (cost=1,588.11..37,901.34 rows=159 width=8) (actual time=23.668..23.668 rows=1 loops=1)

  • Filter: ((NOT deleted) AND ((change_tz(reporting_date, 'UTC'::character varying, 'Asia/Kolkata'::character varying))::date >= '2020-07-24 00:00:00'::timestamp without time zone) AND ((change_tz(reporting_date, 'UTC'::character varying, 'Asia/Kolkata'::character varying))::date <= '2020-09-15 00:00:00'::timestamp without time zone))
  • Rows Removed by Filter: 1,010
  • Heap Blocks: exact=242
26. 14.039 14.039 ↓ 6.2 195,857 1

Bitmap Index Scan on idx_child_sla_deleted_client_id_slaid (cost=0.00..1,588.08 rows=31,714 width=0) (actual time=14.039..14.039 rows=195,857 loops=1)

  • Index Cond: (deleted = false)
27. 0.019 0.034 ↑ 3.0 1 1

Index Scan using idx_du_entity_id on domain_user du_2 (cost=0.42..9.51 rows=3 width=16) (actual time=0.034..0.034 rows=1 loops=1)

  • Index Cond: (entity_id = et.contract_id)
  • Filter: ((user_id = 1,044) OR (SubPlan 2))
  • Rows Removed by Filter: 5
28.          

SubPlan (for Index Scan)

29. 0.005 0.015 ↓ 0.0 0 5

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

30. 0.010 0.010 ↓ 0.0 0 5

Seq Scan on user_group ug_1 (cost=0.00..1.10 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=5)

  • Filter: (id = du_2.user_group_id)
  • Rows Removed by Filter: 8
31. 0.007 0.034 ↓ 2.0 2 1

Sort (cost=4.21..4.22 rows=1 width=16) (actual time=0.033..0.034 rows=2 loops=1)

  • Sort Key: crg.flowdown_rolegroup
  • Sort Method: quicksort Memory: 25kB
32. 0.007 0.027 ↓ 2.0 2 1

Bitmap Heap Scan on role_group crg (cost=3.09..4.20 rows=1 width=16) (actual time=0.025..0.027 rows=2 loops=1)

  • Recheck Cond: ((entity_type_id = 15) AND (client_id = 1,002))
  • Heap Blocks: exact=2
33. 0.003 0.020 ↓ 0.0 0 1

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

34. 0.008 0.008 ↑ 1.0 9 1

Bitmap Index Scan on role_group_entity_type_id_idx (cost=0.00..1.31 rows=9 width=0) (actual time=0.008..0.008 rows=9 loops=1)

  • Index Cond: (entity_type_id = 15)
35. 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)
36. 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=17) (never executed)

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

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

  • Index Cond: (du_2.user_group_id = id)
38. 0.310 274.848 ↓ 296.0 296 1

Nested Loop Left Join (cost=1,593.30..3,242.17 rows=1 width=311) (actual time=22.965..274.848 rows=296 loops=1)

39. 0.224 274.538 ↓ 296.0 296 1

Nested Loop Left Join (cost=1,593.17..3,242.01 rows=1 width=37) (actual time=22.961..274.538 rows=296 loops=1)

40. 0.243 271.946 ↓ 296.0 296 1

Merge Join (cost=1,592.90..3,241.69 rows=1 width=24) (actual time=22.946..271.946 rows=296 loops=1)

  • Merge Cond: (prg_1.id = crg_1.flowdown_rolegroup)
41. 6.566 271.609 ↓ 12.9 296 1

Nested Loop (cost=1,588.68..39,510.58 rows=23 width=20) (actual time=22.911..271.609 rows=296 loops=1)

  • Join Filter: (du_3.role_group_id = prg_1.id)
  • Rows Removed by Join Filter: 38,756
42. 0.116 0.116 ↓ 1.1 13 1

Index Scan using pk_role_group on role_group prg_1 (cost=0.14..16.83 rows=12 width=4) (actual time=0.007..0.116 rows=13 loops=1)

  • Filter: (entity_type_id = 1)
  • Rows Removed by Filter: 215
43. 6.160 264.927 ↓ 7.3 3,004 13

Materialize (cost=1,588.54..39,420.61 rows=412 width=16) (actual time=1.762..20.379 rows=3,004 loops=13)

44. 1.218 258.767 ↓ 7.3 3,004 1

Nested Loop (cost=1,588.54..39,418.55 rows=412 width=16) (actual time=22.900..258.767 rows=3,004 loops=1)

45. 230.902 244.229 ↓ 1.9 296 1

Bitmap Heap Scan on child_sla et_1 (cost=1,588.11..37,901.34 rows=159 width=8) (actual time=22.831..244.229 rows=296 loops=1)

  • Filter: ((NOT deleted) AND ((change_tz(reporting_date, 'UTC'::character varying, 'Asia/Kolkata'::character varying))::date >= '2020-07-24 00:00:00'::timestamp without time zone) AND ((change_tz(reporting_date, 'UTC'::character varying, 'Asia/Kolkata'::character varying))::date <= '2020-09-15 00:00:00'::timestamp without time zone))
  • Rows Removed by Filter: 31,403
  • Heap Blocks: exact=25,491
46. 13.327 13.327 ↓ 6.2 195,857 1

Bitmap Index Scan on idx_child_sla_deleted_client_id_slaid (cost=0.00..1,588.08 rows=31,714 width=0) (actual time=13.327..13.327 rows=195,857 loops=1)

  • Index Cond: (deleted = false)
47. 7.808 13.320 ↓ 3.3 10 296

Index Scan using idx_du_entity_id on domain_user du_3 (cost=0.42..9.51 rows=3 width=16) (actual time=0.035..0.045 rows=10 loops=296)

  • Index Cond: (entity_id = et_1.relation_id)
  • Filter: ((user_id = 1,044) OR (SubPlan 3))
  • Rows Removed by Filter: 19
48.          

SubPlan (for Index Scan)

49. 0.000 5.512 ↓ 0.0 0 5,512

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

50. 5.512 5.512 ↓ 0.0 0 5,512

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=5,512)

  • Filter: (id = du_3.user_group_id)
  • Rows Removed by Filter: 8
51. 0.070 0.094 ↓ 297.0 297 1

Sort (cost=4.21..4.22 rows=1 width=16) (actual time=0.030..0.094 rows=297 loops=1)

  • Sort Key: crg_1.flowdown_rolegroup
  • Sort Method: quicksort Memory: 25kB
52. 0.006 0.024 ↓ 2.0 2 1

Bitmap Heap Scan on role_group crg_1 (cost=3.09..4.20 rows=1 width=16) (actual time=0.022..0.024 rows=2 loops=1)

  • Recheck Cond: ((entity_type_id = 15) AND (client_id = 1,002))
  • Heap Blocks: exact=2
53. 0.002 0.018 ↓ 0.0 0 1

BitmapAnd (cost=3.09..3.09 rows=1 width=0) (actual time=0.017..0.018 rows=0 loops=1)

54. 0.008 0.008 ↑ 1.0 9 1

Bitmap Index Scan on role_group_entity_type_id_idx (cost=0.00..1.31 rows=9 width=0) (actual time=0.007..0.008 rows=9 loops=1)

  • Index Cond: (entity_type_id = 15)
55. 0.008 0.008 ↓ 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.008..0.008 rows=66 loops=1)

  • Index Cond: (client_id = 1,002)
56. 2.368 2.368 ↑ 1.0 1 296

Index Scan using pk_app_user on app_user u_2 (cost=0.28..0.32 rows=1 width=17) (actual time=0.008..0.008 rows=1 loops=296)

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

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

  • Index Cond: (du_3.user_group_id = id)
58. 0.303 601.842 ↓ 302.0 302 1

Sort (cost=31.72..31.73 rows=1 width=169) (actual time=601.777..601.842 rows=302 loops=1)

  • Sort Key: csla.id, r.name, ets.description, c.name, c.document_title
  • Sort Method: quicksort Memory: 115kB
59. 0.170 601.539 ↓ 302.0 302 1

Nested Loop (cost=4.71..31.71 rows=1 width=169) (actual time=589.044..601.539 rows=302 loops=1)

60. 0.168 600.765 ↓ 302.0 302 1

Nested Loop Left Join (cost=4.43..30.74 rows=1 width=151) (actual time=589.033..600.765 rows=302 loops=1)

61. 0.416 599.413 ↓ 296.0 296 1

Nested Loop (cost=4.01..29.59 rows=1 width=155) (actual time=589.017..599.413 rows=296 loops=1)

  • Join Filter: (((du.entity_id = csla.id) AND (du.role_group_id = 2,003) AND (sla.status_id = ANY ('{1511,5,1502,1,1501,1508,1499,1497,1512,1504,6,1500,1510,1509,1496,2,1513,1514,1498,1506,1503,1507,1505,1618,1616,1614,1612,1615,1611,1613,1617}'::integer[]))) OR ((du.entity_id = sla.id) AND (du.role_group_id = 2,002)))
62. 0.224 598.109 ↓ 296.0 296 1

Nested Loop Left Join (cost=3.72..27.56 rows=1 width=163) (actual time=588.999..598.109 rows=296 loops=1)

63. 0.311 597.293 ↓ 296.0 296 1

Nested Loop Left Join (cost=3.43..25.21 rows=1 width=117) (actual time=588.988..597.293 rows=296 loops=1)

64. 0.523 596.390 ↓ 296.0 296 1

Nested Loop (cost=3.15..23.58 rows=1 width=70) (actual time=588.976..596.390 rows=296 loops=1)

65. 589.651 589.651 ↓ 296.0 296 1

CTE Scan on domain_user_with_flowdown_rgs du (cost=0.00..3.85 rows=1 width=12) (actual time=588.919..589.651 rows=296 loops=1)

  • Filter: (((entity_type_id = 15) OR (entity_type_id = 14)) AND ((role_group_id = 2,003) OR (role_group_id = 2,002)) AND ((user_id = 1,044) OR (SubPlan 6)))
  • Rows Removed by Filter: 296
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. 4.736 6.216 ↑ 1.0 1 296

Bitmap Heap Scan on child_sla csla (cost=3.15..19.72 rows=1 width=62) (actual time=0.021..0.021 rows=1 loops=296)

  • Recheck Cond: ((du.entity_id = id) OR ((du.entity_id = slaid) AND (client_id = 1,002) AND (NOT deleted)))
  • Filter: ((NOT deleted) AND (client_id = 1,002) AND (((du.entity_type_id = 15) AND (du.entity_id = id)) OR ((du.entity_type_id = 14) AND (du.entity_id = slaid))) AND (status_id = ANY ('{1628,1626,1621,1620,1622,1627,1623,1624,1625,1619,1568,1,1566,2,4,1567,1563,1564,1565,1562,21}'::integer[])) AND ((change_tz(reporting_date, 'UTC'::character varying, 'Asia/Kolkata'::character varying))::date >= '2020-07-24 00:00:00'::timestamp without time zone) AND ((change_tz(reporting_date, 'UTC'::character varying, 'Asia/Kolkata'::character varying))::date <= '2020-09-15 00:00:00'::timestamp without time zone))
  • Heap Blocks: exact=361
70. 0.296 1.480 ↓ 0.0 0 296

BitmapOr (cost=3.15..3.15 rows=10 width=0) (actual time=0.005..0.005 rows=0 loops=296)

71. 0.592 0.592 ↑ 1.0 1 296

Bitmap Index Scan on pk_child_sla (cost=0.00..1.52 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=296)

  • Index Cond: (du.entity_id = id)
72. 0.592 0.592 ↓ 0.0 0 296

Bitmap Index Scan on idx_child_sla_deleted_client_id_slaid (cost=0.00..1.63 rows=9 width=0) (actual time=0.002..0.002 rows=0 loops=296)

  • Index Cond: ((du.entity_id = slaid) AND (client_id = 1,002) AND (deleted = false))
73. 0.592 0.592 ↑ 1.0 1 296

Index Scan using pk_relation on relation r (cost=0.28..1.62 rows=1 width=55) (actual time=0.002..0.002 rows=1 loops=296)

  • Index Cond: (id = csla.relation_id)
74. 0.592 0.592 ↑ 1.0 1 296

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

  • Index Cond: (id = csla.contract_id)
75. 0.888 0.888 ↑ 1.0 1 296

Index Scan using pk_sla on sla (cost=0.29..1.98 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=296)

  • Index Cond: (id = csla.slaid)
  • Filter: (client_id = 1,002)
76. 1.184 1.184 ↑ 1.0 1 296

Index Only Scan using idx_link_entity_function_6 on link_entity_function lef (cost=0.42..1.13 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=296)

  • Index Cond: ((entity_id = sla.id) AND (entity_type_id = 14))
  • Heap Fetches: 320
77. 0.604 0.604 ↑ 1.0 1 302

Index Scan using pk_work_flow_status on work_flow_status ets (cost=0.28..0.97 rows=1 width=22) (actual time=0.002..0.002 rows=1 loops=302)

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

SubPlan (for Group)

79. 0.592 14.208 ↑ 1.0 1 296

Aggregate (cost=2.91..2.92 rows=1 width=32) (actual time=0.048..0.048 rows=1 loops=296)

80. 0.888 13.616 ↓ 2.0 2 296

Nested Loop (cost=0.14..2.90 rows=1 width=1,310) (actual time=0.016..0.046 rows=2 loops=296)

81. 12.136 12.136 ↓ 2.0 2 296

CTE Scan on domain_user_with_flowdown_rgs du_4 (cost=0.00..0.09 rows=1 width=1,314) (actual time=0.014..0.041 rows=2 loops=296)

  • Filter: (entity_id = csla.id)
  • Rows Removed by Filter: 590
82. 0.592 0.592 ↑ 1.0 1 592

Index Scan using pk_role_group on role_group rg_1 (cost=0.14..2.37 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=592)

  • Index Cond: (id = du_4.role_group_id)
  • Filter: (entity_type_id = 15)
Planning time : 7.240 ms
Execution time : 619.758 ms