explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MlBM

Settings
# exclusive inclusive rows x rows loops node
1. 0.063 1,432.185 ↑ 409.3 16 1

Sort (cost=137,449.30..137,465.67 rows=6,549 width=96) (actual time=1,432.185..1,432.185 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.020 1,432.122 ↑ 409.3 16 1

Append (cost=60.61..137,034.19 rows=6,549 width=96) (actual time=3.451..1,432.122 rows=16 loops=1)

3. 8.866 120.548 ↑ 215.5 13 1

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

4. 3.850 53.462 ↓ 1.0 2,911 1

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

5. 2.176 2.604 ↑ 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..2.604 rows=2,938 loops=1)

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

  • Index Cond: (parent_shareuser_id IS NOT NULL)
7. 47.008 47.008 ↑ 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.015..0.016 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.559 724.915 ↓ 0.0 0 1

Merge Anti Join (cost=49,953.14..51,982.32 rows=2,984 width=96) (actual time=724.915..724.915 rows=0 loops=1)

  • Merge Cond: ((s_1.owner_id = par_su.user_id) AND (s_1.parent_shareuser_id = par_su.id))
  • Join Filter: ((par_s.object_id = s_1.object_id) OR (SubPlan 1))
10. 0.052 14.344 ↑ 430.0 7 1

Sort (cost=22,055.78..22,063.31 rows=3,010 width=536) (actual time=14.327..14.344 rows=7 loops=1)

  • Sort Key: s_1.owner_id, s_1.parent_shareuser_id
  • Sort Method: quicksort Memory: 31kB
11. 0.475 14.292 ↑ 430.0 7 1

Nested Loop (cost=60.17..21,881.87 rows=3,010 width=536) (actual time=2.012..14.292 rows=7 loops=1)

12. 1.677 2.065 ↑ 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.473..2.065 rows=2,938 loops=1)

  • Recheck Cond: (parent_shareuser_id IS NOT NULL)
  • Heap Blocks: exact=416
13. 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)
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=510) (actual time=0.004..0.004 rows=0 loops=2,938)

  • Index Cond: (id = s_1.object_id)
15. 0.423 407.468 ↑ 36.4 1,459 1

Materialize (cost=27,896.54..28,161.93 rows=53,079 width=24) (actual time=406.559..407.468 rows=1,459 loops=1)

16. 4.406 407.045 ↑ 36.4 1,459 1

Sort (cost=27,896.54..28,029.23 rows=53,079 width=24) (actual time=406.553..407.045 rows=1,459 loops=1)

  • Sort Key: par_su.user_id, par_su.id
  • Sort Method: quicksort Memory: 541kB
17. 26.545 402.639 ↑ 11.9 4,462 1

Hash Join (cost=5,422.98..22,638.94 rows=53,079 width=24) (actual time=100.427..402.639 rows=4,462 loops=1)

  • Hash Cond: (par_su.share_id = par_s.id)
18. 276.686 283.800 ↑ 11.9 4,462 1

Bitmap Heap Scan on shareusers par_su (cost=837.65..16,090.77 rows=53,079 width=24) (actual time=7.581..283.800 rows=4,462 loops=1)

  • Recheck Cond: (user_id IS NOT NULL)
  • Filter: lib.__permissionbitmask_has_permission(permissionbitmask, 'SHARE'::character varying)
  • Rows Removed by Filter: 46756
  • Heap Blocks: exact=1580
19. 7.114 7.114 ↑ 1.0 51,220 1

Bitmap Index Scan on ix_shareusers_contact (cost=0.00..824.38 rows=51,312 width=0) (actual time=7.114..7.114 rows=51,220 loops=1)

20. 51.032 92.294 ↑ 1.0 124,548 1

Hash (cost=2,419.48..2,419.48 rows=124,548 width=16) (actual time=92.294..92.294 rows=124,548 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 1984kB
21. 41.262 41.262 ↑ 1.0 124,548 1

Seq Scan on shares par_s (cost=0.00..2,419.48 rows=124,548 width=16) (actual time=0.022..41.262 rows=124,548 loops=1)

22.          

SubPlan (forMerge Anti Join)

23. 302.544 302.544 ↑ 1.0 1 4

Seq Scan on pinnedfolders par_pf (cost=0.00..1,985.96 rows=1 width=8) (actual time=75.636..75.636 rows=1 loops=4)

  • Filter: ((ARRAY[(lower((pinnedobjectid)::text))::character varying] <@ pfi.parents) AND ((pfi.pinnedobjectid)::text ~~ (replace(replace(replace((pinnedobjectid)::text, '\'::text, '\\'::text), '%'::text, '\%'::text), '_'::text, '\_'::text) || '%'::text)))
  • Rows Removed by Filter: 21365
24. 1.653 584.581 ↑ 184.7 3 1

Merge Anti Join (cost=31,106.62..32,503.84 rows=554 width=96) (actual time=460.035..584.581 rows=3 loops=1)

  • Merge Cond: ((s_2.owner_id = par_su_1.user_id) AND (s_2.parent_shareuser_id = par_su_1.id))
  • Join Filter: ((par_s_1.object_id = s_2.object_id) OR (SubPlan 2))
  • Rows Removed by Join Filter: 3
25. 0.086 15.371 ↑ 29.4 19 1

Sort (cost=3,209.26..3,210.66 rows=559 width=377) (actual time=15.355..15.371 rows=19 loops=1)

  • Sort Key: s_2.owner_id, s_2.parent_shareuser_id
  • Sort Method: quicksort Memory: 35kB
26. 5.004 15.285 ↑ 29.4 19 1

Hash Join (cost=1,301.47..3,183.75 rows=559 width=377) (actual time=12.400..15.285 rows=19 loops=1)

  • Hash Cond: (pf.id = s_2.object_id)
27. 6.163 6.163 ↑ 1.0 21,853 1

Seq Scan on pinnedfolders pf (cost=0.00..1,603.53 rows=21,853 width=351) (actual time=0.010..6.163 rows=21,853 loops=1)

28. 1.481 4.118 ↑ 1.0 2,938 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 239kB
29. 2.166 2.637 ↑ 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.610..2.637 rows=2,938 loops=1)

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

  • Index Cond: (parent_shareuser_id IS NOT NULL)
31. 0.671 391.933 ↑ 12.0 4,432 1

Materialize (cost=27,896.54..28,161.93 rows=53,079 width=24) (actual time=390.474..391.933 rows=4,432 loops=1)

32. 4.806 391.262 ↑ 12.0 4,431 1

Sort (cost=27,896.54..28,029.23 rows=53,079 width=24) (actual time=390.469..391.262 rows=4,431 loops=1)

  • Sort Key: par_su_1.user_id, par_su_1.id
  • Sort Method: quicksort Memory: 541kB
33. 28.922 386.456 ↑ 11.9 4,462 1

Hash Join (cost=5,422.98..22,638.94 rows=53,079 width=24) (actual time=95.578..386.456 rows=4,462 loops=1)

  • Hash Cond: (par_su_1.share_id = par_s_1.id)
34. 263.137 269.843 ↑ 11.9 4,462 1

Bitmap Heap Scan on shareusers par_su_1 (cost=837.65..16,090.77 rows=53,079 width=24) (actual time=7.209..269.843 rows=4,462 loops=1)

  • Recheck Cond: (user_id IS NOT NULL)
  • Filter: lib.__permissionbitmask_has_permission(permissionbitmask, 'SHARE'::character varying)
  • Rows Removed by Filter: 46756
  • Heap Blocks: exact=1580
35. 6.706 6.706 ↑ 1.0 51,220 1

Bitmap Index Scan on ix_shareusers_contact (cost=0.00..824.38 rows=51,312 width=0) (actual time=6.706..6.706 rows=51,220 loops=1)

36. 49.940 87.691 ↑ 1.0 124,548 1

Hash (cost=2,419.48..2,419.48 rows=124,548 width=16) (actual time=87.691..87.691 rows=124,548 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 1984kB
37. 37.751 37.751 ↑ 1.0 124,548 1

Seq Scan on shares par_s_1 (cost=0.00..2,419.48 rows=124,548 width=16) (actual time=0.023..37.751 rows=124,548 loops=1)

38.          

SubPlan (forMerge Anti Join)

39. 175.624 175.624 ↑ 1.0 1 4

Seq Scan on pinnedfolders par_pf_1 (cost=0.00..1,985.96 rows=1 width=8) (actual time=39.346..43.906 rows=1 loops=4)

  • Filter: ((ARRAY[(lower((pinnedobjectid)::text))::character varying] <@ pf.parents) AND ((pf.pinnedobjectid)::text ~~ (replace(replace(replace((pinnedobjectid)::text, '\'::text, '\\'::text), '%'::text, '\%'::text), '_'::text, '\_'::text) || '%'::text)))
  • Rows Removed by Filter: 21770
40. 0.001 2.058 ↓ 0.0 0 1

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

41. 0.023 2.057 ↓ 0.0 0 1

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

  • Sort Key: s_3.owner_id, s_3.id, (lib.__dictionary_getname('share_type'::character varying, (s_3.sharetype_id)::integer)), s_3.object_id, s_3.parent_shareuser_id
  • Sort Method: quicksort Memory: 25kB
42. 0.032 2.034 ↓ 0.0 0 1

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

43. 0.012 1.638 ↑ 8.1 26 1

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

  • Join Filter: (sl.id = sli.sharedlist_id)
44. 0.348 1.585 ↑ 5.0 1 1

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

  • Hash Cond: (s_3.object_id = sl.id)
45. 0.914 1.153 ↑ 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.286..1.153 rows=2,938 loops=1)

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

  • Index Cond: (parent_shareuser_id IS NOT NULL)
47. 0.042 0.084 ↑ 1.0 204 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
48. 0.042 0.042 ↑ 1.0 204 1

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

49. 0.041 0.041 ↑ 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.029..0.041 rows=26 loops=1)

  • Index Cond: (sharedlist_id = s_3.object_id)
  • Heap Fetches: 26
50. 0.364 0.364 ↑ 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.014..0.014 rows=1 loops=26)

  • Index Cond: ((object_id = sli.item_id) AND (shareuser_id = s_3.parent_shareuser_id))
  • Filter: ((user_id = s_3.owner_id) AND lib.__permissionbitmask_has_permission(permissionbitmask, 'SHARE'::character varying))