explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hYxD

Settings
# exclusive inclusive rows x rows loops node
1. 0.102 253.358 ↑ 1.8 73 1

Nested Loop Left Join (cost=93,523.51..126,271.39 rows=131 width=210) (actual time=246.898..253.358 rows=73 loops=1)

  • Join Filter: (reel.id = personal_board_1.board_id)
  • Rows Removed by Join Filter: 73
2. 0.014 245.737 ↑ 1.0 73 1

Limit (cost=93,123.58..93,123.76 rows=73 width=146) (actual time=245.708..245.737 rows=73 loops=1)

3.          

CTE parent

4. 48.717 185.103 ↑ 3.9 51,248 1

Recursive Union (cost=649.24..84,994.55 rows=199,928 width=80) (actual time=2.129..185.103 rows=51,248 loops=1)

5. 5.067 6.960 ↑ 1.1 13,454 1

Bitmap Heap Scan on reel reel_1 (cost=649.24..3,389.22 rows=14,558 width=80) (actual time=2.125..6.960 rows=13,454 loops=1)

  • Recheck Cond: (workspace_id = '1f32d99e-2062-43cb-b9a8-7ae13ab7c977'::uuid)
  • Heap Blocks: exact=1909
6. 1.893 1.893 ↑ 1.1 13,454 1

Bitmap Index Scan on reel_workspace_id_index (cost=0.00..645.60 rows=14,558 width=0) (actual time=1.893..1.893 rows=13,454 loops=1)

  • Index Cond: (workspace_id = '1f32d99e-2062-43cb-b9a8-7ae13ab7c977'::uuid)
7. 110.965 129.426 ↑ 5.4 3,436 11

Hash Join (cost=3,571.19..7,760.68 rows=18,537 width=80) (actual time=0.893..11.766 rows=3,436 loops=11)

  • Hash Cond: (parent_1.parent_id = r.id)
8. 8.800 8.800 ↑ 32.2 4,495 11

WorkTable Scan on parent parent_1 (cost=0.00..3,275.55 rows=144,852 width=64) (actual time=0.001..0.800 rows=4,495 loops=11)

  • Filter: (id <> 'c05212de-a166-4d12-b5ac-ae6dd1adb394'::uuid)
  • Rows Removed by Filter: 164
9. 3.154 9.661 ↑ 1.1 13,454 1

Hash (cost=3,389.22..3,389.22 rows=14,558 width=52) (actual time=9.661..9.661 rows=13,454 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 1217kB
10. 4.652 6.507 ↑ 1.1 13,454 1

Bitmap Heap Scan on reel r (cost=649.24..3,389.22 rows=14,558 width=52) (actual time=2.084..6.507 rows=13,454 loops=1)

  • Recheck Cond: (workspace_id = '1f32d99e-2062-43cb-b9a8-7ae13ab7c977'::uuid)
  • Heap Blocks: exact=1909
11. 1.855 1.855 ↑ 1.1 13,454 1

Bitmap Index Scan on reel_workspace_id_index (cost=0.00..645.60 rows=14,558 width=0) (actual time=1.855..1.855 rows=13,454 loops=1)

  • Index Cond: (workspace_id = '1f32d99e-2062-43cb-b9a8-7ae13ab7c977'::uuid)
12. 0.769 245.723 ↑ 1.8 73 1

Sort (cost=8,129.04..8,129.36 rows=128 width=146) (actual time=245.706..245.723 rows=73 loops=1)

  • Sort Key: reel.updated_at DESC, reel.id DESC
  • Sort Method: top-N heapsort Memory: 46kB
13. 2.504 244.954 ↓ 14.1 1,802 1

Hash Left Join (cost=3,619.06..8,124.56 rows=128 width=146) (actual time=22.529..244.954 rows=1,802 loops=1)

  • Hash Cond: (reel.id = is_in_board_subquery.board_id)
14. 1.433 242.253 ↓ 14.1 1,802 1

Hash Join (cost=3,571.19..8,074.60 rows=128 width=145) (actual time=22.311..242.253 rows=1,802 loops=1)

  • Hash Cond: (parent.root_id = reel.id)
15. 230.060 230.060 ↓ 1.8 1,802 1

CTE Scan on parent (cost=0.00..4,498.38 rows=1,000 width=48) (actual time=11.471..230.060 rows=1,802 loops=1)

  • Filter: (id = 'c05212de-a166-4d12-b5ac-ae6dd1adb394'::uuid)
  • Rows Removed by Filter: 49446
16. 4.021 10.760 ↑ 1.1 13,454 1

Hash (cost=3,389.22..3,389.22 rows=14,558 width=113) (actual time=10.760..10.760 rows=13,454 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 1894kB
17. 4.897 6.739 ↑ 1.1 13,454 1

Bitmap Heap Scan on reel (cost=649.24..3,389.22 rows=14,558 width=113) (actual time=2.071..6.739 rows=13,454 loops=1)

  • Recheck Cond: (workspace_id = '1f32d99e-2062-43cb-b9a8-7ae13ab7c977'::uuid)
  • Heap Blocks: exact=1909
18. 1.842 1.842 ↑ 1.1 13,454 1

Bitmap Index Scan on reel_workspace_id_index (cost=0.00..645.60 rows=14,558 width=0) (actual time=1.842..1.842 rows=13,454 loops=1)

  • Index Cond: (workspace_id = '1f32d99e-2062-43cb-b9a8-7ae13ab7c977'::uuid)
19. 0.030 0.197 ↓ 1.0 143 1

Hash (cost=46.09..46.09 rows=142 width=17) (actual time=0.197..0.197 rows=143 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
20. 0.011 0.167 ↓ 1.0 143 1

Subquery Scan on is_in_board_subquery (cost=42.89..46.09 rows=142 width=17) (actual time=0.141..0.167 rows=143 loops=1)

21. 0.058 0.156 ↓ 1.0 143 1

HashAggregate (cost=42.89..44.67 rows=142 width=17) (actual time=0.141..0.156 rows=143 loops=1)

  • Group Key: personal_board.board_id
22. 0.059 0.098 ↑ 1.0 143 1

Bitmap Heap Scan on personal_board (cost=5.39..42.18 rows=143 width=32) (actual time=0.047..0.098 rows=143 loops=1)

  • Recheck Cond: (account_id = '0b4c3803-a3e6-4a65-8901-3de66db88db2'::uuid)
  • Heap Blocks: exact=30
23. 0.039 0.039 ↓ 1.1 151 1

Bitmap Index Scan on personal_board_account_id_index (cost=0.00..5.35 rows=143 width=0) (actual time=0.039..0.039 rows=151 loops=1)

  • Index Cond: (account_id = '0b4c3803-a3e6-4a65-8901-3de66db88db2'::uuid)
24. 0.002 1.022 ↑ 358.0 1 73

Materialize (cost=399.92..413.35 rows=358 width=48) (actual time=0.014..0.014 rows=1 loops=73)

25. 0.033 1.020 ↑ 358.0 1 1

GroupAggregate (cost=399.92..407.98 rows=358 width=48) (actual time=1.020..1.020 rows=1 loops=1)

  • Group Key: personal_board_1.board_id
26. 0.011 0.987 ↑ 358.0 1 1

Sort (cost=399.92..400.82 rows=358 width=134) (actual time=0.987..0.987 rows=1 loops=1)

  • Sort Key: personal_board_1.board_id
  • Sort Method: quicksort Memory: 25kB
27. 0.005 0.976 ↑ 358.0 1 1

Nested Loop Left Join (cost=135.74..384.74 rows=358 width=134) (actual time=0.968..0.976 rows=1 loops=1)

28. 0.024 0.961 ↑ 358.0 1 1

Hash Join (cost=135.45..198.96 rows=358 width=32) (actual time=0.954..0.961 rows=1 loops=1)

  • Hash Cond: (workspace_member.account_id = personal_board_1.account_id)
29. 0.031 0.057 ↑ 1.1 13 1

Bitmap Heap Scan on workspace_member (cost=4.40..50.48 rows=14 width=16) (actual time=0.041..0.057 rows=13 loops=1)

  • Recheck Cond: (workspace_id = '1f32d99e-2062-43cb-b9a8-7ae13ab7c977'::uuid)
  • Filter: ((member_role <> 'sysadmin'::text) AND (status = 'active'::text))
  • Rows Removed by Filter: 2
  • Heap Blocks: exact=12
30. 0.026 0.026 ↑ 1.0 15 1

Bitmap Index Scan on team_member_team_id_account_id_key (cost=0.00..4.40 rows=15 width=0) (actual time=0.026..0.026 rows=15 loops=1)

  • Index Cond: (workspace_id = '1f32d99e-2062-43cb-b9a8-7ae13ab7c977'::uuid)
31. 0.530 0.880 ↓ 1.0 4,365 1

Hash (cost=77.69..77.69 rows=4,269 width=32) (actual time=0.880..0.880 rows=4,365 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 337kB
32. 0.350 0.350 ↓ 1.0 4,365 1

Seq Scan on personal_board personal_board_1 (cost=0.00..77.69 rows=4,269 width=32) (actual time=0.007..0.350 rows=4,365 loops=1)

33. 0.010 0.010 ↑ 1.0 1 1

Index Scan using account_pkey on account (cost=0.28..0.51 rows=1 width=118) (actual time=0.009..0.010 rows=1 loops=1)

  • Index Cond: (id = personal_board_1.account_id)
34.          

SubPlan (for Nested Loop Left Join)

35. 0.146 6.497 ↑ 1.0 1 73

Result (cost=246.88..246.89 rows=1 width=32) (actual time=0.089..0.089 rows=1 loops=73)

36.          

Initplan (for Result)

37. 0.073 6.351 ↑ 1.0 4 73

Limit (cost=1.71..246.88 rows=4 width=40) (actual time=0.041..0.087 rows=4 loops=73)

38. 0.255 6.278 ↑ 6.8 4 73

Nested Loop (cost=1.71..1,656.59 rows=27 width=40) (actual time=0.041..0.086 rows=4 loops=73)

39. 0.028 4.891 ↑ 13.0 4 73

Nested Loop (cost=1.28..1,561.69 rows=52 width=16) (actual time=0.036..0.067 rows=4 loops=73)

40. 0.146 1.095 ↑ 60.4 13 73

Nested Loop (cost=0.85..508.40 rows=785 width=16) (actual time=0.012..0.015 rows=13 loops=73)

41. 0.365 0.365 ↑ 30.0 1 73

Index Scan using board_ancestor_ancestor_id_index on board_ancestor (cost=0.42..124.88 rows=30 width=16) (actual time=0.004..0.005 rows=1 loops=73)

  • Index Cond: (ancestor_id = reel.id)
42. 0.584 0.584 ↑ 8.5 13 73

Index Only Scan using reel_clip_pkey on reel_clip (cost=0.43..11.68 rows=110 width=32) (actual time=0.007..0.008 rows=13 loops=73)

  • Index Cond: (reel_id = board_ancestor.id)
  • Heap Fetches: 25
43. 3.768 3.768 ↓ 0.0 0 942

Index Scan using clip_pkey on clip (cost=0.43..1.33 rows=1 width=32) (actual time=0.004..0.004 rows=0 loops=942)

  • Index Cond: (id = reel_clip.clip_id)
  • Filter: (visible AND (NOT inactive) AND (status <> 'created'::text) AND (status = 'transcoded'::text) AND (workspace_id = '1f32d99e-2062-43cb-b9a8-7ae13ab7c977'::uuid))
  • Rows Removed by Filter: 1
44. 1.132 1.132 ↑ 1.0 1 283

Index Scan using clip_media_pkey on clip_media (cost=0.43..1.81 rows=1 width=56) (actual time=0.004..0.004 rows=1 loops=283)

  • Index Cond: (id = clip.media_id)
  • Filter: (image IS NOT NULL)