explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GJon

Settings
# exclusive inclusive rows x rows loops node
1. 0.436 812.013 ↓ 0.0 0 1

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

  • Merge Cond: ((s.owner_id = par_su.user_id) AND (s.parent_shareuser_id = par_su.id))
  • Join Filter: ((par_s.object_id = s.object_id) OR (SubPlan 1))
2. 0.045 14.676 ↑ 430.0 7 1

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

  • Sort Key: s.owner_id, s.parent_shareuser_id
  • Sort Method: quicksort Memory: 31kB
3. 0.651 14.631 ↑ 430.0 7 1

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

4. 1.823 2.228 ↑ 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.513..2.228 rows=2,938 loops=1)

  • Recheck Cond: (parent_shareuser_id IS NOT NULL)
  • Heap Blocks: exact=416
5. 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)
6. 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.object_id)
7. 0.433 434.089 ↑ 36.4 1,459 1

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

8. 4.347 433.656 ↑ 36.4 1,459 1

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

  • Sort Key: par_su.user_id, par_su.id
  • Sort Method: quicksort Memory: 541kB
9. 26.321 429.309 ↑ 11.9 4,462 1

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

  • Hash Cond: (par_su.share_id = par_s.id)
10. 297.173 304.998 ↑ 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=8.324..304.998 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
11. 7.825 7.825 ↑ 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.825..7.825 rows=51,220 loops=1)

12. 54.292 97.990 ↑ 1.0 124,548 1

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

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

14.          

SubPlan (forMerge Anti Join)

15. 362.812 362.812 ↑ 1.0 1 4

Seq Scan on pinnedfolders par_pf (cost=0.00..1,985.96 rows=1 width=8) (actual time=90.703..90.703 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