explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ENbL

Settings
# exclusive inclusive rows x rows loops node
1. 0.071 818.115 ↑ 570.6 16 1

Sort (cost=116,380.79..116,403.61 rows=9,129 width=96) (actual time=818.113..818.115 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.015 818.044 ↑ 570.6 16 1

Append (cost=60.61..115,780.27 rows=9,129 width=96) (actual time=5.215..818.044 rows=16 loops=1)

3. 12.238 155.655 ↑ 215.5 13 1

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

4. 5.311 64.820 ↓ 1.0 2,911 1

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

5. 2.985 3.687 ↑ 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.815..3.687 rows=2,938 loops=1)

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

  • Index Cond: (parent_shareuser_id IS NOT NULL)
7. 55.822 55.822 ↑ 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.018..0.019 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. 78.597 78.597 ↑ 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.027..0.027 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.021 176.527 ↓ 0.0 0 1

Nested Loop (cost=2,135.58..20,974.77 rows=2,995 width=96) (actual time=176.527..176.527 rows=0 loops=1)

  • Join Filter: ((par_pfi.id <> s_1.object_id) AND (NOT (ARRAY[(lower((par_pfi.pinnedobjectid)::text))::character varying] <@ pfi.parents)))
  • Rows Removed by Join Filter: 3
10. 0.018 176.478 ↑ 430.0 7 1

Nested Loop (cost=2,135.16..11,903.11 rows=3,010 width=440) (actual time=171.037..176.478 rows=7 loops=1)

11. 38.441 176.404 ↑ 430.0 7 1

Merge Join (cost=2,134.74..10,448.81 rows=3,010 width=440) (actual time=171.015..176.404 rows=7 loops=1)

  • Merge Cond: (s_1.parent_shareuser_id = par_su.id)
12. 1.644 15.757 ↑ 430.0 7 1

Nested Loop (cost=0.84..23,356.87 rows=3,010 width=432) (actual time=15.360..15.757 rows=7 loops=1)

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

  • Index Cond: (parent_shareuser_id IS NOT NULL)
14. 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)
15. 122.206 122.206 ↑ 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.028..122.206 rows=159,237 loops=1)

16. 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)
17. 0.028 0.028 ↓ 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.004..0.004 rows=0 loops=7)

  • Index Cond: (id = par_s.object_id)
18. 0.070 175.751 ↓ 0.0 0 1

Nested Loop (cost=2,135.44..13,154.10 rows=556 width=96) (actual time=175.751..175.751 rows=0 loops=1)

  • Join Filter: ((par_pf.id <> s_2.object_id) AND (NOT (ARRAY[(lower((par_pf.pinnedobjectid)::text))::character varying] <@ pfi_1.parents)))
  • Rows Removed by Join Filter: 4
19. 0.026 175.569 ↑ 430.0 7 1

Nested Loop (cost=2,135.16..11,903.11 rows=3,010 width=440) (actual time=170.185..175.569 rows=7 loops=1)

20. 38.762 175.494 ↑ 430.0 7 1

Merge Join (cost=2,134.74..10,448.81 rows=3,010 width=440) (actual time=170.160..175.494 rows=7 loops=1)

  • Merge Cond: (s_2.parent_shareuser_id = par_su_1.id)
21. 1.339 12.015 ↑ 430.0 7 1

Nested Loop (cost=0.84..23,356.87 rows=3,010 width=432) (actual time=11.763..12.015 rows=7 loops=1)

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

  • Index Cond: (parent_shareuser_id IS NOT NULL)
23. 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)
24. 124.717 124.717 ↑ 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.016..124.717 rows=159,237 loops=1)

25. 0.049 0.049 ↑ 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.007 rows=1 loops=7)

  • Index Cond: (id = par_su_1.share_id)
26. 0.112 0.112 ↑ 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.015..0.016 rows=1 loops=7)

  • Index Cond: (id = par_s_1.object_id)
27. 0.050 21.539 ↓ 0.0 0 1

Nested Loop (cost=1,302.59..5,514.82 rows=103 width=96) (actual time=21.539..21.539 rows=0 loops=1)

  • Join Filter: ((par_pf_1.id <> s_3.object_id) AND (NOT (ARRAY[(lower((par_pf_1.pinnedobjectid)::text))::character varying] <@ pf.parents)))
  • Rows Removed by Join Filter: 16
28. 0.034 21.394 ↑ 29.4 19 1

Nested Loop (cost=1,302.31..5,282.56 rows=559 width=312) (actual time=16.426..21.394 rows=19 loops=1)

29. 0.030 21.208 ↑ 29.4 19 1

Nested Loop (cost=1,301.89..5,012.48 rows=559 width=312) (actual time=16.412..21.208 rows=19 loops=1)

30. 5.498 21.045 ↑ 29.4 19 1

Hash Join (cost=1,301.47..3,183.75 rows=559 width=304) (actual time=16.383..21.045 rows=19 loops=1)

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

32. 1.031 3.395 ↑ 1.0 2,938 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 239kB
33. 1.976 2.364 ↑ 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.493..2.364 rows=2,938 loops=1)

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

  • Index Cond: (parent_shareuser_id IS NOT NULL)
35. 0.133 0.133 ↑ 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.007 rows=1 loops=19)

  • Index Cond: (id = s_3.parent_shareuser_id)
36. 0.152 0.152 ↑ 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.007..0.008 rows=1 loops=19)

  • Index Cond: (id = par_su_2.share_id)
37. 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)
38. 0.001 3.494 ↓ 0.0 0 1

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

39. 0.031 3.493 ↓ 0.0 0 1

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

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

41. 0.031 2.804 ↑ 8.1 26 1

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

  • Join Filter: (sl.id = sli.sharedlist_id)
42. 0.716 2.703 ↑ 5.0 1 1

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

  • Hash Cond: (s_4.object_id = sl.id)
43. 1.406 1.851 ↑ 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.531..1.851 rows=2,938 loops=1)

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

  • Index Cond: (parent_shareuser_id IS NOT NULL)
45. 0.063 0.136 ↑ 1.0 204 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
46. 0.073 0.073 ↑ 1.0 204 1

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

47. 0.070 0.070 ↑ 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.070 rows=26 loops=1)

  • Index Cond: (sharedlist_id = s_4.object_id)
  • Heap Fetches: 26
48. 0.624 0.624 ↑ 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.024..0.024 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))
49. 16.953 285.063 ↑ 821.3 3 1

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

  • Join Filter: (ROW(o_1.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
50. 4.475 253.425 ↓ 1.2 2,937 1

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

51. 45.776 234.265 ↓ 1.2 2,937 1

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

  • Merge Cond: (s_5.parent_shareuser_id = par_su_3.id)
52. 4.010 50.522 ↓ 1.2 2,937 1

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

53. 2.442 2.442 ↑ 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.016..2.442 rows=2,938 loops=1)

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

Index Scan using pk_objects on objects o_1 (cost=0.43..8.25 rows=1 width=12) (actual time=0.015..0.015 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
55. 137.967 137.967 ↓ 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..137.967 rows=161,979 loops=1)

56. 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.005..0.005 rows=1 loops=2,937)

  • Index Cond: (id = par_su_3.share_id)
57. 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)