explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uGuf

Settings
# exclusive inclusive rows x rows loops node
1. 5.501 2,231.277 ↓ 296.0 296 1

Group (cost=15,973.07..15,986.02 rows=1 width=293) (actual time=1,258.291..2,231.277 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. 62.502 797.313 ↓ 150.0 49,811 1

HashAggregate (cost=15,621.92..15,625.24 rows=332 width=1,330) (actual time=768.254..797.313 rows=49,811 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. 14.384 734.811 ↓ 150.0 49,811 1

Append (cost=75.53..15,614.45 rows=332 width=1,330) (actual time=2.762..734.811 rows=49,811 loops=1)

5. 51.436 376.934 ↓ 76.6 25,052 1

Nested Loop Left Join (cost=75.53..13,319.40 rows=327 width=311) (actual time=2.761..376.934 rows=25,052 loops=1)

  • Join Filter: (du_1.user_group_id = ug_3.id)
  • Rows Removed by Join Filter: 200,416
6. 23.674 300.446 ↓ 76.6 25,052 1

Nested Loop Left Join (cost=75.53..13,279.06 rows=327 width=37) (actual time=2.750..300.446 rows=25,052 loops=1)

7. 28.456 101.408 ↓ 76.6 25,052 1

Nested Loop (cost=75.26..13,172.66 rows=327 width=24) (actual time=2.738..101.408 rows=25,052 loops=1)

8. 7.802 22.848 ↓ 11.6 25,052 1

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

9. 0.136 0.136 ↓ 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.019..0.136 rows=2 loops=1)

  • Filter: ((entity_type_id = 15) AND (client_id = 1,002))
  • Rows Removed by Filter: 226
10. 12.632 14.910 ↓ 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.356..7.455 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.360 1.360 ↓ 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.680..0.680 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. 50.104 50.104 ↑ 1.0 1 25,052

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

  • Index Cond: (id = du_1.entity_id)
  • Filter: (NOT deleted)
16. 175.364 175.364 ↑ 1.0 1 25,052

Index Scan using pk_app_user on app_user u (cost=0.28..0.33 rows=1 width=17) (actual time=0.007..0.007 rows=1 loops=25,052)

  • Index Cond: (du_1.user_id = id)
17. 25.049 25.052 ↑ 1.0 8 25,052

Materialize (cost=0.00..1.12 rows=8 width=278) (actual time=0.000..0.001 rows=8 loops=25,052)

18. 0.003 0.003 ↑ 1.0 8 1

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

19. 0.000 0.074 ↓ 0.0 0 1

Nested Loop Left Join (cost=5.47..1,131.57 rows=3 width=311) (actual time=0.074..0.074 rows=0 loops=1)

  • Join Filter: (du_2.user_group_id = ug_4.id)
20. 0.001 0.074 ↓ 0.0 0 1

Nested Loop Left Join (cost=5.47..1,130.11 rows=3 width=37) (actual time=0.074..0.074 rows=0 loops=1)

21. 0.000 0.073 ↓ 0.0 0 1

Nested Loop (cost=5.20..1,129.15 rows=3 width=24) (actual time=0.073..0.073 rows=0 loops=1)

22. 0.004 0.073 ↓ 0.0 0 1

Merge Join (cost=4.78..872.97 rows=20 width=24) (actual time=0.072..0.073 rows=0 loops=1)

  • Merge Cond: (prg.id = crg.flowdown_rolegroup)
23. 0.004 0.032 ↑ 40,350.0 1 1

Nested Loop (cost=0.57..118,633.44 rows=40,350 width=20) (actual time=0.032..0.032 rows=1 loops=1)

24. 0.013 0.013 ↑ 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.013..0.013 rows=1 loops=1)

  • Filter: (entity_type_id = 61)
  • Rows Removed by Filter: 1
25. 0.015 0.015 ↑ 4,256.0 1 1

Index Scan using idx_domain_user_3 on domain_user du_2 (cost=0.42..7,370.98 rows=4,256 width=16) (actual time=0.015..0.015 rows=1 loops=1)

  • Index Cond: (role_group_id = prg.id)
  • Filter: ((user_id = 1,044) OR (SubPlan 2))
26.          

SubPlan (for Index Scan)

27. 0.000 0.000 ↓ 0.0 0

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

28. 0.000 0.000 ↓ 0.0 0

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

  • Filter: (id = du_2.user_group_id)
29. 0.002 0.037 ↓ 2.0 2 1

Materialize (cost=4.21..4.22 rows=1 width=16) (actual time=0.036..0.037 rows=2 loops=1)

30. 0.010 0.035 ↓ 2.0 2 1

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

  • Sort Key: crg.flowdown_rolegroup
  • Sort Method: quicksort Memory: 25kB
31. 0.006 0.025 ↓ 2.0 2 1

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

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

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

33. 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)
34. 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)
35. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_child_sla_4 on child_sla et (cost=0.41..8.94 rows=387 width=8) (never executed)

  • Index Cond: (contract_id = du_2.entity_id)
  • Filter: (NOT deleted)
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

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

38. 0.000 0.000 ↓ 0.0 0

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

39. 51.593 343.419 ↓ 12,379.5 24,759 1

Nested Loop Left Join (cost=5.47..1,158.51 rows=2 width=311) (actual time=0.145..343.419 rows=24,759 loops=1)

  • Join Filter: (du_3.user_group_id = ug_5.id)
  • Rows Removed by Join Filter: 198,072
40. 27.711 267.067 ↓ 12,379.5 24,759 1

Nested Loop Left Join (cost=5.47..1,157.17 rows=2 width=37) (actual time=0.132..267.067 rows=24,759 loops=1)

41. 8.930 66.043 ↓ 12,379.5 24,759 1

Nested Loop (cost=5.20..1,156.53 rows=2 width=24) (actual time=0.118..66.043 rows=24,759 loops=1)

42. 1.974 10.253 ↓ 260.3 3,905 1

Merge Join (cost=4.78..675.61 rows=15 width=24) (actual time=0.046..10.253 rows=3,905 loops=1)

  • Merge Cond: (prg_1.id = crg_1.flowdown_rolegroup)
43. 1.178 7.653 ↑ 7.7 3,906 1

Nested Loop (cost=0.57..91,772.60 rows=30,263 width=20) (actual time=0.021..7.653 rows=3,906 loops=1)

44. 0.105 0.105 ↓ 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.005..0.105 rows=13 loops=1)

  • Filter: (entity_type_id = 1)
  • Rows Removed by Filter: 205
45. 4.747 6.370 ↑ 14.2 300 13

Index Scan using idx_domain_user_3 on domain_user du_3 (cost=0.42..7,603.75 rows=4,256 width=16) (actual time=0.058..0.490 rows=300 loops=13)

  • Index Cond: (role_group_id = prg_1.id)
  • Filter: ((user_id = 1,044) OR (SubPlan 3))
  • Rows Removed by Filter: 125
46.          

SubPlan (for Index Scan)

47. 0.000 1.623 ↓ 0.0 0 1,623

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

48. 1.623 1.623 ↓ 0.0 0 1,623

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=1,623)

  • Filter: (id = du_3.user_group_id)
  • Rows Removed by Filter: 8
49. 0.606 0.626 ↓ 3,906.0 3,906 1

Materialize (cost=4.21..4.22 rows=1 width=16) (actual time=0.021..0.626 rows=3,906 loops=1)

50. 0.004 0.020 ↓ 2.0 2 1

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

  • Sort Key: crg_1.flowdown_rolegroup
  • Sort Method: quicksort Memory: 25kB
51. 0.004 0.016 ↓ 2.0 2 1

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

  • Recheck Cond: ((entity_type_id = 15) AND (client_id = 1,002))
  • Heap Blocks: exact=2
52. 0.001 0.012 ↓ 0.0 0 1

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

53. 0.004 0.004 ↑ 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.004..0.004 rows=9 loops=1)

  • Index Cond: (entity_type_id = 15)
54. 0.007 0.007 ↓ 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.007..0.007 rows=66 loops=1)

  • Index Cond: (client_id = 1,002)
55. 46.860 46.860 ↑ 165.2 6 3,905

Index Scan using idx_child_sla_3 on child_sla et_1 (cost=0.41..22.15 rows=991 width=8) (actual time=0.001..0.012 rows=6 loops=3,905)

  • Index Cond: (relation_id = du_3.entity_id)
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 1
56. 173.313 173.313 ↑ 1.0 1 24,759

Index Scan using pk_app_user on app_user u_2 (cost=0.28..0.32 rows=1 width=17) (actual time=0.007..0.007 rows=1 loops=24,759)

  • Index Cond: (du_3.user_id = id)
57. 24.755 24.759 ↑ 1.0 8 24,759

Materialize (cost=0.00..1.12 rows=8 width=278) (actual time=0.000..0.001 rows=8 loops=24,759)

58. 0.004 0.004 ↑ 1.0 8 1

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

59. 0.490 1,254.896 ↓ 302.0 302 1

Sort (cost=347.83..347.84 rows=1 width=169) (actual time=1,254.765..1,254.896 rows=302 loops=1)

  • Sort Key: csla.id, r.name, ets.description, c.name, c.document_title
  • Sort Method: quicksort Memory: 115kB
60. 0.446 1,254.406 ↓ 302.0 302 1

Nested Loop (cost=4.71..347.82 rows=1 width=169) (actual time=770.712..1,254.406 rows=302 loops=1)

61. 0.532 1,253.356 ↓ 302.0 302 1

Nested Loop Left Join (cost=4.43..346.85 rows=1 width=151) (actual time=770.701..1,253.356 rows=302 loops=1)

62. 0.414 1,251.344 ↓ 296.0 296 1

Nested Loop (cost=4.01..345.70 rows=1 width=155) (actual time=770.684..1,251.344 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)))
63. 0.252 1,249.450 ↓ 296.0 296 1

Nested Loop Left Join (cost=3.72..343.67 rows=1 width=163) (actual time=770.665..1,249.450 rows=296 loops=1)

64. 0.356 1,248.310 ↓ 296.0 296 1

Nested Loop Left Join (cost=3.43..341.32 rows=1 width=117) (actual time=770.653..1,248.310 rows=296 loops=1)

65. 14.067 1,247.066 ↓ 296.0 296 1

Nested Loop (cost=3.15..339.69 rows=1 width=70) (actual time=770.640..1,247.066 rows=296 loops=1)

66. 832.167 832.167 ↓ 25,052.0 25,052 1

CTE Scan on domain_user_with_flowdown_rgs du (cost=0.00..319.96 rows=1 width=12) (actual time=768.260..832.167 rows=25,052 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: 24,759
67.          

SubPlan (for CTE Scan)

68. 0.000 0.000 ↓ 0.0 0

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

69. 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)
70. 275.572 400.832 ↓ 0.0 0 25,052

Bitmap Heap Scan on child_sla csla (cost=3.15..19.72 rows=1 width=62) (actual time=0.016..0.016 rows=0 loops=25,052)

  • 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))
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=47,812
71. 25.052 125.260 ↓ 0.0 0 25,052

BitmapOr (cost=3.15..3.15 rows=10 width=0) (actual time=0.005..0.005 rows=0 loops=25,052)

72. 50.104 50.104 ↓ 2.0 2 25,052

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

  • Index Cond: (du.entity_id = id)
73. 50.104 50.104 ↓ 0.0 0 25,052

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=25,052)

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

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

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

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

  • Index Cond: (id = csla.contract_id)
76. 1.480 1.480 ↑ 1.0 1 296

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

  • Index Cond: (id = csla.slaid)
  • Filter: (client_id = 1,002)
77. 1.480 1.480 ↑ 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.005..0.005 rows=1 loops=296)

  • Index Cond: ((entity_id = sla.id) AND (entity_type_id = 14))
  • Heap Fetches: 320
78. 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)
79.          

SubPlan (for Group)

80. 0.888 970.880 ↑ 1.0 1 296

Aggregate (cost=12.66..12.67 rows=1 width=32) (actual time=3.280..3.280 rows=1 loops=296)

81. 1.184 969.992 ↓ 2.0 2 296

Nested Loop (cost=0.14..12.64 rows=1 width=1,310) (actual time=1.083..3.277 rows=2 loops=296)

82. 967.032 967.032 ↑ 1.0 2 296

CTE Scan on domain_user_with_flowdown_rgs du_4 (cost=0.00..7.47 rows=2 width=1,314) (actual time=1.078..3.267 rows=2 loops=296)

  • Filter: (entity_id = csla.id)
  • Rows Removed by Filter: 49,809
83. 1.776 1.776 ↑ 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.003..0.003 rows=1 loops=592)

  • Index Cond: (id = du_4.role_group_id)
  • Filter: (entity_type_id = 15)
Planning time : 6.677 ms
Execution time : 2,232.983 ms