explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PTq5

Settings
# exclusive inclusive rows x rows loops node
1. 41.865 54,908.846 ↓ 19,133.0 38,266 1

Sort (cost=7,405.82..7,405.82 rows=2 width=381) (actual time=54,899.567..54,908.846 rows=38,266 loops=1)

  • Sort Key: objects.depth
  • Sort Method: external merge Disk: 12,984kB
2.          

CTE path_set

3. 0.081 0.081 ↑ 500.0 2 1

Function Scan on fixed_json_populate_recordset (cost=0.25..10.25 rows=1,000 width=53) (actual time=0.080..0.081 rows=2 loops=1)

4.          

CTE global_permissions

5. 2.932 26.102 ↓ 21,536.0 21,536 1

Unique (cost=1,171.64..1,171.64 rows=1 width=4) (actual time=21.194..26.102 rows=21,536 loops=1)

6. 7.378 23.170 ↓ 21,758.0 21,758 1

Sort (cost=1,171.64..1,171.64 rows=1 width=4) (actual time=21.193..23.170 rows=21,758 loops=1)

  • Sort Key: data.id
  • Sort Method: quicksort Memory: 1,788kB
7. 2.609 15.792 ↓ 21,758.0 21,758 1

Nested Loop (cost=0.99..1,171.63 rows=1 width=4) (actual time=0.038..15.792 rows=21,758 loops=1)

8. 0.022 0.258 ↓ 15.7 235 1

Nested Loop (cost=0.56..93.67 rows=15 width=8) (actual time=0.019..0.258 rows=235 loops=1)

9. 0.040 0.040 ↓ 7.0 7 1

Index Scan using rm_2_objectid_index on rm_2 rm (cost=0.28..19.36 rows=1 width=4) (actual time=0.011..0.040 rows=7 loops=1)

  • Index Cond: ("objectId" IS NULL)
  • Filter: (((type = 1) AND ("userGroupId" = 35)) OR ((type = 2) AND ("userGroupId" = ANY ('{81,91,96,100,119,132,165}'::integer[]))))
  • Rows Removed by Filter: 149
10. 0.196 0.196 ↓ 1.5 34 7

Index Scan using role_id_object_type_id_object_life_cycle_state_id_org_key on role_permissions rp (cost=0.29..74.08 rows=23 width=12) (actual time=0.004..0.028 rows=34 loops=7)

  • Index Cond: (role_id = rm."roleId")
  • Filter: ((permission >= 1) AND (assigned >= false))
  • Rows Removed by Filter: 8
11. 12.925 12.925 ↓ 3.6 93 235

Index Scan using ix_od_2_typestate on od_2 data (cost=0.43..71.60 rows=26 width=12) (actual time=0.000..0.055 rows=93 loops=235)

  • Index Cond: (("objectTypeId" = rp.object_type_id) AND ("objectTypeId" = ANY ('{40,42,42}'::integer[])) AND ("objectLifeCycleStateId" = rp.object_life_cycle_state_id))
12.          

CTE assigned_permissions

13. 2.119 641.656 ↓ 16,877.0 16,877 1

Unique (cost=2,974.86..2,974.87 rows=1 width=4) (actual time=638.757..641.656 rows=16,877 loops=1)

14. 4.508 639.537 ↓ 16,932.0 16,932 1

Sort (cost=2,974.86..2,974.86 rows=1 width=4) (actual time=638.756..639.537 rows=16,932 loops=1)

  • Sort Key: data_1.id
  • Sort Method: quicksort Memory: 1,562kB
15. 8.082 635.029 ↓ 16,932.0 16,932 1

Nested Loop (cost=31.58..2,974.85 rows=1 width=4) (actual time=0.068..635.029 rows=16,932 loops=1)

  • Join Filter: (rm_1."roleId" = rp_1.role_id)
16. 0.000 610.014 ↓ 2,419.0 16,933 1

Nested Loop (cost=31.30..2,972.09 rows=7 width=20) (actual time=0.058..610.014 rows=16,933 loops=1)

17. 25.924 83.388 ↓ 480.0 288,479 1

Nested Loop (cost=30.87..1,394.97 rows=601 width=12) (actual time=0.046..83.388 rows=288,479 loops=1)

18. 0.022 0.046 ↓ 4.8 38 1

Bitmap Heap Scan on rm_2 rm_1 (cost=30.32..40.59 rows=8 width=8) (actual time=0.030..0.046 rows=38 loops=1)

  • Recheck Cond: ((("userGroupId" = 35) AND (type = 1) AND ("objectId" IS NOT NULL)) OR (("userGroupId" = ANY ('{81,91,96,100,119,132,165}'::integer[])) AND (type = 2) AND ("objectId" IS NOT NULL)))
  • Heap Blocks: exact=3
19. 0.001 0.024 ↓ 0.0 0 1

BitmapOr (cost=30.32..30.32 rows=8 width=0) (actual time=0.024..0.024 rows=0 loops=1)

20. 0.008 0.008 ↓ 0.0 0 1

Bitmap Index Scan on ix_rm_2_p (cost=0.00..4.29 rows=1 width=0) (actual time=0.008..0.008 rows=0 loops=1)

  • Index Cond: (("userGroupId" = 35) AND (type = 1) AND ("objectId" IS NOT NULL))
21. 0.015 0.015 ↓ 5.6 39 1

Bitmap Index Scan on ix_rm_2_p (cost=0.00..26.03 rows=7 width=0) (actual time=0.015..0.015 rows=39 loops=1)

  • Index Cond: (("userGroupId" = ANY ('{81,91,96,100,119,132,165}'::integer[])) AND (type = 2) AND ("objectId" IS NOT NULL))
22. 57.418 57.418 ↓ 13.2 7,592 38

Index Only Scan using "pg_2_anchorObjectId_roleId_memberObjectId_idx" on pg_2 pg (cost=0.55..163.55 rows=575 width=12) (actual time=0.003..1.511 rows=7,592 loops=38)

  • Index Cond: (("anchorObjectId" = rm_1."objectId") AND ("roleId" = rm_1."roleId"))
  • Heap Fetches: 0
23. 576.958 576.958 ↓ 0.0 0 288,479

Index Scan using ix_od_2_dwh_replication on od_2 data_1 (cost=0.43..2.62 rows=1 width=12) (actual time=0.002..0.002 rows=0 loops=288,479)

  • Index Cond: (id = pg."memberObjectId")
  • Filter: ("objectTypeId" = ANY ('{40,42,42}'::integer[]))
  • Rows Removed by Filter: 1
24. 16.933 16.933 ↑ 1.0 1 16,933

Index Scan using role_id_object_type_id_object_life_cycle_state_id_org_key on role_permissions rp_1 (cost=0.29..0.38 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=16,933)

  • Index Cond: ((role_id = pg."roleId") AND (object_type_id = data_1."objectTypeId") AND (object_life_cycle_state_id = data_1."objectLifeCycleStateId"))
  • Filter: ((permission >= 1) AND (assigned >= false))
  • Rows Removed by Filter: 0
25.          

CTE permissions

26. 3.676 688.242 ↓ 10,768.0 21,536 1

Unique (cost=0.08..0.09 rows=2 width=4) (actual time=682.831..688.242 rows=21,536 loops=1)

27. 9.028 684.566 ↓ 19,206.5 38,413 1

Sort (cost=0.08..0.09 rows=2 width=4) (actual time=682.830..684.566 rows=38,413 loops=1)

  • Sort Key: global_permissions.id
  • Sort Method: quicksort Memory: 3,166kB
28. 2.580 675.538 ↓ 19,206.5 38,413 1

Append (cost=0.00..0.07 rows=2 width=4) (actual time=21.196..675.538 rows=38,413 loops=1)

29. 29.183 29.183 ↓ 21,536.0 21,536 1

CTE Scan on global_permissions (cost=0.00..0.02 rows=1 width=4) (actual time=21.195..29.183 rows=21,536 loops=1)

30. 643.775 643.775 ↓ 16,877.0 16,877 1

CTE Scan on assigned_permissions (cost=0.00..0.02 rows=1 width=4) (actual time=638.758..643.775 rows=16,877 loops=1)

31.          

CTE objects

32. 169.725 387.730 ↓ 3,478.7 38,266 1

Recursive Union (cost=0.43..3,234.57 rows=11 width=352) (actual time=1.150..387.730 rows=38,266 loops=1)

33. 13.359 118.253 ↓ 19,133.0 19,133 1

Nested Loop (cost=0.43..867.13 rows=1 width=352) (actual time=1.148..118.253 rows=19,133 loops=1)

  • Join Filter: (d."objectTypeId" = paths."destinationType")
34. 0.085 0.085 ↑ 1.0 1 1

CTE Scan on path_set paths (cost=0.00..25.00 rows=1 width=40) (actual time=0.082..0.085 rows=1 loops=1)

  • Filter: ((''::text = path) AND (id = 1))
  • Rows Removed by Filter: 1
35. 22.410 104.809 ↓ 382.7 19,133 1

Nested Loop (cost=0.43..841.50 rows=50 width=203) (actual time=1.063..104.809 rows=19,133 loops=1)

36. 5.867 5.867 ↓ 191.3 19,133 1

Function Scan on unnest oid (cost=0.00..1.00 rows=100 width=4) (actual time=1.051..5.867 rows=19,133 loops=1)

37. 76.532 76.532 ↑ 1.0 1 19,133

Index Scan using ix_od_2_dwh_replication on od_2 d (cost=0.43..8.40 rows=1 width=203) (actual time=0.004..0.004 rows=1 loops=19,133)

  • Index Cond: (id = oid.oid)
  • Filter: (NOT COALESCE(("assessmentObjectTypeId" IS NOT NULL), true))
38. 14.439 99.752 ↓ 9,566.0 9,566 2

Nested Loop (cost=1.12..236.72 rows=1 width=352) (actual time=9.349..49.876 rows=9,566 loops=2)

39. 5.946 66.180 ↓ 9,566.0 9,566 2

Nested Loop (cost=0.70..236.01 rows=1 width=93) (actual time=9.342..33.090 rows=9,566 loops=2)

40. 3.334 21.968 ↓ 382.6 9,566 2

Hash Join (cost=0.26..24.26 rows=25 width=93) (actual time=9.329..10.984 rows=9,566 loops=2)

  • Hash Cond: (paths_1.previous = objects_1.depth)
41. 0.004 0.004 ↑ 500.0 2 2

CTE Scan on path_set paths_1 (cost=0.00..20.00 rows=1,000 width=21) (actual time=0.001..0.002 rows=2 loops=2)

42. 5.824 18.630 ↓ 3,826.6 19,133 2

Hash (cost=0.20..0.20 rows=5 width=76) (actual time=9.314..9.315 rows=19,133 loops=2)

  • Buckets: 32,768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1,527kB
43. 12.806 12.806 ↓ 3,826.6 19,133 2

WorkTable Scan on objects objects_1 (cost=0.00..0.20 rows=5 width=76) (actual time=0.023..6.403 rows=19,133 loops=2)

  • Filter: (NOT filtered)
44. 38.266 38.266 ↑ 1.0 1 19,133

Index Scan using ix_ol_2_sourcetype on ol_2 l (cost=0.43..8.46 rows=1 width=21) (actual time=0.002..0.002 rows=1 loops=19,133)

  • Index Cond: ((source = objects_1.objdest) AND (type = paths_1."relationshipType"))
  • Filter: ((paths_1."sourceType" = "sourceType") AND (paths_1."destinationType" = "destType") AND (paths_1.inverse = inverse))
45. 19.133 19.133 ↑ 1.0 1 19,133

Index Scan using ix_od_2_dwh_replication on od_2 d_1 (cost=0.43..0.67 rows=1 width=203) (actual time=0.001..0.001 rows=1 loops=19,133)

  • Index Cond: (id = l.dest)
46.          

CTE related

47. 24,182.398 53,978.183 ↓ 19,133.0 19,133 1

Nested Loop Semi Join (cost=0.86..13.96 rows=1 width=1,268) (actual time=2.807..53,978.183 rows=19,133 loops=1)

  • Join Filter: (data_2.id = permissions_1.id)
  • Rows Removed by Join Filter: 409,650,457
48. 8.606 579.694 ↓ 19,133.0 19,133 1

Nested Loop (cost=0.86..13.90 rows=1 width=1,268) (actual time=0.031..579.694 rows=19,133 loops=1)

49. 10.422 532.822 ↓ 19,133.0 19,133 1

Nested Loop (cost=0.43..8.71 rows=1 width=44) (actual time=0.020..532.822 rows=19,133 loops=1)

50. 445.868 445.868 ↓ 19,133.0 19,133 1

CTE Scan on objects objects_2 (cost=0.00..0.25 rows=1 width=36) (actual time=0.003..445.868 rows=19,133 loops=1)

  • Filter: (otpath = ''::text)
  • Rows Removed by Filter: 19,133
51. 76.532 76.532 ↑ 1.0 1 19,133

Index Scan using ix_ol_2_sourcetype on ol_2 link (cost=0.43..8.45 rows=1 width=12) (actual time=0.004..0.004 rows=1 loops=19,133)

  • Index Cond: ((source = objects_2.id) AND (type = 136))
  • Filter: (NOT inverse)
52. 38.266 38.266 ↑ 1.0 1 19,133

Index Scan using ix_od_2_dwh_replication on od_2 data_2 (cost=0.43..5.19 rows=1 width=1,224) (actual time=0.002..0.002 rows=1 loops=19,133)

  • Index Cond: (id = link.dest)
53. 29,216.091 29,216.091 ↓ 10,706.0 21,412 19,133

CTE Scan on permissions permissions_1 (cost=0.00..0.04 rows=2 width=4) (actual time=0.000..1.527 rows=21,412 loops=19,133)

54. 9.577 54,866.981 ↓ 19,133.0 38,266 1

Hash Left Join (cost=0.15..0.43 rows=2 width=381) (actual time=54,833.510..54,866.981 rows=38,266 loops=1)

  • Hash Cond: (objects.id = relations.source)
55. 16.494 718.266 ↓ 19,133.0 38,266 1

Hash Join (cost=0.07..0.35 rows=2 width=349) (actual time=694.358..718.266 rows=38,266 loops=1)

  • Hash Cond: (objects.id = permissions.id)
56. 8.574 8.574 ↓ 3,478.7 38,266 1

CTE Scan on objects (cost=0.00..0.22 rows=11 width=349) (actual time=1.153..8.574 rows=38,266 loops=1)

57. 2.132 693.198 ↓ 10,768.0 21,536 1

Hash (cost=0.04..0.04 rows=2 width=4) (actual time=693.197..693.198 rows=21,536 loops=1)

  • Buckets: 32,768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1,014kB
58. 691.066 691.066 ↓ 10,768.0 21,536 1

CTE Scan on permissions (cost=0.00..0.04 rows=2 width=4) (actual time=682.832..691.066 rows=21,536 loops=1)

59. 3.602 54,139.138 ↓ 19,126.0 19,126 1

Hash (cost=0.07..0.07 rows=1 width=36) (actual time=54,139.137..54,139.138 rows=19,126 loops=1)

  • Buckets: 32,768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1,710kB
60. 1.804 54,135.536 ↓ 19,126.0 19,126 1

Subquery Scan on relations (cost=0.03..0.07 rows=1 width=36) (actual time=54,114.063..54,135.536 rows=19,126 loops=1)

61. 17.575 54,133.732 ↓ 19,126.0 19,126 1

GroupAggregate (cost=0.03..0.06 rows=1 width=36) (actual time=54,114.062..54,133.732 rows=19,126 loops=1)

  • Group Key: ol.source
62. 13.556 54,116.157 ↓ 19,133.0 19,133 1

Sort (cost=0.03..0.04 rows=1 width=72) (actual time=54,114.038..54,116.157 rows=19,133 loops=1)

  • Sort Key: ol.source
  • Sort Method: quicksort Memory: 2,271kB
63. 54,102.601 54,102.601 ↓ 19,133.0 19,133 1

CTE Scan on related ol (cost=0.00..0.02 rows=1 width=72) (actual time=2.816..54,102.601 rows=19,133 loops=1)

Planning time : 6.475 ms
Execution time : 54,922.098 ms