explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OWsN

Settings
# exclusive inclusive rows x rows loops node
1. 0.059 635.303 ↑ 1,802.2 16 1
→

Sort (cost=149,233.17..149,305.26 rows=28,835 width=96) (actual time=635.299..635.303 rows=16 loops=1)

  • Sort Key: s.owner_id, s.id, (lib.__dictionary_getname('share_type'::character varying, (s.sharetype_id)::integer))
  • Sort Method: quicksort Memory: 26kB
2. 0.018 635.244 ↑ 1,802.2 16 1
→

Append (cost=60.61..145,616.64 rows=28,835 width=96) (actual time=3.049..635.244 rows=16 loops=1)

3. 7.452 122.466 ↑ 215.5 13 1
→

Nested Loop Anti Join (cost=60.61..49,483.64 rows=2,801 width=96) (actual time=3.049..122.466 rows=13 loops=1)

4. 4.263 50.972 ↓ 1.0 2,911 1
→

Nested Loop (cost=60.18..26,111.26 rows=2,819 width=38) (actual time=0.530..50.972 rows=2,911 loops=1)

5. 2.215 2.639 ↑ 1.0 2,938 1
→

Bitmap Heap Scan on shares s (cost=59.74..1,263.84 rows=3,010 width=34) (actual time=0.510..2.639 rows=2,938 loops=1)

  • Recheck Cond: (parent_shareuser_id IS NOT NULL)
  • Heap Blocks: exact=416
6. 0.424 0.424 ↑ 1.0 2,938 1
→

Bitmap Index Scan on ix_shares_parent_shareuser (cost=0.00..58.99 rows=3,010 width=0) (actual time=0.424..0.424 rows=2,938 loops=1)

  • Index Cond: (parent_shareuser_id IS NOT NULL)
7. 44.070 44.070 ↑ 1.0 1 2,938
→

Index Scan using pk_objects on objects o (cost=0.43..8.24 rows=1 width=12) (actual time=0.014..0.015 rows=1 loops=2,938)

  • Index Cond: (id = s.object_id)
  • Filter: (type_id <> ALL ('{3,5,16}'::integer[]))
  • Rows Removed by Filter: 0
8. 64.042 64.042 ↑ 1.0 1 2,911
→

Index Scan using ux_objectpermissions on objectpermissions op (cost=0.43..7.78 rows=1 width=24) (actual time=0.022..0.022 rows=1 loops=2,911)

  • Index Cond: ((object_id = s.object_id) AND (shareuser_id = s.parent_shareuser_id))
  • Filter: ((user_id = s.owner_id) AND lib.__permissionbitmask_has_permission(permissionbitmask, 'SHARE'::character varying))
9. 0.134 51.947 ↓ 0.0 0 1
→

Nested Loop (cost=61.46..52,313.81 rows=23,323 width=96) (actual time=51.947..51.947 rows=0 loops=1)

  • Join Filter: (NOT (ARRAY[(lower((par_pfi.pinnedobjectid)::text))::character varying] <@ pfi.parents))
  • Rows Removed by Join Filter: 3
10. 0.016 51.764 ↑ 3,348.6 7 1
→

Nested Loop (cost=61.03..31,752.15 rows=23,440 width=440) (actual time=10.485..51.764 rows=7 loops=1)

11. 0.031 15.901 ↑ 27.7 7 1
→

Nested Loop (cost=60.60..25,165.80 rows=194 width=432) (actual time=2.194..15.901 rows=7 loops=1)

  • Join Filter: (s_1.object_id = o_1.id)
12. 1.859 15.800 ↑ 430.0 7 1
→

Nested Loop (cost=60.17..21,881.87 rows=3,010 width=440) (actual time=2.162..15.800 rows=7 loops=1)

13. 1.788 2.189 ↑ 1.0 2,938 1
→

Bitmap Heap Scan on shares s_1 (cost=59.74..1,263.84 rows=3,010 width=34) (actual time=0.486..2.189 rows=2,938 loops=1)

  • Recheck Cond: (parent_shareuser_id IS NOT NULL)
  • Heap Blocks: exact=416
14. 0.401 0.401 ↑ 1.0 2,938 1
→

Bitmap Index Scan on ix_shares_parent_shareuser (cost=0.00..58.99 rows=3,010 width=0) (actual time=0.401..0.401 rows=2,938 loops=1)

  • Index Cond: (parent_shareuser_id IS NOT NULL)
15. 11.752 11.752 ↓ 0.0 0 2,938
→

Index Scan using pk_pinnedfiles on pinnedfiles pfi (cost=0.42..6.84 rows=1 width=406) (actual time=0.004..0.004 rows=0 loops=2,938)

  • Index Cond: (id = s_1.object_id)
16. 0.070 0.070 ↑ 1.0 1 7
→

Index Only Scan using pk_objects on objects o_1 (cost=0.43..1.08 rows=1 width=8) (actual time=0.009..0.010 rows=1 loops=7)

  • Index Cond: (id = pfi.id)
  • Heap Fetches: 7
17. 35.847 35.847 ↑ 226.0 1 7
→

Index Scan using ix_objectpermissions_shareuser on objectpermissions par_op (cost=0.43..31.69 rows=226 width=16) (actual time=5.120..5.121 rows=1 loops=7)

  • Index Cond: (shareuser_id = s_1.parent_shareuser_id)
18. 0.049 0.049 ↓ 0.0 0 7
→

Index Scan using pk_pinnedfiles on pinnedfiles par_pfi (cost=0.42..0.61 rows=1 width=112) (actual time=0.007..0.007 rows=0 loops=7)

  • Index Cond: (id = par_op.object_id)
19. 0.093 182.307 ↓ 0.0 0 1
→

Nested Loop (cost=2,135.88..10,742.45 rows=36 width=96) (actual time=182.307..182.307 rows=0 loops=1)

  • Join Filter: (NOT (ARRAY[(lower((par_pf.pinnedobjectid)::text))::character varying] <@ pfi_1.parents))
  • Rows Removed by Join Filter: 4
20. 0.024 182.123 ↑ 27.7 7 1
→

Nested Loop (cost=2,135.59..10,662.27 rows=194 width=440) (actual time=176.730..182.123 rows=7 loops=1)

21. 39.250 182.043 ↑ 27.7 7 1
→

Merge Join (cost=2,135.17..10,568.54 rows=194 width=440) (actual time=176.707..182.043 rows=7 loops=1)

  • Merge Cond: (s_2.parent_shareuser_id = par_su.id)
22. 0.034 12.453 ↑ 27.7 7 1
→

Nested Loop (cost=1.28..26,640.80 rows=194 width=432) (actual time=12.157..12.453 rows=7 loops=1)

  • Join Filter: (s_2.object_id = o_2.id)
23. 1.520 12.356 ↑ 430.0 7 1
→

Nested Loop (cost=0.84..23,356.87 rows=3,010 width=440) (actual time=12.125..12.356 rows=7 loops=1)

24. 2.022 2.022 ↑ 1.0 2,938 1
→

Index Scan using ix_shares_parent_shareuser on shares s_2 (cost=0.42..2,738.85 rows=3,010 width=34) (actual time=0.018..2.022 rows=2,938 loops=1)

  • Index Cond: (parent_shareuser_id IS NOT NULL)
25. 8.814 8.814 ↓ 0.0 0 2,938
→

Index Scan using pk_pinnedfiles on pinnedfiles pfi_1 (cost=0.42..6.84 rows=1 width=406) (actual time=0.003..0.003 rows=0 loops=2,938)

  • Index Cond: (id = s_2.object_id)
26. 0.063 0.063 ↑ 1.0 1 7
→

Index Only Scan using pk_objects on objects o_2 (cost=0.43..1.08 rows=1 width=8) (actual time=0.008..0.009 rows=1 loops=7)

  • Index Cond: (id = pfi_1.id)
  • Heap Fetches: 7
27. 130.340 130.340 ↑ 1.0 159,237 1
→

Index Scan using pk_shareusers on shareusers par_su (cost=0.42..9,482.51 rows=159,238 width=16) (actual time=0.032..130.340 rows=159,237 loops=1)

28. 0.056 0.056 ↑ 1.0 1 7
→

Index Scan using pk_shares on shares par_s (cost=0.42..0.47 rows=1 width=16) (actual time=0.008..0.008 rows=1 loops=7)

  • Index Cond: (id = par_su.share_id)
29. 0.091 0.091 ↑ 1.0 1 7
→

Index Scan using pk_pinnedfolders on pinnedfolders par_pf (cost=0.29..0.35 rows=1 width=81) (actual time=0.013..0.013 rows=1 loops=7)

  • Index Cond: (id = par_s.object_id)
30. 0.002 21.141 ↓ 0.0 0 1
→

Nested Loop (cost=1,303.03..6,226.75 rows=1 width=96) (actual time=21.141..21.141 rows=0 loops=1)

  • Join Filter: (s_3.object_id = o_3.id)
31. 0.140 21.139 ↓ 0.0 0 1
→

Nested Loop (cost=1,302.59..5,487.42 rows=103 width=42) (actual time=21.139..21.139 rows=0 loops=1)

  • Join Filter: (NOT (ARRAY[(lower((par_pf_1.pinnedobjectid)::text))::character varying] <@ pf.parents))
  • Rows Removed by Join Filter: 16
32. 0.032 20.904 ↑ 29.4 19 1
→

Nested Loop (cost=1,302.31..5,282.56 rows=559 width=320) (actual time=16.161..20.904 rows=19 loops=1)

33. 0.048 20.739 ↑ 29.4 19 1
→

Nested Loop (cost=1,301.89..5,012.48 rows=559 width=320) (actual time=16.148..20.739 rows=19 loops=1)

34. 5.405 20.558 ↑ 29.4 19 1
→

Hash Join (cost=1,301.47..3,183.75 rows=559 width=312) (actual time=16.117..20.558 rows=19 loops=1)

  • Hash Cond: (pf.id = s_3.object_id)
35. 11.470 11.470 ↑ 1.0 21,853 1
→

Seq Scan on pinnedfolders pf (cost=0.00..1,603.53 rows=21,853 width=278) (actual time=0.013..11.470 rows=21,853 loops=1)

36. 1.222 3.683 ↑ 1.0 2,938 1
→

Hash (cost=1,263.84..1,263.84 rows=3,010 width=34) (actual time=3.683..3.683 rows=2,938 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 239kB
37. 1.917 2.461 ↑ 1.0 2,938 1
→

Bitmap Heap Scan on shares s_3 (cost=59.74..1,263.84 rows=3,010 width=34) (actual time=0.632..2.461 rows=2,938 loops=1)

  • Recheck Cond: (parent_shareuser_id IS NOT NULL)
  • Heap Blocks: exact=416
38. 0.544 0.544 ↑ 1.0 2,938 1
→

Bitmap Index Scan on ix_shares_parent_shareuser (cost=0.00..58.99 rows=3,010 width=0) (actual time=0.544..0.544 rows=2,938 loops=1)

  • Index Cond: (parent_shareuser_id IS NOT NULL)
39. 0.133 0.133 ↑ 1.0 1 19
→

Index Scan using pk_shareusers on shareusers par_su_1 (cost=0.42..3.26 rows=1 width=16) (actual time=0.007..0.007 rows=1 loops=19)

  • Index Cond: (id = s_3.parent_shareuser_id)
40. 0.133 0.133 ↑ 1.0 1 19
→

Index Scan using pk_shares on shares par_s_1 (cost=0.42..0.47 rows=1 width=16) (actual time=0.007..0.007 rows=1 loops=19)

  • Index Cond: (id = par_su_1.share_id)
41. 0.095 0.095 ↑ 1.0 1 19
→

Index Scan using pk_pinnedfolders on pinnedfolders par_pf_1 (cost=0.29..0.35 rows=1 width=81) (actual time=0.004..0.005 rows=1 loops=19)

  • Index Cond: (id = par_s_1.object_id)
42. 0.000 0.000 ↓ 0.0 0
→

Index Only Scan using pk_objects on objects o_3 (cost=0.43..7.16 rows=1 width=8) (never executed)

  • Index Cond: (id = pf.id)
  • Heap Fetches: 0
43. 0.001 3.444 ↓ 0.0 0 1
→

Unique (cost=2,995.75..2,998.90 rows=210 width=96) (actual time=3.444..3.444 rows=0 loops=1)

44. 0.038 3.443 ↓ 0.0 0 1
→

Sort (cost=2,995.75..2,996.27 rows=210 width=96) (actual time=3.443..3.443 rows=0 loops=1)

  • Sort Key: s_4.owner_id, s_4.id, (lib.__dictionary_getname('share_type'::character varying, (s_4.sharetype_id)::integer)), s_4.object_id, s_4.parent_shareuser_id
  • Sort Method: quicksort Memory: 25kB
45. 0.030 3.405 ↓ 0.0 0 1
→

Nested Loop Anti Join (cost=67.05..2,987.65 rows=210 width=96) (actual time=3.405..3.405 rows=0 loops=1)

46. 0.023 2.725 ↑ 8.1 26 1
→

Nested Loop (cost=66.62..1,290.57 rows=211 width=42) (actual time=0.926..2.725 rows=26 loops=1)

  • Join Filter: (sl.id = sli.sharedlist_id)
47. 0.611 2.651 ↑ 5.0 1 1
→

Hash Join (cost=66.33..1,281.77 rows=5 width=42) (actual time=0.889..2.651 rows=1 loops=1)

  • Hash Cond: (s_4.object_id = sl.id)
48. 1.454 1.905 ↑ 1.0 2,938 1
→

Bitmap Heap Scan on shares s_4 (cost=59.74..1,263.84 rows=3,010 width=34) (actual time=0.538..1.905 rows=2,938 loops=1)

  • Recheck Cond: (parent_shareuser_id IS NOT NULL)
  • Heap Blocks: exact=416
49. 0.451 0.451 ↑ 1.0 2,938 1
→

Bitmap Index Scan on ix_shares_parent_shareuser (cost=0.00..58.99 rows=3,010 width=0) (actual time=0.451..0.451 rows=2,938 loops=1)

  • Index Cond: (parent_shareuser_id IS NOT NULL)
50. 0.065 0.135 ↑ 1.0 204 1
→

Hash (cost=4.04..4.04 rows=204 width=8) (actual time=0.135..0.135 rows=204 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
51. 0.070 0.070 ↑ 1.0 204 1
→

Seq Scan on sharedlists sl (cost=0.00..4.04 rows=204 width=8) (actual time=0.019..0.070 rows=204 loops=1)

52. 0.051 0.051 ↑ 1.7 26 1
→

Index Only Scan using ux_sharedlistitems_sharedlist_item on sharedlistitems sli (cost=0.29..1.21 rows=44 width=16) (actual time=0.027..0.051 rows=26 loops=1)

  • Index Cond: (sharedlist_id = s_4.object_id)
  • Heap Fetches: 26
53. 0.650 0.650 ↑ 1.0 1 26
→

Index Scan using ux_objectpermissions on objectpermissions op_1 (cost=0.43..7.78 rows=1 width=24) (actual time=0.025..0.025 rows=1 loops=26)

  • Index Cond: ((object_id = sli.item_id) AND (shareuser_id = s_4.parent_shareuser_id))
  • Filter: ((user_id = s_4.owner_id) AND lib.__permissionbitmask_has_permission(permissionbitmask, 'SHARE'::character varying))
54. 15.724 253.921 ↑ 821.3 3 1
→

Nested Loop (cost=2,135.60..23,562.75 rows=2,464 width=96) (actual time=168.339..253.921 rows=3 loops=1)

  • Join Filter: (ROW(o_4.type_id, par_o.type_id) <> ALL (ARRAY[ROW(1, 1), ROW(4, 4), ROW(1, 4), ROW(3, 3), ROW(5, 5), ROW(3, 5)]))
  • Rows Removed by Join Filter: 2934
55. 3.027 223.512 ↓ 1.2 2,937 1
→

Nested Loop (cost=2,135.17..11,772.02 rows=2,540 width=46) (actual time=12.042..223.512 rows=2,937 loops=1)

56. 41.126 205.800 ↓ 1.2 2,937 1
→

Merge Join (cost=2,134.75..10,544.80 rows=2,540 width=46) (actual time=12.020..205.800 rows=2,937 loops=1)

  • Merge Cond: (s_5.parent_shareuser_id = par_su_2.id)
57. 4.121 44.555 ↓ 1.2 2,937 1
→

Nested Loop (cost=0.85..27,590.02 rows=2,540 width=38) (actual time=0.049..44.555 rows=2,937 loops=1)

58. 2.240 2.240 ↑ 1.0 2,938 1
→

Index Scan using ix_shares_parent_shareuser on shares s_5 (cost=0.42..2,738.85 rows=3,010 width=34) (actual time=0.018..2.240 rows=2,938 loops=1)

  • Index Cond: (parent_shareuser_id IS NOT NULL)
59. 38.194 38.194 ↑ 1.0 1 2,938
→

Index Scan using pk_objects on objects o_4 (cost=0.43..8.25 rows=1 width=12) (actual time=0.013..0.013 rows=1 loops=2,938)

  • Index Cond: (id = s_5.object_id)
  • Filter: (type_id = ANY ('{1,4,3,5}'::integer[]))
  • Rows Removed by Filter: 0
60. 120.119 120.119 ↓ 1.0 161,979 1
→

Index Scan using pk_shareusers on shareusers par_su_2 (cost=0.42..9,482.51 rows=159,238 width=16) (actual time=0.016..120.119 rows=161,979 loops=1)

61. 14.685 14.685 ↑ 1.0 1 2,937
→

Index Scan using pk_shares on shares par_s_2 (cost=0.42..0.47 rows=1 width=16) (actual time=0.004..0.005 rows=1 loops=2,937)

  • Index Cond: (id = par_su_2.share_id)
62. 14.685 14.685 ↑ 1.0 1 2,937
→

Index Scan using pk_objects on objects par_o (cost=0.43..4.14 rows=1 width=12) (actual time=0.004..0.005 rows=1 loops=2,937)

  • Index Cond: (id = par_s_2.object_id)