explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cP33

Settings
# exclusive inclusive rows x rows loops node
1. 0.017 1,562.330 ↑ 409.3 16 1

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

2. 7.257 127.951 ↑ 215.5 13 1

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

3. 3.975 56.652 ↓ 1.0 2,911 1

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

4. 2.322 2.731 ↑ 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.496..2.731 rows=2,938 loops=1)

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

  • Index Cond: (parent_shareuser_id IS NOT NULL)
6. 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.016..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
7. 64.042 64.042 ↑ 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.022..0.022 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))
8. 0.417 719.995 ↓ 0.0 0 1

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

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

  • Sort Key: s_1.owner_id, s_1.parent_shareuser_id
  • Sort Method: quicksort Memory: 31kB
10. 0.476 14.440 ↑ 430.0 7 1

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

11. 1.807 2.212 ↑ 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.490..2.212 rows=2,938 loops=1)

  • Recheck Cond: (parent_shareuser_id IS NOT NULL)
  • Heap Blocks: exact=416
12. 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)
13. 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)
14. 0.453 413.766 ↑ 36.4 1,459 1

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

15. 4.253 413.313 ↑ 36.4 1,459 1

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

  • Sort Key: par_su.user_id, par_su.id
  • Sort Method: quicksort Memory: 541kB
16. 25.233 409.060 ↑ 11.9 4,462 1

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

  • Hash Cond: (par_su.share_id = par_s.id)
17. 281.704 288.697 ↑ 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.449..288.697 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
18. 6.993 6.993 ↑ 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.993..6.993 rows=51,220 loops=1)

19. 52.466 95.130 ↑ 1.0 124,548 1

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

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

21.          

SubPlan (forMerge Anti Join)

22. 291.320 291.320 ↑ 1.0 1 4

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

Merge Anti Join (cost=31,106.62..32,503.84 rows=554 width=96) (actual time=492.167..710.913 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
24. 0.073 14.671 ↑ 29.4 19 1

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

  • Sort Key: s_2.owner_id, s_2.parent_shareuser_id
  • Sort Method: quicksort Memory: 35kB
25. 5.166 14.598 ↑ 29.4 19 1

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

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

27. 1.250 3.645 ↑ 1.0 2,938 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 239kB
28. 1.949 2.395 ↑ 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.534..2.395 rows=2,938 loops=1)

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

  • Index Cond: (parent_shareuser_id IS NOT NULL)
30. 1.088 407.564 ↑ 12.0 4,432 1

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

31. 5.067 406.476 ↑ 12.0 4,431 1

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

  • Sort Key: par_su_1.user_id, par_su_1.id
  • Sort Method: quicksort Memory: 541kB
32. 25.283 401.409 ↑ 11.9 4,462 1

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

  • Hash Cond: (par_su_1.share_id = par_s_1.id)
33. 270.103 276.707 ↑ 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.089..276.707 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
34. 6.604 6.604 ↑ 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.604..6.604 rows=51,220 loops=1)

35. 62.367 99.419 ↑ 1.0 124,548 1

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

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

37.          

SubPlan (forMerge Anti Join)

38. 286.484 286.484 ↑ 1.0 1 4

Seq Scan on pinnedfolders par_pf_1 (cost=0.00..1,985.96 rows=1 width=8) (actual time=63.951..71.621 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
39. 0.001 3.454 ↓ 0.0 0 1

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

40. 0.022 3.453 ↓ 0.0 0 1

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

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

42. 0.028 2.784 ↑ 8.1 26 1

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

  • Join Filter: (sl.id = sli.sharedlist_id)
43. 0.647 2.694 ↑ 5.0 1 1

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

  • Hash Cond: (s_3.object_id = sl.id)
44. 1.496 1.923 ↑ 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.515..1.923 rows=2,938 loops=1)

  • Recheck Cond: (parent_shareuser_id IS NOT NULL)
  • Heap Blocks: exact=416
45. 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)
46. 0.063 0.124 ↑ 1.0 204 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
47. 0.061 0.061 ↑ 1.0 204 1

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

48. 0.062 0.062 ↑ 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.041..0.062 rows=26 loops=1)

  • Index Cond: (sharedlist_id = s_3.object_id)
  • Heap Fetches: 26
49. 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_3.parent_shareuser_id))
  • Filter: ((user_id = s_3.owner_id) AND lib.__permissionbitmask_has_permission(permissionbitmask, 'SHARE'::character varying))