explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6Pe1

Settings
# exclusive inclusive rows x rows loops node
1. 4.715 5,655.165 ↓ 290.0 290 1

Group (cost=29,398.53..29,401.96 rows=1 width=293) (actual time=5,186.594..5,655.165 rows=290 loops=1)

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

CTE domain_user_with_flowdown_rgs

3. 40.125 5,159.319 ↓ 1,783.6 24,971 1

HashAggregate (cost=29,357.01..29,357.15 rows=14 width=1,330) (actual time=5,148.796..5,159.319 rows=24,971 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. 10.065 5,119.194 ↓ 1,783.6 24,971 1

Append (cost=75.81..29,356.70 rows=14 width=1,330) (actual time=241.123..5,119.194 rows=24,971 loops=1)

5. 0.435 351.303 ↓ 96.7 290 1

Nested Loop Left Join (cost=75.81..26,138.91 rows=3 width=311) (actual time=241.121..351.303 rows=290 loops=1)

6. 0.352 350.868 ↓ 96.7 290 1

Nested Loop Left Join (cost=75.68..26,138.46 rows=3 width=37) (actual time=241.106..350.868 rows=290 loops=1)

7. 19.215 294.546 ↓ 96.7 290 1

Nested Loop (cost=75.40..26,137.46 rows=3 width=24) (actual time=240.669..294.546 rows=290 loops=1)

8. 9.088 25.591 ↓ 6.3 24,974 1

Nested Loop (cost=74.98..22,126.72 rows=3,948 width=24) (actual time=2.909..25.591 rows=24,974 loops=1)

9. 0.209 0.209 ↑ 1.0 2 1

Index Scan using pk_role_group on role_group rg (cost=0.14..18.88 rows=2 width=12) (actual time=0.023..0.209 rows=2 loops=1)

  • Filter: ((entity_type_id = 15) AND (client_id = 1,002))
  • Rows Removed by Filter: 226
10. 13.932 16.294 ↓ 3.4 12,487 2

Bitmap Heap Scan on domain_user du_1 (cost=74.84..11,017.22 rows=3,670 width=16) (actual time=1.445..8.147 rows=12,487 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,404
  • Heap Blocks: exact=1,174
11. 1.444 1.444 ↓ 2.3 15,980 2

Bitmap Index Scan on idx_domain_user_3 (cost=0.00..73.92 rows=7,013 width=0) (actual time=0.721..0.722 rows=15,980 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. 249.740 249.740 ↓ 0.0 0 24,974

Index Scan using pk_child_sla on child_sla csla_1 (cost=0.41..1.02 rows=1 width=4) (actual time=0.010..0.010 rows=0 loops=24,974)

  • 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. 55.970 55.970 ↑ 1.0 1 290

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

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

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

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

Nested Loop Left Join (cost=6.53..1,462.80 rows=6 width=311) (actual time=0.091..0.091 rows=0 loops=1)

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

Nested Loop Left Join (cost=6.53..1,460.98 rows=6 width=37) (actual time=0.090..0.091 rows=0 loops=1)

20. 0.001 0.090 ↓ 0.0 0 1

Nested Loop (cost=6.26..1,459.02 rows=6 width=24) (actual time=0.089..0.090 rows=0 loops=1)

21. 0.003 0.089 ↓ 0.0 0 1

Merge Join (cost=5.84..945.98 rows=40 width=24) (actual time=0.089..0.089 rows=0 loops=1)

  • Merge Cond: (prg.id = crg.flowdown_rolegroup)
22. 0.004 0.052 ↑ 41,312.0 1 1

Nested Loop (cost=0.57..132,421.68 rows=41,312 width=20) (actual time=0.051..0.052 rows=1 loops=1)

23. 0.014 0.014 ↑ 18.0 1 1

Index Scan using pk_role_group on role_group prg (cost=0.14..18.28 rows=18 width=4) (actual time=0.014..0.014 rows=1 loops=1)

  • Filter: (entity_type_id = 61)
  • Rows Removed by Filter: 1
24. 0.026 0.034 ↑ 4,267.0 1 1

Index Scan using idx_domain_user_3 on domain_user du_2 (cost=0.42..7,313.07 rows=4,267 width=16) (actual time=0.033..0.034 rows=1 loops=1)

  • Index Cond: (role_group_id = prg.id)
  • Filter: ((user_id = 1,044) OR (SubPlan 2))
  • Rows Removed by Filter: 1
25.          

SubPlan (for Index Scan)

26. 0.001 0.008 ↓ 0.0 0 1

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

27. 0.007 0.007 ↓ 0.0 0 1

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

  • Filter: (id = du_2.user_group_id)
  • Rows Removed by Filter: 8
28. 0.008 0.034 ↑ 1.0 2 1

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

  • Sort Key: crg.flowdown_rolegroup
  • Sort Method: quicksort Memory: 25kB
29. 0.007 0.026 ↑ 1.0 2 1

Bitmap Heap Scan on role_group crg (cost=3.12..5.26 rows=2 width=16) (actual time=0.024..0.026 rows=2 loops=1)

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

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

31. 0.007 0.007 ↑ 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.007 rows=9 loops=1)

  • Index Cond: (entity_type_id = 15)
32. 0.009 0.009 ↓ 1.8 74 1

Bitmap Index Scan on idx_role_grp_client_id (cost=0.00..1.55 rows=41 width=0) (actual time=0.009..0.009 rows=74 loops=1)

  • Index Cond: (client_id = 1,002)
33. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (contract_id = du_2.entity_id)
  • Filter: (NOT deleted)
34. 0.000 0.000 ↓ 0.0 0

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

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

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

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

37. 39.717 4,757.735 ↓ 4,936.2 24,681 1

Nested Loop Left Join (cost=6.53..1,754.77 rows=5 width=311) (actual time=4.332..4,757.735 rows=24,681 loops=1)

  • Join Filter: (du_3.user_group_id = ug_5.id)
  • Rows Removed by Join Filter: 197,448
38. 19.162 4,668.656 ↓ 4,936.2 24,681 1

Nested Loop Left Join (cost=6.53..1,753.07 rows=5 width=37) (actual time=4.301..4,668.656 rows=24,681 loops=1)

39. 10.458 83.509 ↓ 4,936.2 24,681 1

Nested Loop (cost=6.26..1,751.44 rows=5 width=24) (actual time=4.069..83.509 rows=24,681 loops=1)

40. 2.016 10.379 ↓ 126.4 3,917 1

Merge Join (cost=5.84..755.14 rows=31 width=24) (actual time=0.069..10.379 rows=3,917 loops=1)

  • Merge Cond: (prg_1.id = crg_1.flowdown_rolegroup)
41. 1.315 7.696 ↑ 8.2 3,918 1

Nested Loop (cost=0.57..105,490.31 rows=32,131 width=20) (actual time=0.029..7.696 rows=3,918 loops=1)

42. 0.102 0.102 ↑ 1.1 13 1

Index Scan using pk_role_group on role_group prg_1 (cost=0.14..18.28 rows=14 width=4) (actual time=0.007..0.102 rows=13 loops=1)

  • Filter: (entity_type_id = 1)
  • Rows Removed by Filter: 205
43. 4.653 6.279 ↑ 14.2 301 13

Index Scan using idx_domain_user_3 on domain_user du_3 (cost=0.42..7,491.05 rows=4,267 width=16) (actual time=0.055..0.483 rows=301 loops=13)

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

SubPlan (for Index Scan)

45. 0.000 1.626 ↓ 0.0 0 1,626

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

46. 1.626 1.626 ↓ 0.0 0 1,626

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,626)

  • Filter: (id = du_3.user_group_id)
  • Rows Removed by Filter: 8
47. 0.635 0.667 ↓ 1,959.0 3,918 1

Sort (cost=5.27..5.28 rows=2 width=16) (actual time=0.036..0.667 rows=3,918 loops=1)

  • Sort Key: crg_1.flowdown_rolegroup
  • Sort Method: quicksort Memory: 25kB
48. 0.007 0.032 ↑ 1.0 2 1

Bitmap Heap Scan on role_group crg_1 (cost=3.12..5.26 rows=2 width=16) (actual time=0.030..0.032 rows=2 loops=1)

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

BitmapAnd (cost=3.12..3.12 rows=2 width=0) (actual time=0.025..0.025 rows=0 loops=1)

50. 0.009 0.009 ↑ 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.009..0.009 rows=9 loops=1)

  • Index Cond: (entity_type_id = 15)
51. 0.014 0.014 ↓ 1.8 74 1

Bitmap Index Scan on idx_role_grp_client_id (cost=0.00..1.55 rows=41 width=0) (actual time=0.014..0.014 rows=74 loops=1)

  • Index Cond: (client_id = 1,002)
52. 62.672 62.672 ↑ 164.8 6 3,917

Index Scan using idx_child_sla_3 on child_sla et_1 (cost=0.41..22.25 rows=989 width=8) (actual time=0.001..0.016 rows=6 loops=3,917)

  • Index Cond: (relation_id = du_3.entity_id)
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 1
53. 4,565.985 4,565.985 ↑ 1.0 1 24,681

Index Scan using pk_app_user on app_user u_2 (cost=0.28..0.33 rows=1 width=17) (actual time=0.185..0.185 rows=1 loops=24,681)

  • Index Cond: (du_3.user_id = id)
54. 49.354 49.362 ↑ 1.0 8 24,681

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

55. 0.008 0.008 ↑ 1.0 8 1

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

56. 0.351 5,185.000 ↓ 290.0 290 1

Sort (cost=41.38..41.39 rows=1 width=169) (actual time=5,184.895..5,185.000 rows=290 loops=1)

  • Sort Key: csla.id, r.name, ets.description, c.name, c.document_title
  • Sort Method: quicksort Memory: 110kB
57. 0.169 5,184.649 ↓ 290.0 290 1

Nested Loop (cost=4.71..41.37 rows=1 width=169) (actual time=5,149.052..5,184.649 rows=290 loops=1)

58. 0.256 5,183.900 ↓ 290.0 290 1

Nested Loop Left Join (cost=4.43..40.40 rows=1 width=151) (actual time=5,149.043..5,183.900 rows=290 loops=1)

59. 0.457 5,182.484 ↓ 290.0 290 1

Nested Loop (cost=4.01..39.25 rows=1 width=155) (actual time=5,149.027..5,182.484 rows=290 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)))
60. 0.236 5,181.157 ↓ 290.0 290 1

Nested Loop Left Join (cost=3.72..37.20 rows=1 width=163) (actual time=5,149.007..5,181.157 rows=290 loops=1)

61. 0.384 5,180.341 ↓ 290.0 290 1

Nested Loop Left Join (cost=3.43..34.84 rows=1 width=117) (actual time=5,148.994..5,180.341 rows=290 loops=1)

62. 0.617 5,179.377 ↓ 290.0 290 1

Nested Loop (cost=3.15..33.22 rows=1 width=70) (actual time=5,148.981..5,179.377 rows=290 loops=1)

63. 5,171.510 5,171.510 ↓ 290.0 290 1

CTE Scan on domain_user_with_flowdown_rgs du (cost=0.00..13.49 rows=1 width=12) (actual time=5,148.893..5,171.510 rows=290 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,681
64.          

SubPlan (for CTE Scan)

65. 0.000 0.000 ↓ 0.0 0

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

66. 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)
67. 5.510 7.250 ↑ 1.0 1 290

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

  • 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=612
68. 0.290 1.740 ↓ 0.0 0 290

BitmapOr (cost=3.15..3.15 rows=10 width=0) (actual time=0.006..0.006 rows=0 loops=290)

69. 0.870 0.870 ↓ 2.0 2 290

Bitmap Index Scan on pk_child_sla (cost=0.00..1.52 rows=1 width=0) (actual time=0.003..0.003 rows=2 loops=290)

  • Index Cond: (du.entity_id = id)
70. 0.580 0.580 ↓ 0.0 0 290

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

  • Index Cond: ((du.entity_id = slaid) AND (client_id = 1,002) AND (deleted = false))
71. 0.580 0.580 ↑ 1.0 1 290

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

  • Index Cond: (id = csla.relation_id)
72. 0.580 0.580 ↑ 1.0 1 290

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

  • Index Cond: (id = csla.contract_id)
73. 0.870 0.870 ↑ 1.0 1 290

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

  • Index Cond: (id = csla.slaid)
  • Filter: (client_id = 1,002)
74. 1.160 1.160 ↑ 1.0 1 290

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

  • Index Cond: ((entity_id = sla.id) AND (entity_type_id = 14))
  • Heap Fetches: 304
75. 0.580 0.580 ↑ 1.0 1 290

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

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

SubPlan (for Group)

77. 0.870 465.450 ↑ 1.0 1 290

Aggregate (cost=3.14..3.15 rows=1 width=32) (actual time=1.605..1.605 rows=1 loops=290)

78. 1.160 464.580 ↓ 2.0 2 290

Nested Loop (cost=0.14..3.12 rows=1 width=1,310) (actual time=0.536..1.602 rows=2 loops=290)

79. 462.260 462.260 ↓ 2.0 2 290

CTE Scan on domain_user_with_flowdown_rgs du_4 (cost=0.00..0.32 rows=1 width=1,314) (actual time=0.532..1.594 rows=2 loops=290)

  • Filter: (entity_id = csla.id)
  • Rows Removed by Filter: 24,969
80. 1.160 1.160 ↑ 1.0 1 580

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

  • Index Cond: (id = du_4.role_group_id)
  • Filter: (entity_type_id = 15)
Planning time : 7.222 ms
Execution time : 5,656.385 ms