explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sanH

Settings
# exclusive inclusive rows x rows loops node
1. 8.715 29,168.950 ↓ 292.0 292 1

Group (cost=42,510.36..42,517.24 rows=1 width=293) (actual time=26,140.735..29,168.950 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. 1.322 616.701 ↓ 146.0 584 1

HashAggregate (cost=20,255.19..20,255.23 rows=4 width=24) (actual time=615.854..616.701 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
4. 0.197 615.379 ↓ 146.0 584 1

Append (cost=75.21..20,255.13 rows=4 width=24) (actual time=260.436..615.379 rows=584 loops=1)

5. 7.951 308.506 ↓ 146.0 292 1

Nested Loop (cost=75.21..14,262.72 rows=2 width=24) (actual time=260.436..308.506 rows=292 loops=1)

6. 8.875 25.731 ↓ 11.5 24,984 1

Nested Loop (cost=74.79..12,090.04 rows=2,167 width=24) (actual time=2.843..25.731 rows=24,984 loops=1)

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

  • Filter: ((entity_type_id = 15) AND (client_id = 1,002))
  • Rows Removed by Filter: 226
8. 13.472 16.720 ↓ 3.4 12,492 2

Bitmap Heap Scan on domain_user du_1 (cost=74.65..12,036.10 rows=3,657 width=16) (actual time=1.413..8.360 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,161
9. 1.412 1.412 ↓ 2.3 15,792 2

Bitmap Index Scan on idx_domain_user_3 (cost=0.00..73.73 rows=6,988 width=0) (actual time=0.706..0.706 rows=15,792 loops=2)

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

SubPlan (for Bitmap Heap Scan)

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

12. 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
13. 274.824 274.824 ↓ 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.011..0.011 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
14. 0.004 24.136 ↓ 0.0 0 1

Merge Join (cost=1,552.90..2,806.02 rows=1 width=24) (actual time=24.135..24.136 rows=0 loops=1)

  • Merge Cond: (prg.id = crg.flowdown_rolegroup)
15. 0.003 24.099 ↑ 30.0 1 1

Nested Loop (cost=1,548.69..39,141.83 rows=30 width=20) (actual time=24.099..24.099 rows=1 loops=1)

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

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

Materialize (cost=1,548.54..39,027.86 rows=409 width=16) (actual time=24.085..24.085 rows=1 loops=1)

18. 0.004 24.082 ↑ 409.0 1 1

Nested Loop (cost=1,548.54..39,025.81 rows=409 width=16) (actual time=24.082..24.082 rows=1 loops=1)

19. 9.880 24.042 ↑ 158.0 1 1

Bitmap Heap Scan on child_sla et (cost=1,548.12..37,518.30 rows=158 width=8) (actual time=24.042..24.042 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
20. 14.162 14.162 ↓ 5.9 188,130 1

Bitmap Index Scan on idx_child_sla_deleted_client_id_slaid (cost=0.00..1,548.08 rows=31,630 width=0) (actual time=14.162..14.162 rows=188,130 loops=1)

  • Index Cond: (deleted = false)
21. 0.021 0.036 ↑ 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.036..0.036 rows=1 loops=1)

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

SubPlan (for Index Scan)

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

24. 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
25. 0.008 0.033 ↓ 2.0 2 1

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

  • Sort Key: crg.flowdown_rolegroup
  • Sort Method: quicksort Memory: 25kB
26. 0.007 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
27. 0.002 0.018 ↓ 0.0 0 1

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

28. 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)
29. 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)
30. 0.256 282.540 ↓ 292.0 292 1

Merge Join (cost=1,552.90..3,186.33 rows=1 width=24) (actual time=22.641..282.540 rows=292 loops=1)

  • Merge Cond: (prg_1.id = crg_1.flowdown_rolegroup)
31. 6.383 282.183 ↓ 12.7 292 1

Nested Loop (cost=1,548.69..39,117.29 rows=23 width=20) (actual time=22.611..282.183 rows=292 loops=1)

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

  • Filter: (entity_type_id = 1)
  • Rows Removed by Filter: 215
33. 5.939 275.691 ↓ 7.2 2,940 13

Materialize (cost=1,548.54..39,027.86 rows=409 width=16) (actual time=1.739..21.207 rows=2,940 loops=13)

34. 1.122 269.752 ↓ 7.2 2,940 1

Nested Loop (cost=1,548.54..39,025.81 rows=409 width=16) (actual time=22.600..269.752 rows=2,940 loops=1)

35. 241.615 254.614 ↓ 1.8 292 1

Bitmap Heap Scan on child_sla et_1 (cost=1,548.12..37,518.30 rows=158 width=8) (actual time=22.537..254.614 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,658
36. 12.999 12.999 ↓ 5.9 188,130 1

Bitmap Index Scan on idx_child_sla_deleted_client_id_slaid (cost=0.00..1,548.08 rows=31,630 width=0) (actual time=12.999..12.999 rows=188,130 loops=1)

  • Index Cond: (deleted = false)
37. 8.524 14.016 ↓ 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.038..0.048 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
38.          

SubPlan (for Index Scan)

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

40. 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
41. 0.080 0.101 ↓ 293.0 293 1

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

  • Sort Key: crg_1.flowdown_rolegroup
  • Sort Method: quicksort Memory: 25kB
42. 0.005 0.021 ↓ 2.0 2 1

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

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

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

44. 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.006..0.007 rows=9 loops=1)

  • Index Cond: (entity_type_id = 15)
45. 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)
46. 0.966 26,125.479 ↓ 294.0 294 1

Sort (cost=22,255.13..22,255.13 rows=1 width=169) (actual time=26,125.322..26,125.479 rows=294 loops=1)

  • Sort Key: csla.id, r.name, ets.description, c.name, c.document_title
  • Sort Method: quicksort Memory: 111kB
47. 0.854 26,124.513 ↓ 294.0 294 1

Nested Loop (cost=4,128.23..22,255.12 rows=1 width=169) (actual time=649.886..26,124.513 rows=294 loops=1)

48. 1.099 26,121.895 ↓ 294.0 294 1

Nested Loop Left Join (cost=4,127.95..22,254.82 rows=1 width=151) (actual time=649.875..26,121.895 rows=294 loops=1)

49. 0.848 26,117.584 ↓ 292.0 292 1

Nested Loop Left Join (cost=4,127.53..22,253.67 rows=1 width=155) (actual time=649.858..26,117.584 rows=292 loops=1)

50. 1.415 26,114.692 ↓ 292.0 292 1

Nested Loop Left Join (cost=4,127.24..22,253.11 rows=1 width=109) (actual time=649.847..26,114.692 rows=292 loops=1)

51. 2,185.272 26,110.649 ↓ 292.0 292 1

Hash Join (cost=4,126.95..22,252.79 rows=1 width=62) (actual time=649.833..26,110.649 rows=292 loops=1)

  • Hash Cond: (csla.slaid = sla.id)
  • 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)))
  • Rows Removed by Join Filter: 7,234,300
52. 7,068.228 23,912.221 ↓ 307.1 7,234,592 1

Nested Loop (cost=1,652.95..19,716.96 rows=23,555 width=70) (actual time=627.442..23,912.221 rows=7,234,592 loops=1)

53. 617.845 617.845 ↓ 292.0 292 1

CTE Scan on domain_user_with_flowdown_rgs du (cost=0.00..3.83 rows=1 width=8) (actual time=615.860..617.845 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
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. 13,601.944 16,226.148 ↓ 1.1 24,776 292

Bitmap Heap Scan on child_sla csla (cost=1,652.95..19,477.57 rows=23,555 width=62) (actual time=11.553..55.569 rows=24,776 loops=292)

  • Recheck Cond: (client_id = 1,002)
  • Filter: ((NOT deleted) 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[])))
  • Rows Removed by Filter: 787
  • Heap Blocks: exact=5,397,328
58. 2,624.204 2,624.204 ↓ 2.3 56,079 292

Bitmap Index Scan on idx_child_sla_deleted_client_id_slaid (cost=0.00..1,647.07 rows=24,886 width=0) (actual time=8.987..8.987 rows=56,079 loops=292)

  • Index Cond: ((client_id = 1,002) AND (deleted = false))
59. 2.030 13.156 ↓ 1.0 8,361 1

Hash (cost=2,369.53..2,369.53 rows=8,358 width=8) (actual time=13.156..13.156 rows=8,361 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 455kB
60. 11.126 11.126 ↓ 1.0 8,361 1

Seq Scan on sla (cost=0.00..2,369.53 rows=8,358 width=8) (actual time=0.009..11.126 rows=8,361 loops=1)

  • Filter: (client_id = 1,002)
  • Rows Removed by Filter: 3,281
61. 2.628 2.628 ↑ 1.0 1 292

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

  • Index Cond: (id = csla.relation_id)
62. 2.044 2.044 ↑ 1.0 1 292

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

  • Index Cond: (id = csla.contract_id)
63. 3.212 3.212 ↑ 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.011..0.011 rows=1 loops=292)

  • Index Cond: ((entity_id = sla.id) AND (entity_type_id = 14))
  • Heap Fetches: 312
64. 1.764 1.764 ↑ 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.006..0.006 rows=1 loops=294)

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

SubPlan (for Group)

66. 1.460 3,034.756 ↑ 1.0 1 292

Aggregate (cost=6.59..6.60 rows=1 width=32) (actual time=10.393..10.393 rows=1 loops=292)

67. 2.044 3,033.296 ↓ 2.0 2 292

Nested Loop Left Join (cost=1.53..6.58 rows=1 width=291) (actual time=5.183..10.388 rows=2 loops=292)

  • Join Filter: (du_4.user_group_id = ug_3.id)
  • Rows Removed by Join Filter: 16
68. 1.460 3,029.500 ↓ 2.0 2 292

Nested Loop Left Join (cost=1.53..5.40 rows=1 width=21) (actual time=5.176..10.375 rows=2 loops=292)

69. 0.876 15.768 ↓ 2.0 2 292

Nested Loop (cost=0.14..2.90 rows=1 width=8) (actual time=0.020..0.054 rows=2 loops=292)

70. 12.556 12.556 ↓ 2.0 2 292

CTE Scan on domain_user_with_flowdown_rgs du_4 (cost=0.00..0.09 rows=1 width=12) (actual time=0.014..0.043 rows=2 loops=292)

  • Filter: (entity_id = csla.id)
  • Rows Removed by Filter: 582
71. 2.336 2.336 ↑ 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.004..0.004 rows=1 loops=584)

  • Index Cond: (id = du_4.role_group_id)
  • Filter: (entity_type_id = 15)
72. 2,679.976 3,012.272 ↑ 1.0 1 584

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

  • Recheck Cond: (du_4.user_id = id)
  • Heap Blocks: exact=2,952,120
73. 332.296 332.296 ↓ 5,059.0 5,059 584

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

  • Index Cond: (du_4.user_id = id)
74. 1.752 1.752 ↑ 1.0 8 584

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

Planning time : 4.790 ms
Execution time : 29,169.517 ms