explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cVcu

Settings
# exclusive inclusive rows x rows loops node
1. 0.060 1,950.004 ↑ 3,281.6 16 1

Sort (cost=308,901.62..309,032.89 rows=52,506 width=96) (actual time=1,949.999..1,950.004 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.019 1,949.944 ↑ 3,281.6 16 1

Append (cost=60.61..302,090.10 rows=52,506 width=96) (actual time=2.973..1,949.944 rows=16 loops=1)

3. 9.461 118.549 ↑ 215.5 13 1

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

4. 6.353 50.868 ↓ 1.0 2,911 1

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

5. 2.956 3.383 ↑ 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.516..3.383 rows=2,938 loops=1)

  • Recheck Cond: (parent_shareuser_id IS NOT NULL)
  • Heap Blocks: exact=416
6. 0.427 0.427 ↑ 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.427..0.427 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.014..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.054 15.117 ↓ 0.0 0 1

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

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

11. 0.022 14.928 ↑ 27.7 7 1

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

  • Join Filter: (s_1.object_id = o_1.id)
12. 0.852 14.836 ↑ 430.0 7 1

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

13. 1.827 2.232 ↑ 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.494..2.232 rows=2,938 loops=1)

  • Recheck Cond: (parent_shareuser_id IS NOT NULL)
  • Heap Blocks: exact=416
14. 0.405 0.405 ↑ 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.405..0.405 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.010..0.010 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.028 0.028 ↓ 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.004..0.004 rows=0 loops=7)

  • Index Cond: (id = par_op.object_id)
19. 3.513 42.867 ↓ 0.0 0 1

Hash Join (cost=2,236.72..37,360.59 rows=1,457 width=96) (actual time=42.867..42.867 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.028 12.497 ↑ 3,348.6 7 1

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

21. 0.021 12.427 ↑ 27.7 7 1

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

  • Join Filter: (s_2.object_id = o_2.id)
22. 1.528 12.357 ↑ 430.0 7 1

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

23. 1.636 2.015 ↑ 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.462..2.015 rows=2,938 loops=1)

  • Recheck Cond: (parent_shareuser_id IS NOT NULL)
  • Heap Blocks: exact=416
24. 0.379 0.379 ↑ 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.379..0.379 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.049 0.049 ↑ 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.007 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. 12.248 26.857 ↑ 1.0 21,853 1

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

  • Buckets: 16384 Batches: 2 Memory Usage: 1348kB
29. 14.609 14.609 ↑ 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.020..14.609 rows=21,853 loops=1)

30. 0.140 15.635 ↓ 0.0 0 1

Nested Loop (cost=1,302.62..7,560.40 rows=36 width=96) (actual time=15.635..15.635 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.075 15.090 ↑ 4.3 135 1

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

32. 0.046 14.654 ↓ 3.8 19 1

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

  • Join Filter: (s_3.object_id = o_3.id)
33. 5.023 14.456 ↑ 29.4 19 1

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

  • Hash Cond: (pf.id = s_3.object_id)
34. 6.004 6.004 ↑ 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..6.004 rows=21,853 loops=1)

35. 1.186 3.429 ↑ 1.0 2,938 1

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

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

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

  • Index Cond: (parent_shareuser_id IS NOT NULL)
38. 0.152 0.152 ↑ 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.008..0.008 rows=1 loops=19)

  • Index Cond: (id = pf.id)
  • Heap Fetches: 19
39. 0.361 0.361 ↑ 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.011..0.019 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.266 ↓ 0.0 0 1

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

42. 0.019 3.264 ↓ 0.0 0 1

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

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

44. 0.018 2.603 ↑ 8.1 26 1

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

  • Join Filter: (sl.id = sli.sharedlist_id)
45. 0.714 2.532 ↑ 5.0 1 1

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

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

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

  • Index Cond: (parent_shareuser_id IS NOT NULL)
48. 0.049 0.119 ↑ 1.0 204 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
49. 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.016..0.070 rows=204 loops=1)

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

  • Index Cond: (sharedlist_id = s_4.object_id)
  • Heap Fetches: 26
51. 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))
52. 16.179 1,754.491 ↑ 8,226.3 3 1

Nested Loop (cost=44,413.80..151,847.71 rows=24,679 width=96) (actual time=1,686.458..1,754.491 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. 37.273 1,723.627 ↑ 8.7 2,937 1

Merge Join (cost=44,413.36..46,264.06 rows=25,442 width=46) (actual time=1,560.295..1,723.627 rows=2,937 loops=1)

  • Merge Cond: (s_5.parent_shareuser_id = par_op_3.shareuser_id)
54. 4.458 45.701 ↓ 1.2 2,937 1

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

55. 3.049 3.049 ↑ 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..3.049 rows=2,938 loops=1)

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

Materialize (cost=44,157.44..44,949.85 rows=158,483 width=16) (actual time=1,551.321..1,640.653 rows=161,979 loops=1)

58. 262.670 1,599.864 ↓ 1.0 159,236 1

Sort (cost=44,157.44..44,553.65 rows=158,483 width=16) (actual time=1,551.317..1,599.864 rows=159,236 loops=1)

  • Sort Key: par_op_3.shareuser_id
  • Sort Method: external merge Disk: 4048kB
59. 1,288.600 1,337.194 ↓ 1.0 159,238 1

Bitmap Heap Scan on objectpermissions par_op_3 (cost=2,592.46..27,760.29 rows=158,483 width=16) (actual time=51.325..1,337.194 rows=159,238 loops=1)

  • Recheck Cond: isdirect
  • Heap Blocks: exact=8857
60. 48.594 48.594 ↓ 1.0 159,238 1

Bitmap Index Scan on ix_objectpermissions_user_id_isdirect (cost=0.00..2,552.84 rows=158,483 width=0) (actual time=48.594..48.594 rows=159,238 loops=1)

61. 14.685 14.685 ↑ 1.0 1 2,937

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

  • Index Cond: (id = par_op_3.object_id)