explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DvVR

Settings
# exclusive inclusive rows x rows loops node
1. 0.067 769.193 ↑ 297.6 16 1

Sort (cost=104,182.75..104,194.66 rows=4,762 width=96) (actual time=769.190..769.193 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.021 769.126 ↑ 297.6 16 1

Append (cost=60.61..103,891.86 rows=4,762 width=96) (actual time=3.067..769.126 rows=16 loops=1)

3. 8.820 119.196 ↑ 215.5 13 1

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

4. 5.209 49.245 ↓ 1.0 2,911 1

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

5. 2.401 2.904 ↑ 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.592..2.904 rows=2,938 loops=1)

  • Recheck Cond: (parent_shareuser_id IS NOT NULL)
  • Heap Blocks: exact=416
6. 0.503 0.503 ↑ 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.503..0.503 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. 61.131 61.131 ↑ 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.021..0.021 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.090 198.470 ↓ 0.0 0 1

Nested Loop (cost=2,136.02..11,246.46 rows=193 width=96) (actual time=198.470..198.470 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.030 198.324 ↑ 27.7 7 1

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

11. 40.903 198.161 ↑ 27.7 7 1

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

  • Merge Cond: (s_1.parent_shareuser_id = par_su.id)
12. 0.045 16.047 ↑ 27.7 7 1

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

  • Join Filter: (s_1.object_id = o_1.id)
13. 1.991 15.897 ↑ 430.0 7 1

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

14. 2.154 2.154 ↑ 1.0 2,938 1

Index Scan using ix_shares_parent_shareuser on shares s_1 (cost=0.42..2,738.85 rows=3,010 width=34) (actual time=0.018..2.154 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.105 0.105 ↑ 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.014..0.015 rows=1 loops=7)

  • Index Cond: (id = pfi.id)
  • Heap Fetches: 7
17. 141.211 141.211 ↑ 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.030..141.211 rows=159,237 loops=1)

18. 0.133 0.133 ↑ 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.018..0.019 rows=1 loops=7)

  • Index Cond: (id = par_su.share_id)
19. 0.056 0.056 ↓ 0.0 0 7

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

  • Index Cond: (id = par_s.object_id)
20. 0.091 183.302 ↓ 0.0 0 1

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

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

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

22. 37.784 183.043 ↑ 27.7 7 1

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

  • Merge Cond: (s_2.parent_shareuser_id = par_su_1.id)
23. 0.032 19.675 ↑ 27.7 7 1

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

  • Join Filter: (s_2.object_id = o_2.id)
24. 1.997 19.580 ↑ 430.0 7 1

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

25. 2.893 2.893 ↑ 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.031..2.893 rows=2,938 loops=1)

  • Index Cond: (parent_shareuser_id IS NOT NULL)
26. 14.690 14.690 ↓ 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.005..0.005 rows=0 loops=2,938)

  • Index Cond: (id = s_2.object_id)
27. 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
28. 125.584 125.584 ↑ 1.0 159,237 1

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

29. 0.056 0.056 ↑ 1.0 1 7

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

  • Index Cond: (id = par_su_1.share_id)
30. 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_1.object_id)
31. 0.001 21.212 ↓ 0.0 0 1

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

  • Join Filter: (s_3.object_id = o_3.id)
32. 0.188 21.211 ↓ 0.0 0 1

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

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

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

34. 0.029 20.737 ↑ 29.4 19 1

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

35. 5.332 20.556 ↑ 29.4 19 1

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

  • Hash Cond: (pf.id = s_3.object_id)
36. 11.717 11.717 ↑ 1.0 21,853 1

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

37. 1.145 3.507 ↑ 1.0 2,938 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 239kB
38. 1.926 2.362 ↑ 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.527..2.362 rows=2,938 loops=1)

  • Recheck Cond: (parent_shareuser_id IS NOT NULL)
  • Heap Blocks: exact=416
39. 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)
40. 0.152 0.152 ↑ 1.0 1 19

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

  • Index Cond: (id = s_3.parent_shareuser_id)
41. 0.171 0.171 ↑ 1.0 1 19

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

  • Index Cond: (id = par_su_2.share_id)
42. 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.005..0.005 rows=1 loops=19)

  • Index Cond: (id = par_s_2.object_id)
43. 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
44. 0.001 3.455 ↓ 0.0 0 1

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

45. 0.039 3.454 ↓ 0.0 0 1

Sort (cost=2,995.75..2,996.27 rows=210 width=96) (actual time=3.454..3.454 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
46. 0.019 3.415 ↓ 0.0 0 1

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

47. 0.029 2.798 ↑ 8.1 26 1

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

  • Join Filter: (sl.id = sli.sharedlist_id)
48. 0.675 2.718 ↑ 5.0 1 1

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

  • Hash Cond: (s_4.object_id = sl.id)
49. 1.486 1.906 ↑ 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.541..1.906 rows=2,938 loops=1)

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

  • Index Cond: (parent_shareuser_id IS NOT NULL)
51. 0.065 0.137 ↑ 1.0 204 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
52. 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)

53. 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.030..0.051 rows=26 loops=1)

  • Index Cond: (sharedlist_id = s_4.object_id)
  • Heap Fetches: 26
54. 0.598 0.598 ↑ 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.023..0.023 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))
55. 4.717 243.470 ↑ 507.0 3 1

Nested Loop (cost=2,135.60..23,146.04 rows=1,521 width=96) (actual time=156.787..243.470 rows=3 loops=1)

  • Join Filter: (((o_4.type_id <> 1) OR (par_o.type_id <> 1)) AND ((o_4.type_id <> 4) OR (par_o.type_id <> 4)) AND ((o_4.type_id <> 1) OR (par_o.type_id <> 4)) AND ((o_4.type_id <> 3) OR (par_o.type_id <> 3)) AND ((o_4.type_id <> 5) OR (par_o.type_id <> 5)) AND ((o_4.type_id <> 3) OR (par_o.type_id <> 5)))
  • Rows Removed by Join Filter: 2934
56. 2.551 224.068 ↓ 1.2 2,937 1

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

57. 40.102 206.832 ↓ 1.2 2,937 1

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

  • Merge Cond: (s_5.parent_shareuser_id = par_su_3.id)
58. 3.014 43.409 ↓ 1.2 2,937 1

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

59. 2.201 2.201 ↑ 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.201 rows=2,938 loops=1)

  • Index Cond: (parent_shareuser_id IS NOT NULL)
60. 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.012..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
61. 123.321 123.321 ↓ 1.0 161,979 1

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

62. 14.685 14.685 ↑ 1.0 1 2,937

Index Scan using pk_shares on shares par_s_3 (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_3.share_id)
63. 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_3.object_id)