explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qeYI

Settings
# exclusive inclusive rows x rows loops node
1. 0.015 49.527 ↓ 4.0 4 1

Sort (cost=8,645.77..8,645.78 rows=1 width=194) (actual time=49.526..49.527 rows=4 loops=1)

  • Sort Key: reel.title, reel.id
  • Sort Method: quicksort Memory: 27kB
2. 0.031 49.512 ↓ 4.0 4 1

Merge Right Join (cost=8,301.96..8,645.76 rows=1 width=194) (actual time=48.996..49.512 rows=4 loops=1)

  • Merge Cond: (personal_board.board_id = reel.id)
3. 0.414 1.568 ↑ 1.5 51 1

GroupAggregate (cost=73.71..75.49 rows=79 width=48) (actual time=1.156..1.568 rows=51 loops=1)

  • Group Key: personal_board.board_id
4. 0.052 1.154 ↑ 1.5 51 1

Sort (cost=73.71..73.91 rows=79 width=147) (actual time=1.132..1.154 rows=51 loops=1)

  • Sort Key: personal_board.board_id
  • Sort Method: quicksort Memory: 38kB
5. 0.038 1.102 ↑ 1.5 51 1

Hash Left Join (cost=44.65..71.22 rows=79 width=147) (actual time=0.625..1.102 rows=51 loops=1)

  • Hash Cond: (personal_board.account_id = account.id)
6. 0.240 0.488 ↑ 1.5 51 1

Hash Join (cost=15.19..40.68 rows=79 width=32) (actual time=0.046..0.488 rows=51 loops=1)

  • Hash Cond: (personal_board.account_id = workspace_member.account_id)
7. 0.229 0.229 ↓ 1.0 907 1

Seq Scan on personal_board (cost=0.00..17.97 rows=897 width=32) (actual time=0.006..0.229 rows=907 loops=1)

8. 0.006 0.019 ↑ 1.0 4 1

Hash (cost=15.14..15.14 rows=4 width=16) (actual time=0.018..0.019 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
9. 0.007 0.013 ↑ 1.0 4 1

Bitmap Heap Scan on workspace_member (cost=4.31..15.14 rows=4 width=16) (actual time=0.011..0.013 rows=4 loops=1)

  • Recheck Cond: (workspace_id = '64fd5327-6007-4a54-81a7-7359817930a0'::uuid)
  • Filter: ((member_role <> 'sysadmin'::text) AND (status = 'active'::text))
  • Heap Blocks: exact=1
10. 0.006 0.006 ↑ 1.0 4 1

Bitmap Index Scan on team_member_team_id_account_id_key (cost=0.00..4.31 rows=4 width=0) (actual time=0.006..0.006 rows=4 loops=1)

  • Index Cond: (workspace_id = '64fd5327-6007-4a54-81a7-7359817930a0'::uuid)
11. 0.292 0.576 ↓ 1.0 700 1

Hash (cost=20.87..20.87 rows=687 width=131) (actual time=0.575..0.576 rows=700 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 70kB
12. 0.284 0.284 ↓ 1.0 700 1

Seq Scan on account (cost=0.00..20.87 rows=687 width=131) (actual time=0.005..0.284 rows=700 loops=1)

13. 0.009 47.649 ↓ 4.0 4 1

Sort (cost=8,228.24..8,228.25 rows=1 width=130) (actual time=47.648..47.649 rows=4 loops=1)

  • Sort Key: reel.id
  • Sort Method: quicksort Memory: 26kB
14. 0.009 47.640 ↓ 4.0 4 1

Nested Loop (cost=8,220.19..8,228.23 rows=1 width=130) (actual time=47.614..47.640 rows=4 loops=1)

15. 0.004 47.595 ↓ 4.0 4 1

Limit (cost=8,219.91..8,219.91 rows=1 width=71) (actual time=47.588..47.595 rows=4 loops=1)

16.          

CTE parent

17. 10.924 39.900 ↑ 2.3 7,321 1

Recursive Union (cost=40.70..7,254.61 rows=16,746 width=80) (actual time=0.118..39.900 rows=7,321 loops=1)

18. 0.667 0.761 ↑ 1.0 1,069 1

Bitmap Heap Scan on reel reel_2 (cost=40.70..329.27 rows=1,086 width=80) (actual time=0.113..0.761 rows=1,069 loops=1)

  • Recheck Cond: (workspace_id = '64fd5327-6007-4a54-81a7-7359817930a0'::uuid)
  • Heap Blocks: exact=173
19. 0.094 0.094 ↑ 1.0 1,086 1

Bitmap Index Scan on reel_workspace_id_index (cost=0.00..40.43 rows=1,086 width=0) (actual time=0.094..0.094 rows=1,086 loops=1)

  • Index Cond: (workspace_id = '64fd5327-6007-4a54-81a7-7359817930a0'::uuid)
20. 24.904 28.215 ↑ 2.8 568 11

Hash Join (cost=342.85..659.04 rows=1,566 width=80) (actual time=0.099..2.565 rows=568 loops=11)

  • Hash Cond: (parent_1.parent_id = r.id)
21. 2.354 2.354 ↑ 16.2 665 11

WorkTable Scan on parent parent_1 (cost=0.00..244.35 rows=10,806 width=64) (actual time=0.001..0.214 rows=665 loops=11)

  • Filter: (id <> 'ed144b14-dad1-47bc-bca3-b2848f75a44d'::uuid)
  • Rows Removed by Filter: 0
22. 0.394 0.957 ↑ 1.0 1,069 1

Hash (cost=329.27..329.27 rows=1,086 width=46) (actual time=0.957..0.957 rows=1,069 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 102kB
23. 0.477 0.563 ↑ 1.0 1,069 1

Bitmap Heap Scan on reel r (cost=40.70..329.27 rows=1,086 width=46) (actual time=0.102..0.563 rows=1,069 loops=1)

  • Recheck Cond: (workspace_id = '64fd5327-6007-4a54-81a7-7359817930a0'::uuid)
  • Heap Blocks: exact=173
24. 0.086 0.086 ↑ 1.0 1,086 1

Bitmap Index Scan on reel_workspace_id_index (cost=0.00..40.43 rows=1,086 width=0) (actual time=0.086..0.086 rows=1,086 loops=1)

  • Index Cond: (workspace_id = '64fd5327-6007-4a54-81a7-7359817930a0'::uuid)
25. 0.042 47.591 ↓ 4.0 4 1

Sort (cost=965.30..965.30 rows=1 width=71) (actual time=47.587..47.591 rows=4 loops=1)

  • Sort Key: reel_1.title, reel_1.id
  • Sort Method: quicksort Memory: 25kB
26. 0.044 47.549 ↓ 4.0 4 1

Nested Loop Left Join (cost=13.96..965.29 rows=1 width=71) (actual time=6.042..47.549 rows=4 loops=1)

  • Join Filter: (reel_1.id = personal_board_1.board_id)
  • Rows Removed by Join Filter: 67
27. 0.018 47.437 ↓ 4.0 4 1

Nested Loop (cost=0.28..951.04 rows=1 width=62) (actual time=5.990..47.437 rows=4 loops=1)

28. 47.349 47.349 ↑ 16.8 5 1

CTE Scan on parent (cost=0.00..376.78 rows=84 width=48) (actual time=1.073..47.349 rows=5 loops=1)

  • Filter: (id = 'ed144b14-dad1-47bc-bca3-b2848f75a44d'::uuid)
  • Rows Removed by Filter: 7316
29. 0.070 0.070 ↑ 1.0 1 5

Index Scan using reel_pkey on reel reel_1 (cost=0.28..6.83 rows=1 width=30) (actual time=0.014..0.014 rows=1 loops=5)

  • Index Cond: (id = parent.root_id)
  • Filter: ((workspace_id = '64fd5327-6007-4a54-81a7-7359817930a0'::uuid) AND ((title @@ '''inner'''::tsquery) OR (title @@ '''inner'''::tsquery) OR (description @@ '''inner'''::tsquery) OR (description @@ '''inner'''::tsquery)))
  • Rows Removed by Filter: 0
30. 0.045 0.068 ↓ 1.1 17 4

HashAggregate (cost=13.68..13.88 rows=16 width=17) (actual time=0.009..0.017 rows=17 loops=4)

  • Group Key: personal_board_1.board_id
31. 0.014 0.023 ↓ 1.1 17 1

Bitmap Heap Scan on personal_board personal_board_1 (cost=4.40..13.60 rows=16 width=32) (actual time=0.012..0.023 rows=17 loops=1)

  • Recheck Cond: (account_id = '82e5cd00-6f64-4b2a-92c8-950fca470448'::uuid)
  • Heap Blocks: exact=8
32. 0.009 0.009 ↓ 1.1 17 1

Bitmap Index Scan on personal_board_account_id_index (cost=0.00..4.40 rows=16 width=0) (actual time=0.008..0.009 rows=17 loops=1)

  • Index Cond: (account_id = '82e5cd00-6f64-4b2a-92c8-950fca470448'::uuid)
33. 0.036 0.036 ↑ 1.0 1 4

Index Scan using reel_pkey on reel (cost=0.28..8.30 rows=1 width=97) (actual time=0.008..0.009 rows=1 loops=4)

  • Index Cond: (id = reel_1.id)
  • Filter: (workspace_id = '64fd5327-6007-4a54-81a7-7359817930a0'::uuid)
34.          

SubPlan (for Merge Right Join)

35. 0.020 0.264 ↑ 1.0 1 4

Result (cost=341.02..341.03 rows=1 width=32) (actual time=0.065..0.066 rows=1 loops=4)

36.          

Initplan (for Result)

37. 0.008 0.244 ↑ 1.5 2 4

Limit (cost=1.55..341.02 rows=3 width=41) (actual time=0.034..0.061 rows=2 loops=4)

38. 0.014 0.236 ↑ 1.5 2 4

Nested Loop (cost=1.55..341.02 rows=3 width=41) (actual time=0.034..0.059 rows=2 loops=4)

39. 0.016 0.172 ↑ 4.0 2 4

Nested Loop (cost=1.13..329.17 rows=8 width=16) (actual time=0.027..0.043 rows=2 loops=4)

40. 0.014 0.096 ↑ 43.5 2 4

Nested Loop (cost=0.71..194.69 rows=87 width=16) (actual time=0.018..0.024 rows=2 loops=4)

41. 0.040 0.040 ↑ 2.5 2 4

Index Scan using board_ancestor_ancestor_id_index on board_ancestor (cost=0.29..24.16 rows=5 width=16) (actual time=0.008..0.010 rows=2 loops=4)

  • Index Cond: (ancestor_id = reel.id)
42. 0.042 0.042 ↑ 35.0 1 7

Index Only Scan using reel_clip_pkey on reel_clip (cost=0.42..33.76 rows=35 width=32) (actual time=0.005..0.006 rows=1 loops=7)

  • Index Cond: (reel_id = board_ancestor.id)
  • Heap Fetches: 10
43. 0.060 0.060 ↑ 1.0 1 10

Index Scan using clip_pkey on clip (cost=0.42..1.54 rows=1 width=32) (actual time=0.006..0.006 rows=1 loops=10)

  • Index Cond: (id = reel_clip.clip_id)
  • Filter: (visible AND (NOT inactive) AND (status <> 'created'::text) AND (status = 'transcoded'::text) AND (workspace_id = '64fd5327-6007-4a54-81a7-7359817930a0'::uuid))
44. 0.050 0.050 ↑ 1.0 1 10

Index Scan using clip_media_pkey on clip_media (cost=0.42..1.47 rows=1 width=57) (actual time=0.005..0.005 rows=1 loops=10)

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