explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bxeg

Settings
# exclusive inclusive rows x rows loops node
1. 0.060 459.899 ↑ 1,893.2 16 1
→

Sort (cost=177,391.64..177,467.37 rows=30,291 width=96) (actual time=459.896..459.899 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.016 459.839 ↑ 1,893.2 16 1
→

Append (cost=60.61..173,582.99 rows=30,291 width=96) (actual time=3.599..459.839 rows=16 loops=1)

3. 7.335 115.916 ↑ 215.5 13 1
→

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

4. 3.693 50.361 ↓ 1.0 2,911 1
→

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

5. 5.038 5.536 ↑ 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.595..5.536 rows=2,938 loops=1)

  • Recheck Cond: (parent_shareuser_id IS NOT NULL)
  • Heap Blocks: exact=416
6. 0.498 0.498 ↑ 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.498..0.498 rows=2,938 loops=1)

  • Index Cond: (parent_shareuser_id IS NOT NULL)
7. 41.132 41.132 ↑ 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.013..0.014 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. 58.220 58.220 ↑ 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.020..0.020 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.057 16.255 ↓ 0.0 0 1
→

Nested Loop (cost=61.46..52,313.81 rows=23,323 width=96) (actual time=16.255..16.255 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 16.149 ↑ 3,348.6 7 1
→

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

11. 0.023 16.042 ↑ 27.7 7 1
→

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

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

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

13. 2.643 3.079 ↑ 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.546..3.079 rows=2,938 loops=1)

  • Recheck Cond: (parent_shareuser_id IS NOT NULL)
  • Heap Blocks: exact=416
14. 0.436 0.436 ↑ 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.436..0.436 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.056 0.056 ↑ 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.008..0.008 rows=1 loops=7)

  • Index Cond: (id = pfi.id)
  • Heap Fetches: 7
17. 0.091 0.091 ↑ 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=0.013..0.013 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. 3.523 43.641 ↓ 0.0 0 1
→

Hash Join (cost=2,236.72..37,360.59 rows=1,457 width=96) (actual time=43.641..43.641 rows=0 loops=1)

  • Hash Cond: (par_op_1.object_id = par_pf.id)
  • Join Filter: (NOT (ARRAY[(lower((par_pf.pinnedobjectid)::text))::character varying] <@ pfi_1.parents))
  • Rows Removed by Join Filter: 4
20. 0.013 12.161 ↑ 3,348.6 7 1
→

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

21. 0.043 12.106 ↑ 27.7 7 1
→

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

  • Join Filter: (s_2.object_id = o_2.id)
22. 1.235 12.007 ↑ 430.0 7 1
→

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

23. 1.539 1.958 ↑ 1.0 2,938 1
→

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

  • Recheck Cond: (parent_shareuser_id IS NOT NULL)
  • Heap Blocks: exact=416
24. 0.419 0.419 ↑ 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.419..0.419 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.056 0.056 ↑ 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.007..0.008 rows=1 loops=7)

  • Index Cond: (id = pfi_1.id)
  • Heap Fetches: 7
27. 0.042 0.042 ↑ 226.0 1 7
→

Index Scan using ix_objectpermissions_shareuser on objectpermissions par_op_1 (cost=0.43..31.69 rows=226 width=16) (actual time=0.005..0.006 rows=1 loops=7)

  • Index Cond: (shareuser_id = s_2.parent_shareuser_id)
28. 13.188 27.957 ↑ 1.0 21,853 1
→

Hash (cost=1,603.53..1,603.53 rows=21,853 width=81) (actual time=27.957..27.957 rows=21,853 loops=1)

  • Buckets: 16384 Batches: 2 Memory Usage: 1348kB
29. 14.769 14.769 ↑ 1.0 21,853 1
→

Seq Scan on pinnedfolders par_pf (cost=0.00..1,603.53 rows=21,853 width=81) (actual time=0.018..14.769 rows=21,853 loops=1)

30. 0.152 15.409 ↓ 0.0 0 1
→

Nested Loop (cost=1,302.62..7,560.40 rows=36 width=96) (actual time=15.409..15.409 rows=0 loops=1)

  • Join Filter: (NOT (ARRAY[(lower((par_pf_1.pinnedobjectid)::text))::character varying] <@ pf.parents))
  • Rows Removed by Join Filter: 16
31. 0.098 14.852 ↑ 4.3 135 1
→

Nested Loop (cost=1,302.33..7,364.59 rows=578 width=312) (actual time=11.384..14.852 rows=135 loops=1)

32. 0.068 14.412 ↓ 3.8 19 1
→

Nested Loop (cost=1,301.90..7,194.84 rows=5 width=304) (actual time=11.328..14.412 rows=19 loops=1)

  • Join Filter: (s_3.object_id = o_3.id)
33. 4.939 14.173 ↑ 29.4 19 1
→

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

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

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

35. 1.205 3.256 ↑ 1.0 2,938 1
→

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

  • Buckets: 4096 Batches: 1 Memory Usage: 239kB
36. 1.635 2.051 ↑ 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.502..2.051 rows=2,938 loops=1)

  • Recheck Cond: (parent_shareuser_id IS NOT NULL)
  • Heap Blocks: exact=416
37. 0.416 0.416 ↑ 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.416..0.416 rows=2,938 loops=1)

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

Index Only Scan using pk_objects on objects o_3 (cost=0.43..7.16 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=19)

  • Index Cond: (id = pf.id)
  • Heap Fetches: 19
39. 0.342 0.342 ↑ 32.3 7 19
→

Index Scan using ix_objectpermissions_shareuser on objectpermissions par_op_2 (cost=0.43..31.69 rows=226 width=16) (actual time=0.010..0.018 rows=7 loops=19)

  • Index Cond: (shareuser_id = s_3.parent_shareuser_id)
40. 0.405 0.405 ↓ 0.0 0 135
→

Index Scan using pk_pinnedfolders on pinnedfolders par_pf_1 (cost=0.29..0.31 rows=1 width=81) (actual time=0.003..0.003 rows=0 loops=135)

  • Index Cond: (id = par_op_2.object_id)
41. 0.002 3.230 ↓ 0.0 0 1
→

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

42. 0.028 3.228 ↓ 0.0 0 1
→

Sort (cost=2,995.75..2,996.27 rows=210 width=96) (actual time=3.228..3.228 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
43. 0.031 3.200 ↓ 0.0 0 1
→

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

44. 0.029 2.597 ↑ 8.1 26 1
→

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

  • Join Filter: (sl.id = sli.sharedlist_id)
45. 0.661 2.522 ↑ 5.0 1 1
→

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

  • Hash Cond: (s_4.object_id = sl.id)
46. 1.297 1.723 ↑ 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.511..1.723 rows=2,938 loops=1)

  • Recheck Cond: (parent_shareuser_id IS NOT NULL)
  • Heap Blocks: exact=416
47. 0.426 0.426 ↑ 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.426..0.426 rows=2,938 loops=1)

  • Index Cond: (parent_shareuser_id IS NOT NULL)
48. 0.066 0.138 ↑ 1.0 204 1
→

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

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
49. 0.072 0.072 ↑ 1.0 204 1
→

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

50. 0.046 0.046 ↑ 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.026..0.046 rows=26 loops=1)

  • Index Cond: (sharedlist_id = s_4.object_id)
  • Heap Fetches: 26
51. 0.572 0.572 ↑ 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.022..0.022 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))
52. 14.780 265.372 ↑ 821.3 3 1
→

Nested Loop (cost=2,135.60..23,562.75 rows=2,464 width=96) (actual time=174.565..265.372 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
53. 3.097 235.907 ↓ 1.2 2,937 1
→

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

54. 42.292 218.125 ↓ 1.2 2,937 1
→

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

  • Merge Cond: (s_5.parent_shareuser_id = par_su.id)
55. 4.575 44.921 ↓ 1.2 2,937 1
→

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

56. 2.152 2.152 ↑ 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.017..2.152 rows=2,938 loops=1)

  • Index Cond: (parent_shareuser_id IS NOT NULL)
57. 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
58. 130.912 130.912 ↓ 1.0 161,979 1
→

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

59. 14.685 14.685 ↑ 1.0 1 2,937
→

Index Scan using pk_shares on shares par_s (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.share_id)
60. 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.object_id)