explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rRgA

Settings
# exclusive inclusive rows x rows loops node
1. 3.262 3,746.411 ↓ 292.0 292 1

Group (cost=20,291.75..20,294.95 rows=1 width=293) (actual time=3,728.876..3,746.411 rows=292 loops=1)

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

CTE domain_user_with_flowdown_rgs

3. 0.620 3,709.790 ↓ 146.0 584 1

Hash Left Join (cost=20,257.98..20,266.67 rows=4 width=311) (actual time=601.750..3,709.790 rows=584 loops=1)

  • Hash Cond: (du_1.user_group_id = ug_3.id)
4. 1.716 3,709.157 ↓ 146.0 584 1

Nested Loop Left Join (cost=20,256.80..20,265.48 rows=4 width=37) (actual time=601.726..3,709.157 rows=584 loops=1)

5. 1.086 593.553 ↓ 146.0 584 1

HashAggregate (cost=20,255.41..20,255.45 rows=4 width=24) (actual time=592.928..593.553 rows=584 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
6. 0.198 592.467 ↓ 146.0 584 1

Append (cost=75.21..20,255.35 rows=4 width=24) (actual time=232.470..592.467 rows=584 loops=1)

7. 3.260 278.209 ↓ 146.0 292 1

Nested Loop (cost=75.21..14,262.73 rows=2 width=24) (actual time=232.469..278.209 rows=292 loops=1)

8. 8.831 25.109 ↓ 11.5 24,984 1

Nested Loop (cost=74.79..12,090.06 rows=2,167 width=24) (actual time=3.081..25.109 rows=24,984 loops=1)

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

  • Filter: ((entity_type_id = 15) AND (client_id = 1,002))
  • Rows Removed by Filter: 226
10. 12.836 16.144 ↓ 3.4 12,492 2

Bitmap Heap Scan on domain_user du_1 (cost=74.65..12,036.12 rows=3,657 width=16) (actual time=1.532..8.072 rows=12,492 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.472 1.472 ↓ 2.3 15,796 2

Bitmap Index Scan on idx_domain_user_3 (cost=0.00..73.73 rows=6,988 width=0) (actual time=0.736..0.736 rows=15,796 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.840 249.840 ↓ 0.0 0 24,984

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=24,984)

  • 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. 0.004 23.632 ↓ 0.0 0 1

Merge Join (cost=1,552.92..2,806.11 rows=1 width=24) (actual time=23.632..23.632 rows=0 loops=1)

  • Merge Cond: (prg.id = crg.flowdown_rolegroup)
17. 0.002 23.598 ↑ 30.0 1 1

Nested Loop (cost=1,548.71..39,143.92 rows=30 width=20) (actual time=23.598..23.598 rows=1 loops=1)

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

  • Filter: (entity_type_id = 61)
  • Rows Removed by Filter: 1
19. 0.003 23.587 ↑ 409.0 1 1

Materialize (cost=1,548.56..39,029.96 rows=409 width=16) (actual time=23.586..23.587 rows=1 loops=1)

20. 0.004 23.584 ↑ 409.0 1 1

Nested Loop (cost=1,548.56..39,027.91 rows=409 width=16) (actual time=23.584..23.584 rows=1 loops=1)

21. 9.816 23.542 ↑ 158.0 1 1

Bitmap Heap Scan on child_sla et (cost=1,548.14..37,520.40 rows=158 width=8) (actual time=23.542..23.542 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
22. 13.726 13.726 ↓ 5.9 188,142 1

Bitmap Index Scan on idx_child_sla_deleted_client_id_slaid (cost=0.00..1,548.10 rows=31,632 width=0) (actual time=13.726..13.726 rows=188,142 loops=1)

  • Index Cond: (deleted = false)
23. 0.023 0.038 ↑ 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.038..0.038 rows=1 loops=1)

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

SubPlan (for Index Scan)

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

26. 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
27. 0.007 0.030 ↓ 2.0 2 1

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

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

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

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

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

30. 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)
31. 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)
32. 0.240 290.428 ↓ 292.0 292 1

Merge Join (cost=1,552.92..3,186.45 rows=1 width=24) (actual time=23.542..290.428 rows=292 loops=1)

  • Merge Cond: (prg_1.id = crg_1.flowdown_rolegroup)
33. 6.416 290.092 ↓ 12.7 292 1

Nested Loop (cost=1,548.71..39,119.38 rows=23 width=20) (actual time=23.508..290.092 rows=292 loops=1)

  • Join Filter: (du_3.role_group_id = prg_1.id)
  • Rows Removed by Join Filter: 37,928
34. 0.120 0.120 ↓ 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.120 rows=13 loops=1)

  • Filter: (entity_type_id = 1)
  • Rows Removed by Filter: 215
35. 6.054 283.556 ↓ 7.2 2,940 13

Materialize (cost=1,548.56..39,029.96 rows=409 width=16) (actual time=1.808..21.812 rows=2,940 loops=13)

36. 1.169 277.502 ↓ 7.2 2,940 1

Nested Loop (cost=1,548.56..39,027.91 rows=409 width=16) (actual time=23.496..277.502 rows=2,940 loops=1)

37. 248.874 262.609 ↓ 1.8 292 1

Bitmap Heap Scan on child_sla et_1 (cost=1,548.14..37,520.40 rows=158 width=8) (actual time=23.429..262.609 rows=292 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,339
  • Heap Blocks: exact=24,660
38. 13.735 13.735 ↓ 5.9 188,142 1

Bitmap Index Scan on idx_child_sla_deleted_client_id_slaid (cost=0.00..1,548.10 rows=31,632 width=0) (actual time=13.734..13.735 rows=188,142 loops=1)

  • Index Cond: (deleted = false)
39. 8.232 13.724 ↓ 3.3 10 292

Index Scan using idx_du_entity_id on domain_user du_3 (cost=0.42..9.51 rows=3 width=16) (actual time=0.037..0.047 rows=10 loops=292)

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

SubPlan (for Index Scan)

41. 0.000 5.492 ↓ 0.0 0 5,492

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

42. 5.492 5.492 ↓ 0.0 0 5,492

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

  • Filter: (id = du_3.user_group_id)
  • Rows Removed by Filter: 8
43. 0.071 0.096 ↓ 293.0 293 1

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

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

Bitmap Heap Scan on role_group crg_1 (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
45. 0.003 0.019 ↓ 0.0 0 1

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

46. 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)
47. 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)
48. 2,766.992 3,113.888 ↑ 1.0 1 584

Bitmap Heap Scan on app_user u (cost=1.38..2.50 rows=1 width=17) (actual time=5.332..5.332 rows=1 loops=584)

  • Recheck Cond: (du_1.user_id = id)
  • Heap Blocks: exact=2,960,296
49. 346.896 346.896 ↓ 5,074.0 5,074 584

Bitmap Index Scan on pk_app_user (cost=0.00..1.38 rows=1 width=0) (actual time=0.594..0.594 rows=5,074 loops=584)

  • Index Cond: (du_1.user_id = id)
50. 0.005 0.013 ↑ 1.0 8 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
51. 0.008 0.008 ↑ 1.0 8 1

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

52. 0.562 3,728.841 ↓ 294.0 294 1

Sort (cost=25.08..25.08 rows=1 width=169) (actual time=3,728.753..3,728.841 rows=294 loops=1)

  • Sort Key: csla.id, r.name, ets.description, c.name, c.document_title
  • Sort Method: quicksort Memory: 111kB
53. 0.509 3,728.279 ↓ 294.0 294 1

Nested Loop (cost=4.71..25.07 rows=1 width=169) (actual time=613.764..3,728.279 rows=294 loops=1)

54. 0.510 3,726.594 ↓ 294.0 294 1

Nested Loop Left Join (cost=4.43..24.76 rows=1 width=151) (actual time=613.754..3,726.594 rows=294 loops=1)

55. 0.518 3,723.748 ↓ 292.0 292 1

Nested Loop Left Join (cost=4.01..23.62 rows=1 width=155) (actual time=613.739..3,723.748 rows=292 loops=1)

56. 0.514 3,722.062 ↓ 292.0 292 1

Nested Loop Left Join (cost=3.72..23.06 rows=1 width=109) (actual time=613.727..3,722.062 rows=292 loops=1)

57. 0.776 3,720.380 ↓ 292.0 292 1

Nested Loop (cost=3.44..22.74 rows=1 width=62) (actual time=613.715..3,720.380 rows=292 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)))
58. 1.277 3,717.560 ↓ 32.4 292 1

Nested Loop (cost=3.15..18.50 rows=9 width=70) (actual time=613.698..3,717.560 rows=292 loops=1)

59. 3,711.027 3,711.027 ↓ 292.0 292 1

CTE Scan on domain_user_with_flowdown_rgs du (cost=0.00..3.83 rows=1 width=8) (actual time=613.666..3,711.027 rows=292 loops=1)

  • Filter: (((role_group_id = 2,003) OR (role_group_id = 2,002)) AND ((user_id = 1,044) OR (SubPlan 6)))
  • Rows Removed by Filter: 292
60.          

SubPlan (for CTE Scan)

61. 0.000 0.000 ↓ 0.0 0

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

62. 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)
63. 1.752 5.256 ↑ 9.0 1 292

Bitmap Heap Scan on child_sla csla (cost=3.15..14.57 rows=9 width=62) (actual time=0.018..0.018 rows=1 loops=292)

  • 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 (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=343
64. 0.584 3.504 ↓ 0.0 0 292

BitmapOr (cost=3.15..3.15 rows=10 width=0) (actual time=0.012..0.012 rows=0 loops=292)

65. 1.752 1.752 ↑ 1.0 1 292

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

  • Index Cond: (du.entity_id = id)
66. 1.168 1.168 ↓ 0.0 0 292

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

  • Index Cond: ((du.entity_id = slaid) AND (client_id = 1,002) AND (deleted = false))
67. 2.044 2.044 ↑ 1.0 1 292

Index Scan using pk_sla on sla (cost=0.29..0.41 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=292)

  • Index Cond: (id = csla.slaid)
  • Filter: (client_id = 1,002)
68. 1.168 1.168 ↑ 1.0 1 292

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

  • Index Cond: (id = csla.relation_id)
69. 1.168 1.168 ↑ 1.0 1 292

Index Scan using pk_contract on contract c (cost=0.29..0.56 rows=1 width=54) (actual time=0.004..0.004 rows=1 loops=292)

  • Index Cond: (id = csla.contract_id)
70. 2.336 2.336 ↑ 1.0 1 292

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.007..0.008 rows=1 loops=292)

  • Index Cond: ((entity_id = sla.id) AND (entity_type_id = 14))
  • Heap Fetches: 312
71. 1.176 1.176 ↑ 1.0 1 294

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

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

SubPlan (for Group)

73. 0.584 14.308 ↑ 1.0 1 292

Aggregate (cost=2.91..2.92 rows=1 width=32) (actual time=0.049..0.049 rows=1 loops=292)

74. 0.876 13.724 ↓ 2.0 2 292

Nested Loop (cost=0.14..2.90 rows=1 width=1,310) (actual time=0.017..0.047 rows=2 loops=292)

75. 12.264 12.264 ↓ 2.0 2 292

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.042 rows=2 loops=292)

  • Filter: (entity_id = csla.id)
  • Rows Removed by Filter: 582
76. 0.584 0.584 ↑ 1.0 1 584

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

  • Index Cond: (id = du_4.role_group_id)
  • Filter: (entity_type_id = 15)
Planning time : 5.897 ms
Execution time : 3,747.046 ms