explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yvkA

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 423.601 ↓ 0.0 0 1

Append (cost=2,136.02..28,217.96 rows=230 width=96) (actual time=423.601..423.601 rows=0 loops=1)

2. 0.087 165.409 ↓ 0.0 0 1

Nested Loop (cost=2,136.02..11,246.46 rows=193 width=96) (actual time=165.409..165.409 rows=0 loops=1)

  • Join Filter: (NOT (ARRAY[(lower((par_pfi.pinnedobjectid)::text))::character varying] <@ pfi.parents))
  • Rows Removed by Join Filter: 3
3. 0.028 165.280 ↑ 27.7 7 1

Nested Loop (cost=2,135.59..10,662.27 rows=194 width=440) (actual time=160.206..165.280 rows=7 loops=1)

4. 37.543 165.196 ↑ 27.7 7 1

Merge Join (cost=2,135.17..10,568.54 rows=194 width=440) (actual time=160.184..165.196 rows=7 loops=1)

  • Merge Cond: (s.parent_shareuser_id = par_su.id)
5. 0.025 13.418 ↑ 27.7 7 1

Nested Loop (cost=1.28..26,640.80 rows=194 width=432) (actual time=13.092..13.418 rows=7 loops=1)

  • Join Filter: (s.object_id = o.id)
6. 2.460 13.330 ↑ 430.0 7 1

Nested Loop (cost=0.84..23,356.87 rows=3,010 width=440) (actual time=13.076..13.330 rows=7 loops=1)

7. 2.056 2.056 ↑ 1.0 2,938 1

Index Scan using ix_shares_parent_shareuser on shares s (cost=0.42..2,738.85 rows=3,010 width=34) (actual time=0.020..2.056 rows=2,938 loops=1)

  • Index Cond: (parent_shareuser_id IS NOT NULL)
8. 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=406) (actual time=0.003..0.003 rows=0 loops=2,938)

  • Index Cond: (id = s.object_id)
9. 0.063 0.063 ↑ 1.0 1 7

Index Only Scan using pk_objects on objects o (cost=0.43..1.08 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=7)

  • Index Cond: (id = pfi.id)
  • Heap Fetches: 7
10. 114.235 114.235 ↑ 1.0 159,237 1

Index Scan using pk_shareusers on shareusers par_su (cost=0.42..9,482.51 rows=159,238 width=16) (actual time=0.014..114.235 rows=159,237 loops=1)

11. 0.056 0.056 ↑ 1.0 1 7

Index Scan using pk_shares on shares par_s (cost=0.42..0.47 rows=1 width=16) (actual time=0.007..0.008 rows=1 loops=7)

  • Index Cond: (id = par_su.share_id)
12. 0.042 0.042 ↓ 0.0 0 7

Index Scan using pk_pinnedfiles on pinnedfiles par_pfi (cost=0.42..2.75 rows=1 width=112) (actual time=0.005..0.006 rows=0 loops=7)

  • Index Cond: (id = par_s.object_id)
13. 0.102 176.939 ↓ 0.0 0 1

Nested Loop (cost=2,135.88..10,742.45 rows=36 width=96) (actual time=176.939..176.939 rows=0 loops=1)

  • Join Filter: (NOT (ARRAY[(lower((par_pf.pinnedobjectid)::text))::character varying] <@ pfi_1.parents))
  • Rows Removed by Join Filter: 4
14. 0.020 176.802 ↑ 27.7 7 1

Nested Loop (cost=2,135.59..10,662.27 rows=194 width=440) (actual time=171.776..176.802 rows=7 loops=1)

15. 39.633 176.733 ↑ 27.7 7 1

Merge Join (cost=2,135.17..10,568.54 rows=194 width=440) (actual time=171.752..176.733 rows=7 loops=1)

  • Merge Cond: (s_1.parent_shareuser_id = par_su_1.id)
16. 0.020 12.783 ↑ 27.7 7 1

Nested Loop (cost=1.28..26,640.80 rows=194 width=432) (actual time=12.498..12.783 rows=7 loops=1)

  • Join Filter: (s_1.object_id = o_1.id)
17. 1.949 12.707 ↑ 430.0 7 1

Nested Loop (cost=0.84..23,356.87 rows=3,010 width=440) (actual time=12.485..12.707 rows=7 loops=1)

18. 1.944 1.944 ↑ 1.0 2,938 1

Index Scan using ix_shares_parent_shareuser on shares s_1 (cost=0.42..2,738.85 rows=3,010 width=34) (actual time=0.095..1.944 rows=2,938 loops=1)

  • Index Cond: (parent_shareuser_id IS NOT NULL)
19. 8.814 8.814 ↓ 0.0 0 2,938

Index Scan using pk_pinnedfiles on pinnedfiles pfi_1 (cost=0.42..6.84 rows=1 width=406) (actual time=0.003..0.003 rows=0 loops=2,938)

  • Index Cond: (id = s_1.object_id)
20. 0.056 0.056 ↑ 1.0 1 7

Index Only Scan using pk_objects on objects o_1 (cost=0.43..1.08 rows=1 width=8) (actual time=0.007..0.008 rows=1 loops=7)

  • Index Cond: (id = pfi_1.id)
  • Heap Fetches: 7
21. 124.317 124.317 ↑ 1.0 159,237 1

Index Scan using pk_shareusers on shareusers par_su_1 (cost=0.42..9,482.51 rows=159,238 width=16) (actual time=0.016..124.317 rows=159,237 loops=1)

22. 0.049 0.049 ↑ 1.0 1 7

Index Scan using pk_shares on shares par_s_1 (cost=0.42..0.47 rows=1 width=16) (actual time=0.006..0.007 rows=1 loops=7)

  • Index Cond: (id = par_su_1.share_id)
23. 0.035 0.035 ↑ 1.0 1 7

Index Scan using pk_pinnedfolders on pinnedfolders par_pf (cost=0.29..0.35 rows=1 width=81) (actual time=0.004..0.005 rows=1 loops=7)

  • Index Cond: (id = par_s_1.object_id)
24. 0.001 81.251 ↓ 0.0 0 1

Nested Loop (cost=1,303.03..6,226.75 rows=1 width=96) (actual time=81.251..81.251 rows=0 loops=1)

  • Join Filter: (s_2.object_id = o_2.id)
25. 0.189 81.250 ↓ 0.0 0 1

Nested Loop (cost=1,302.59..5,487.42 rows=103 width=42) (actual time=81.250..81.250 rows=0 loops=1)

  • Join Filter: (NOT (ARRAY[(lower((par_pf_1.pinnedobjectid)::text))::character varying] <@ pf.parents))
  • Rows Removed by Join Filter: 16
26. 0.041 60.275 ↑ 29.4 19 1

Nested Loop (cost=1,302.31..5,282.56 rows=559 width=320) (actual time=27.536..60.275 rows=19 loops=1)

27. 0.036 14.881 ↑ 29.4 19 1

Nested Loop (cost=1,301.89..5,012.48 rows=559 width=320) (actual time=11.442..14.881 rows=19 loops=1)

28. 5.229 14.674 ↑ 29.4 19 1

Hash Join (cost=1,301.47..3,183.75 rows=559 width=312) (actual time=11.420..14.674 rows=19 loops=1)

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

Seq Scan on pinnedfolders pf (cost=0.00..1,603.53 rows=21,853 width=278) (actual time=0.013..6.044 rows=21,853 loops=1)

30. 1.175 3.401 ↑ 1.0 2,938 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 239kB
31. 1.799 2.226 ↑ 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.514..2.226 rows=2,938 loops=1)

  • Recheck Cond: (parent_shareuser_id IS NOT NULL)
  • Heap Blocks: exact=416
32. 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)
33. 0.171 0.171 ↑ 1.0 1 19

Index Scan using pk_shareusers on shareusers par_su_2 (cost=0.42..3.26 rows=1 width=16) (actual time=0.008..0.009 rows=1 loops=19)

  • Index Cond: (id = s_2.parent_shareuser_id)
34. 45.353 45.353 ↑ 1.0 1 19

Index Scan using pk_shares on shares par_s_2 (cost=0.42..0.47 rows=1 width=16) (actual time=2.386..2.387 rows=1 loops=19)

  • Index Cond: (id = par_su_2.share_id)
35. 20.786 20.786 ↑ 1.0 1 19

Index Scan using pk_pinnedfolders on pinnedfolders par_pf_1 (cost=0.29..0.35 rows=1 width=81) (actual time=1.094..1.094 rows=1 loops=19)

  • Index Cond: (id = par_s_2.object_id)
36. 0.000 0.000 ↓ 0.0 0

Index Only Scan using pk_objects on objects o_2 (cost=0.43..7.16 rows=1 width=8) (never executed)

  • Index Cond: (id = pf.id)
  • Heap Fetches: 0