explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IQzH

Settings
# exclusive inclusive rows x rows loops node
1. 0.071 1,777.490 ↑ 409.3 16 1

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

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

3. 10.671 164.985 ↑ 215.5 13 1

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

4. 4.842 78.628 ↓ 1.0 2,911 1

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

5. 2.773 3.274 ↑ 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.611..3.274 rows=2,938 loops=1)

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

  • Index Cond: (parent_shareuser_id IS NOT NULL)
7. 70.512 70.512 ↑ 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.023..0.024 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. 75.686 75.686 ↑ 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.026..0.026 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.456 841.186 ↓ 0.0 0 1

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

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

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

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

12. 1.758 2.164 ↑ 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.491..2.164 rows=2,938 loops=1)

  • Recheck Cond: (parent_shareuser_id IS NOT NULL)
  • Heap Blocks: exact=416
13. 0.406 0.406 ↑ 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.406..0.406 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.412 508.290 ↑ 36.4 1,459 1

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

16. 4.333 507.878 ↑ 36.4 1,459 1

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

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

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

  • Hash Cond: (par_su.share_id = par_s.id)
18. 284.330 346.315 ↑ 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=62.668..346.315 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. 61.985 61.985 ↑ 1.0 51,220 1

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

20. 56.529 129.888 ↑ 1.0 124,548 1

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

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

22.          

SubPlan (forMerge Anti Join)

23. 317.220 317.220 ↑ 1.0 1 4

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

Merge Anti Join (cost=31,106.62..32,503.84 rows=554 width=96) (actual time=503.942..767.445 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.083 14.723 ↑ 29.4 19 1

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

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

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

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

28. 1.159 3.423 ↑ 1.0 2,938 1

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

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

  • Recheck Cond: (parent_shareuser_id IS NOT NULL)
  • Heap Blocks: exact=416
30. 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)
31. 1.208 419.091 ↑ 12.0 4,432 1

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

32. 5.281 417.883 ↑ 12.0 4,431 1

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

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

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

  • Hash Cond: (par_su_1.share_id = par_s_1.id)
34. 277.583 284.523 ↑ 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.418..284.523 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.940 6.940 ↑ 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.940..6.940 rows=51,220 loops=1)

36. 58.569 101.329 ↑ 1.0 124,548 1

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

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

38.          

SubPlan (forMerge Anti Join)

39. 331.232 331.232 ↑ 1.0 1 4

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

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

41. 0.028 3.784 ↓ 0.0 0 1

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

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

43. 0.043 3.015 ↑ 8.1 26 1

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

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

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

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

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

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

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

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

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

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