explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nlbV

Settings
# exclusive inclusive rows x rows loops node
1. 0.056 1,486.023 ↑ 409.3 16 1

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

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

3. 9.044 136.050 ↑ 215.5 13 1

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

4. 7.024 60.053 ↓ 1.0 2,911 1

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

5. 2.593 3.083 ↑ 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.618..3.083 rows=2,938 loops=1)

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

  • Index Cond: (parent_shareuser_id IS NOT NULL)
7. 49.946 49.946 ↑ 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.017..0.017 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. 66.953 66.953 ↑ 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.023..0.023 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.427 719.818 ↓ 0.0 0 1

Merge Anti Join (cost=49,953.14..51,982.32 rows=2,984 width=96) (actual time=719.818..719.818 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.049 13.096 ↑ 430.0 7 1

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

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

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

12. 1.618 2.001 ↑ 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.467..2.001 rows=2,938 loops=1)

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

  • Index Cond: (parent_shareuser_id IS NOT NULL)
14. 8.814 8.814 ↓ 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.003..0.003 rows=0 loops=2,938)

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

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

16. 4.294 396.818 ↑ 36.4 1,459 1

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

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

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

  • Hash Cond: (par_su.share_id = par_s.id)
18. 270.088 277.187 ↑ 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.548..277.187 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.099 7.099 ↑ 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.099..7.099 rows=51,220 loops=1)

20. 48.944 88.268 ↑ 1.0 124,548 1

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

  • Buckets: 65536 Batches: 4 Memory Usage: 1984kB
21. 39.324 39.324 ↑ 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.024..39.324 rows=124,548 loops=1)

22.          

SubPlan (forMerge Anti Join)

23. 309.060 309.060 ↑ 1.0 1 4

Seq Scan on pinnedfolders par_pf (cost=0.00..1,985.96 rows=1 width=8) (actual time=77.265..77.265 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.743 627.723 ↑ 184.7 3 1

Merge Anti Join (cost=31,106.62..32,503.84 rows=554 width=96) (actual time=477.724..627.723 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.102 16.426 ↑ 29.4 19 1

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

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

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

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

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

28. 1.186 3.392 ↑ 1.0 2,938 1

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

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

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

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

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

32. 4.666 388.639 ↑ 12.0 4,431 1

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

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

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

  • Hash Cond: (par_su_1.share_id = par_s_1.id)
34. 263.885 270.594 ↑ 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.163..270.594 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.709 6.709 ↑ 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.709..6.709 rows=51,220 loops=1)

36. 50.289 87.898 ↑ 1.0 124,548 1

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

  • Buckets: 65536 Batches: 4 Memory Usage: 1984kB
37. 37.609 37.609 ↑ 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.016..37.609 rows=124,548 loops=1)

38.          

SubPlan (forMerge Anti Join)

39. 220.168 220.168 ↑ 1.0 1 4

Seq Scan on pinnedfolders par_pf_1 (cost=0.00..1,985.96 rows=1 width=8) (actual time=49.949..55.042 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.355 ↓ 0.0 0 1

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

41. 0.024 2.354 ↓ 0.0 0 1

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

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

43. 0.018 1.851 ↑ 8.1 26 1

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

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

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

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

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

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

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

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

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

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

  • Index Cond: (sharedlist_id = s_3.object_id)
  • Heap Fetches: 26
50. 0.442 0.442 ↑ 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.017..0.017 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))