explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1w14

Settings
# exclusive inclusive rows x rows loops node
1. 3.035 772.300 ↓ 290.0 290 1

Group (cost=32,755.63..32,758.85 rows=1 width=293) (actual time=756.552..772.300 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. 1.029 749.021 ↓ 116.0 580 1

HashAggregate (cost=32,731.42..32,731.47 rows=5 width=1,330) (actual time=748.753..749.021 rows=580 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.255 747.992 ↓ 116.0 580 1

Append (cost=75.81..32,731.31 rows=5 width=1,330) (actual time=243.540..747.992 rows=580 loops=1)

5. 0.403 349.864 ↓ 96.7 290 1

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

6. 0.253 349.461 ↓ 96.7 290 1

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

7. 17.271 293.818 ↓ 96.7 290 1

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

8. 9.770 26.807 ↓ 6.3 24,974 1

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

9. 0.143 0.143 ↑ 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.143 rows=2 loops=1)

  • Filter: ((entity_type_id = 15) AND (client_id = 1,002))
  • Rows Removed by Filter: 226
10. 13.580 16.894 ↓ 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.487..8.447 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.478 1.478 ↓ 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.739..0.739 rows=15,980 loops=2)

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

SubPlan (for Bitmap Heap Scan)

13. 0.918 1.836 ↓ 0.0 0 918

ProjectSet (cost=0.00..1.61 rows=100 width=4) (actual time=0.002..0.002 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.390 55.390 ↑ 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.191..0.191 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.001 31.979 ↓ 0.0 0 1

Nested Loop Left Join (cost=1,845.15..3,111.95 rows=1 width=311) (actual time=31.979..31.979 rows=0 loops=1)

19. 0.001 31.978 ↓ 0.0 0 1

Nested Loop Left Join (cost=1,845.02..3,111.79 rows=1 width=37) (actual time=31.978..31.978 rows=0 loops=1)

20. 0.003 31.977 ↓ 0.0 0 1

Merge Join (cost=1,844.74..3,111.46 rows=1 width=24) (actual time=31.977..31.977 rows=0 loops=1)

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

Nested Loop (cost=1,839.47..41,107.15 rows=31 width=20) (actual time=31.931..31.932 rows=1 loops=1)

  • Join Filter: (du_2.role_group_id = prg.id)
22. 0.012 0.012 ↑ 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.012..0.012 rows=1 loops=1)

  • Filter: (entity_type_id = 61)
  • Rows Removed by Filter: 1
23. 0.007 31.917 ↑ 409.0 1 1

Materialize (cost=1,839.33..40,979.46 rows=409 width=16) (actual time=31.917..31.917 rows=1 loops=1)

24. 0.005 31.910 ↑ 409.0 1 1

Nested Loop (cost=1,839.33..40,977.42 rows=409 width=16) (actual time=31.910..31.910 rows=1 loops=1)

25. 12.981 31.865 ↑ 158.0 1 1

Bitmap Heap Scan on child_sla et (cost=1,838.90..39,468.82 rows=158 width=8) (actual time=31.865..31.865 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,054
  • Heap Blocks: exact=253
26. 18.884 18.884 ↓ 7.6 241,363 1

Bitmap Index Scan on idx_child_sla_deleted_client_id_slaid (cost=0.00..1,838.86 rows=31,657 width=0) (actual time=18.884..18.884 rows=241,363 loops=1)

  • Index Cond: (deleted = false)
27. 0.025 0.040 ↑ 3.0 1 1

Index Scan using idx_du_entity_id on domain_user du_2 (cost=0.42..9.52 rows=3 width=16) (actual time=0.040..0.040 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.010 0.042 ↑ 1.0 2 1

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

  • Sort Key: crg.flowdown_rolegroup
  • Sort Method: quicksort Memory: 25kB
32. 0.008 0.032 ↑ 1.0 2 1

Bitmap Heap Scan on role_group crg (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
33. 0.003 0.024 ↓ 0.0 0 1

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

34. 0.012 0.012 ↑ 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.012..0.012 rows=9 loops=1)

  • Index Cond: (entity_type_id = 15)
35. 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)
36. 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)
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.424 365.894 ↓ 290.0 290 1

Nested Loop Left Join (cost=1,845.15..3,480.38 rows=1 width=311) (actual time=30.415..365.894 rows=290 loops=1)

39. 0.386 365.470 ↓ 290.0 290 1

Nested Loop Left Join (cost=1,845.02..3,480.22 rows=1 width=37) (actual time=30.408..365.470 rows=290 loops=1)

40. 0.295 307.954 ↓ 290.0 290 1

Merge Join (cost=1,844.74..3,479.90 rows=1 width=24) (actual time=30.075..307.954 rows=290 loops=1)

  • Merge Cond: (prg_1.id = crg_1.flowdown_rolegroup)
41. 6.445 307.531 ↓ 12.1 290 1

Nested Loop (cost=1,839.47..41,082.61 rows=24 width=20) (actual time=30.033..307.531 rows=290 loops=1)

  • Join Filter: (du_3.role_group_id = prg_1.id)
  • Rows Removed by Join Filter: 37,514
42. 0.123 0.123 ↑ 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.008..0.123 rows=13 loops=1)

  • Filter: (entity_type_id = 1)
  • Rows Removed by Filter: 215
43. 6.005 300.963 ↓ 7.1 2,908 13

Materialize (cost=1,839.33..40,979.46 rows=409 width=16) (actual time=2.310..23.151 rows=2,908 loops=13)

44. 1.260 294.958 ↓ 7.1 2,908 1

Nested Loop (cost=1,839.33..40,977.42 rows=409 width=16) (actual time=30.019..294.958 rows=2,908 loops=1)

45. 259.861 278.618 ↓ 1.8 290 1

Bitmap Heap Scan on child_sla et_1 (cost=1,838.90..39,468.82 rows=158 width=8) (actual time=29.948..278.618 rows=290 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,331
  • Heap Blocks: exact=30,275
46. 18.757 18.757 ↓ 7.6 241,363 1

Bitmap Index Scan on idx_child_sla_deleted_client_id_slaid (cost=0.00..1,838.86 rows=31,657 width=0) (actual time=18.756..18.757 rows=241,363 loops=1)

  • Index Cond: (deleted = false)
47. 9.598 15.080 ↓ 3.3 10 290

Index Scan using idx_du_entity_id on domain_user du_3 (cost=0.42..9.52 rows=3 width=16) (actual time=0.039..0.052 rows=10 loops=290)

  • 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.482 ↓ 0.0 0 5,482

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

50. 5.482 5.482 ↓ 0.0 0 5,482

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

  • Filter: (id = du_3.user_group_id)
  • Rows Removed by Filter: 8
51. 0.099 0.128 ↓ 145.5 291 1

Sort (cost=5.27..5.28 rows=2 width=16) (actual time=0.035..0.128 rows=291 loops=1)

  • Sort Key: crg_1.flowdown_rolegroup
  • Sort Method: quicksort Memory: 25kB
52. 0.011 0.029 ↑ 1.0 2 1

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

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

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

54. 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)
55. 0.008 0.008 ↓ 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.008..0.008 rows=74 loops=1)

  • Index Cond: (client_id = 1,002)
56. 57.130 57.130 ↑ 1.0 1 290

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

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

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

  • Index Cond: (du_3.user_group_id = id)
58. 0.266 756.505 ↓ 290.0 290 1

Sort (cost=24.16..24.16 rows=1 width=169) (actual time=756.446..756.505 rows=290 loops=1)

  • Sort Key: csla.id, r.name, ets.description, c.name, c.document_title
  • Sort Method: quicksort Memory: 110kB
59. 0.290 756.239 ↓ 290.0 290 1

Nested Loop (cost=4.71..24.15 rows=1 width=169) (actual time=748.879..756.239 rows=290 loops=1)

60. 0.249 755.659 ↓ 290.0 290 1

Nested Loop Left Join (cost=4.43..23.85 rows=1 width=151) (actual time=748.869..755.659 rows=290 loops=1)

61. 0.353 754.540 ↓ 290.0 290 1

Nested Loop Left Join (cost=4.01..22.70 rows=1 width=155) (actual time=748.853..754.540 rows=290 loops=1)

62. 0.309 753.897 ↓ 290.0 290 1

Nested Loop Left Join (cost=3.72..22.12 rows=1 width=109) (actual time=748.841..753.897 rows=290 loops=1)

63. 0.201 753.298 ↓ 290.0 290 1

Nested Loop (cost=3.44..21.80 rows=1 width=62) (actual time=748.823..753.298 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)))
64. 0.520 752.227 ↓ 58.0 290 1

Nested Loop (cost=3.15..19.45 rows=5 width=70) (actual time=748.806..752.227 rows=290 loops=1)

65. 749.387 749.387 ↓ 290.0 290 1

CTE Scan on domain_user_with_flowdown_rgs du (cost=0.00..4.82 rows=1 width=12) (actual time=748.760..749.387 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: 290
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. 1.160 2.320 ↑ 1.0 1 290

Bitmap Heap Scan on child_sla csla (cost=3.15..14.62 rows=1 width=62) (actual time=0.008..0.008 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[])))
  • Heap Blocks: exact=612
70. 0.290 1.160 ↓ 0.0 0 290

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

71. 0.580 0.580 ↓ 2.0 2 290

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

  • Index Cond: (du.entity_id = id)
72. 0.290 0.290 ↓ 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.001..0.001 rows=0 loops=290)

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

Index Scan using pk_sla on sla (cost=0.29..0.41 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. 0.290 0.290 ↑ 1.0 1 290

Index Scan using pk_relation on relation r (cost=0.28..0.32 rows=1 width=55) (actual time=0.001..0.001 rows=1 loops=290)

  • Index Cond: (id = csla.relation_id)
75. 0.290 0.290 ↑ 1.0 1 290

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

  • Index Cond: (id = csla.contract_id)
76. 0.870 0.870 ↑ 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.003 rows=1 loops=290)

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

Index Scan using pk_work_flow_status on work_flow_status ets (cost=0.28..0.30 rows=1 width=22) (actual time=0.001..0.001 rows=1 loops=290)

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

SubPlan (for Group)

79. 0.580 12.760 ↑ 1.0 1 290

Aggregate (cost=2.93..2.94 rows=1 width=32) (actual time=0.044..0.044 rows=1 loops=290)

80. 0.870 12.180 ↓ 2.0 2 290

Nested Loop (cost=0.14..2.92 rows=1 width=1,310) (actual time=0.015..0.042 rows=2 loops=290)

81. 10.730 10.730 ↓ 2.0 2 290

CTE Scan on domain_user_with_flowdown_rgs du_4 (cost=0.00..0.11 rows=1 width=1,314) (actual time=0.013..0.037 rows=2 loops=290)

  • Filter: (entity_id = csla.id)
  • Rows Removed by Filter: 578
82. 0.580 0.580 ↑ 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.001..0.001 rows=1 loops=580)

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