explain.depesz.com

PostgreSQL's explain analyze made readable

Result: eLXc1

Settings
# exclusive inclusive rows x rows loops node
1. 8.044 2,908.094 ↓ 220.0 220 1

Group (cost=69,665.12..69,721.13 rows=1 width=292) (actual time=463.322..2,908.094 rows=220 loops=1)

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

CTE domain_user_with_flowdown_rgs

3. 21.567 394.554 ↓ 10.7 20,810 1

HashAggregate (cost=67,708.74..67,728.27 rows=1,953 width=24) (actual time=385.505..394.554 rows=20,810 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
4. 5.827 372.987 ↓ 10.7 20,810 1

Append (cost=2,859.49..67,679.45 rows=1,953 width=24) (actual time=25.072..372.987 rows=20,810 loops=1)

5. 10.272 301.678 ↓ 10.5 20,566 1

Hash Join (cost=2,859.49..61,534.34 rows=1,951 width=24) (actual time=25.071..301.678 rows=20,566 loops=1)

  • Hash Cond: (du_1.role_group_id = rg.id)
6. 9.931 266.386 ↓ 1.3 29,283 1

Nested Loop (cost=0.42..58,615.92 rows=22,178 width=16) (actual time=0.041..266.386 rows=29,283 loops=1)

7. 7.505 7.505 ↑ 1.0 9,958 1

Seq Scan on issue_mgmt im2_1 (cost=0.00..2,278.58 rows=9,958 width=4) (actual time=0.007..7.505 rows=9,958 loops=1)

8. 195.595 248.950 ↓ 1.5 3 9,958

Index Scan using idx_du_entity_id on domain_user du_1 (cost=0.42..5.64 rows=2 width=16) (actual time=0.013..0.025 rows=3 loops=9,958)

  • Index Cond: (entity_id = im2_1.id)
  • Filter: ((NOT entity_deleted) AND ((user_id = 1,044) OR (SubPlan 2)))
  • Rows Removed by Filter: 16
9.          

SubPlan (for Index Scan)

10. 0.000 53.355 ↓ 0.0 0 53,355

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

11. 53.355 53.355 ↓ 0.0 0 53,355

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

  • Filter: (id = du_1.user_group_id)
  • Rows Removed by Filter: 8
12. 0.014 25.020 ↓ 1.4 26 1

Hash (cost=2,858.82..2,858.82 rows=19 width=12) (actual time=25.020..25.020 rows=26 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
13. 0.173 25.006 ↓ 1.4 26 1

Index Scan using pk_role_group on role_group rg (cost=2,841.06..2,858.82 rows=19 width=12) (actual time=24.859..25.006 rows=26 loops=1)

  • Filter: ((client_id = 1,002) AND ((hashed SubPlan 1) OR (entity_type_id = 17)))
  • Rows Removed by Filter: 202
14.          

SubPlan (for Index Scan)

15. 0.166 24.833 ↑ 1.2 13 1

Unique (cost=0.29..2,840.88 rows=15 width=4) (actual time=0.460..24.833 rows=13 loops=1)

16. 24.667 24.667 ↑ 1.3 780 1

Index Scan using idx_issue_mgmt_2 on issue_mgmt im2 (cost=0.29..2,838.34 rows=1,016 width=4) (actual time=0.459..24.667 rows=780 loops=1)

  • Filter: ((NOT deleted) AND (client_id = 1,002))
  • Rows Removed by Filter: 9,178
17. 0.275 54.320 ↓ 167.0 167 1

Hash Join (cost=874.64..3,157.16 rows=1 width=24) (actual time=53.706..54.320 rows=167 loops=1)

  • Hash Cond: (et.contract_id = du_2.entity_id)
18. 7.438 7.438 ↑ 1.0 1,046 1

Seq Scan on issue_mgmt et (cost=0.00..2,278.58 rows=1,046 width=8) (actual time=0.013..7.438 rows=1,046 loops=1)

  • Filter: (NOT deleted)
  • Rows Removed by Filter: 8,912
19. 6.046 46.607 ↓ 602.9 20,498 1

Hash (cost=874.22..874.22 rows=34 width=24) (actual time=46.607..46.607 rows=20,498 loops=1)

  • Buckets: 32,768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1,297kB
20. 9.760 40.561 ↓ 602.9 20,498 1

Merge Join (cost=5.81..874.22 rows=34 width=24) (actual time=0.069..40.561 rows=20,498 loops=1)

  • Merge Cond: (prg.id = crg.flowdown_rolegroup)
21. 5.879 27.711 ↑ 1.7 20,498 1

Nested Loop (cost=0.57..118,470.29 rows=34,685 width=20) (actual time=0.033..27.711 rows=20,498 loops=1)

22. 0.140 0.140 ↓ 1.1 17 1

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

  • Filter: (entity_type_id = 61)
  • Rows Removed by Filter: 211
23. 18.175 21.692 ↑ 3.0 1,206 17

Index Scan using idx_domain_user_3 on domain_user du_2 (cost=0.42..7,366.76 rows=3,658 width=16) (actual time=0.221..1.276 rows=1,206 loops=17)

  • Index Cond: (role_group_id = prg.id)
  • Filter: ((NOT entity_deleted) AND ((user_id = 1,044) OR (SubPlan 3)))
  • Rows Removed by Filter: 1,036
24.          

SubPlan (for Index Scan)

25. 0.000 3.517 ↓ 0.0 0 3,517

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

26. 3.517 3.517 ↓ 0.0 0 3,517

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

  • Filter: (id = du_2.user_group_id)
  • Rows Removed by Filter: 8
27. 3.065 3.090 ↓ 10,250.0 20,500 1

Sort (cost=5.24..5.25 rows=2 width=16) (actual time=0.031..3.090 rows=20,500 loops=1)

  • Sort Key: crg.flowdown_rolegroup
  • Sort Method: quicksort Memory: 25kB
28. 0.007 0.025 ↓ 1.5 3 1

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

  • Recheck Cond: ((entity_type_id = 17) AND (client_id = 1,002))
  • Heap Blocks: exact=2
29. 0.001 0.018 ↓ 0.0 0 1

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

30. 0.008 0.008 ↓ 1.1 11 1

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

  • Index Cond: (entity_type_id = 17)
31. 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.008..0.009 rows=66 loops=1)

  • Index Cond: (client_id = 1,002)
32. 0.288 11.162 ↓ 77.0 77 1

Hash Join (cost=676.14..2,958.65 rows=1 width=24) (actual time=10.571..11.162 rows=77 loops=1)

  • Hash Cond: (et_1.relation_id = du_3.entity_id)
33. 7.311 7.311 ↑ 1.0 1,046 1

Seq Scan on issue_mgmt et_1 (cost=0.00..2,278.58 rows=1,046 width=8) (actual time=0.014..7.311 rows=1,046 loops=1)

  • Filter: (NOT deleted)
  • Rows Removed by Filter: 8,912
34. 0.029 3.563 ↓ 2.8 69 1

Hash (cost=675.83..675.83 rows=25 width=24) (actual time=3.562..3.563 rows=69 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
35. 0.044 3.534 ↓ 2.8 69 1

Merge Join (cost=5.81..675.83 rows=25 width=24) (actual time=0.079..3.534 rows=69 loops=1)

  • Merge Cond: (prg_1.id = crg_1.flowdown_rolegroup)
36. 0.034 3.441 ↑ 377.0 69 1

Nested Loop (cost=0.57..91,648.59 rows=26,014 width=20) (actual time=0.038..3.441 rows=69 loops=1)

37. 0.118 0.118 ↓ 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.013..0.118 rows=13 loops=1)

  • Filter: (entity_type_id = 1)
  • Rows Removed by Filter: 215
38. 2.956 3.289 ↑ 731.6 5 13

Index Scan using idx_domain_user_3 on domain_user du_3 (cost=0.42..7,599.40 rows=3,658 width=16) (actual time=0.055..0.253 rows=5 loops=13)

  • Index Cond: (role_group_id = prg_1.id)
  • Filter: ((NOT entity_deleted) AND ((user_id = 1,044) OR (SubPlan 4)))
  • Rows Removed by Filter: 420
39.          

SubPlan (for Index Scan)

40. 0.000 0.333 ↓ 0.0 0 333

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

41. 0.333 0.333 ↓ 0.0 0 333

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

  • Filter: (id = du_3.user_group_id)
  • Rows Removed by Filter: 8
42. 0.019 0.049 ↓ 35.0 70 1

Sort (cost=5.24..5.25 rows=2 width=16) (actual time=0.037..0.049 rows=70 loops=1)

  • Sort Key: crg_1.flowdown_rolegroup
  • Sort Method: quicksort Memory: 25kB
43. 0.008 0.030 ↓ 1.5 3 1

Bitmap Heap Scan on role_group crg_1 (cost=3.09..5.23 rows=2 width=16) (actual time=0.027..0.030 rows=3 loops=1)

  • Recheck Cond: ((entity_type_id = 17) AND (client_id = 1,002))
  • Heap Blocks: exact=2
44. 0.003 0.022 ↓ 0.0 0 1

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

45. 0.009 0.009 ↓ 1.1 11 1

Bitmap Index Scan on role_group_entity_type_id_idx (cost=0.00..1.32 rows=10 width=0) (actual time=0.009..0.009 rows=11 loops=1)

  • Index Cond: (entity_type_id = 17)
46. 0.010 0.010 ↓ 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.010..0.010 rows=66 loops=1)

  • Index Cond: (client_id = 1,002)
47. 0.566 437.150 ↓ 266.0 266 1

Sort (cost=1,936.85..1,936.85 rows=1 width=168) (actual time=436.973..437.150 rows=266 loops=1)

  • Sort Key: im.id, r.name, ets.description, c.name, c.document_title
  • Sort Method: quicksort Memory: 87kB
48. 0.246 436.584 ↓ 266.0 266 1

Nested Loop (cost=3.92..1,936.84 rows=1 width=168) (actual time=386.004..436.584 rows=266 loops=1)

49. 0.155 435.806 ↓ 266.0 266 1

Nested Loop Left Join (cost=3.65..1,934.34 rows=1 width=150) (actual time=385.992..435.806 rows=266 loops=1)

50. 0.287 434.331 ↓ 220.0 220 1

Nested Loop Left Join (cost=3.22..1,931.68 rows=1 width=150) (actual time=385.976..434.331 rows=220 loops=1)

51. 0.153 433.604 ↓ 220.0 220 1

Nested Loop Left Join (cost=2.93..1,929.18 rows=1 width=104) (actual time=385.970..433.604 rows=220 loops=1)

52. 0.526 432.791 ↓ 220.0 220 1

Nested Loop (cost=2.65..1,926.68 rows=1 width=57) (actual time=385.958..432.791 rows=220 loops=1)

53. 406.121 406.121 ↓ 68.8 688 1

CTE Scan on domain_user_with_flowdown_rgs du (cost=0.00..1,872.44 rows=10 width=8) (actual time=385.549..406.121 rows=688 loops=1)

  • Filter: (((role_group_id = 2,009) OR (role_group_id = 2,000)) AND ((user_id = 1,044) OR (SubPlan 7)))
  • Rows Removed by Filter: 20,122
54.          

SubPlan (for CTE Scan)

55. 0.000 0.000 ↓ 0.0 0

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

56. 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)
57. 26.144 26.144 ↓ 0.0 0 688

Index Scan using pk_issue_mgmt on issue_mgmt im (cost=2.65..5.41 rows=1 width=65) (actual time=0.038..0.038 rows=0 loops=688)

  • Index Cond: (id = du.entity_id)
  • Filter: ((NOT deleted) AND (client_id = 1,002) AND (((du.entity_id = id) AND (du.role_group_id = 2,009)) OR ((du.entity_id = entity_id) AND (hashed SubPlan 8) AND (du.role_group_id = 2,000))) AND (status_id = ANY ('{1552,1,1553,1557,1555,1554,1551,2,4,1556,1691,1689,1690,1688,1687,1685,1686}'::integer[])) AND ((change_tz(planned_completion_date, 'UTC'::character varying, 'Asia/Kolkata'::character varying))::date >= '2020-07-24 00:00:00'::timestamp without time zone) AND ((change_tz(planned_completion_date, 'UTC'::character varying, 'Asia/Kolkata'::character varying))::date <= '2020-09-15 00:00:00'::timestamp without time zone))
  • Rows Removed by Filter: 1
58.          

SubPlan (for Index Scan)

59. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_role_group on role_group (cost=0.14..2.36 rows=1 width=4) (never executed)

  • Index Cond: (id = 2,000)
60. 0.660 0.660 ↑ 1.0 1 220

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

  • Index Cond: (id = im.relation_id)
61. 0.440 0.440 ↑ 1.0 1 220

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

  • Index Cond: (id = im.contract_id)
62. 1.320 1.320 ↑ 1.0 1 220

Index Only Scan using idx_link_entity_function_6 on link_entity_function lef (cost=0.42..2.64 rows=1 width=4) (actual time=0.005..0.006 rows=1 loops=220)

  • Index Cond: ((entity_id = im.id) AND (entity_type_id = 17))
  • Heap Fetches: 266
63. 0.532 0.532 ↑ 1.0 1 266

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

  • Index Cond: (id = im.status_id)
64.          

SubPlan (for Group)

65. 1.320 2,462.900 ↑ 1.0 1 220

Aggregate (cost=55.72..55.73 rows=1 width=32) (actual time=11.195..11.195 rows=1 loops=220)

66. 0.955 2,461.580 ↓ 2.0 2 220

Nested Loop Left Join (cost=10.20..55.71 rows=1 width=291) (actual time=6.258..11.189 rows=2 loops=220)

67. 1.400 2,460.260 ↓ 2.0 2 220

Nested Loop Left Join (cost=10.07..55.15 rows=1 width=21) (actual time=6.255..11.183 rows=2 loops=220)

68. 1.065 321.420 ↓ 2.0 2 220

Hash Join (cost=8.69..52.66 rows=1 width=8) (actual time=0.547..1.461 rows=2 loops=220)

  • Hash Cond: (du_4.role_group_id = rg_1.id)
69. 320.320 320.320 ↑ 2.5 4 220

CTE Scan on domain_user_with_flowdown_rgs du_4 (cost=0.00..43.94 rows=10 width=12) (actual time=0.310..1.456 rows=4 loops=220)

  • Filter: (entity_id = im.id)
  • Rows Removed by Filter: 20,806
70. 0.007 0.035 ↓ 1.1 11 1

Hash (cost=8.56..8.56 rows=10 width=4) (actual time=0.035..0.035 rows=11 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
71. 0.018 0.028 ↓ 1.1 11 1

Bitmap Heap Scan on role_group rg_1 (cost=1.32..8.56 rows=10 width=4) (actual time=0.017..0.028 rows=11 loops=1)

  • Recheck Cond: (entity_type_id = 17)
  • Heap Blocks: exact=5
72. 0.010 0.010 ↓ 1.1 11 1

Bitmap Index Scan on role_group_entity_type_id_idx (cost=0.00..1.32 rows=10 width=0) (actual time=0.010..0.010 rows=11 loops=1)

  • Index Cond: (entity_type_id = 17)
73. 1,915.155 2,137.440 ↑ 1.0 1 365

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

  • Recheck Cond: (du_4.user_id = id)
  • Heap Blocks: exact=1,856,755
74. 222.285 222.285 ↓ 5,092.0 5,092 365

Bitmap Index Scan on pk_app_user (cost=0.00..1.38 rows=1 width=0) (actual time=0.609..0.609 rows=5,092 loops=365)

  • Index Cond: (du_4.user_id = id)
75. 0.365 0.365 ↓ 0.0 0 365

Index Scan using pk_user_group on user_group ug_3 (cost=0.13..0.48 rows=1 width=278) (actual time=0.001..0.001 rows=0 loops=365)

  • Index Cond: (du_4.user_group_id = id)
Planning time : 3.583 ms
Execution time : 2,909.325 ms