explain.depesz.com

PostgreSQL's explain analyze made readable

Result: d3so

Settings
# exclusive inclusive rows x rows loops node
1. 102.152 3,763.132 ↑ 158.5 21 1

Nested Loop Left Join (cost=512,622.87..1,353,413.99 rows=3,329 width=228) (actual time=3,541.533..3,763.132 rows=21 loops=1)

  • Join Filter: (reel_clip.reel_id = reel.id)
  • Rows Removed by Join Filter: 1283628
2. 0.033 308.750 ↑ 1.8 21 1

Nested Loop Left Join (cost=91,929.27..92,055.73 rows=38 width=188) (actual time=308.697..308.750 rows=21 loops=1)

  • Join Filter: (reel.id = members_subquery.board_id)
  • Rows Removed by Join Filter: 21
3. 0.009 307.646 ↑ 1.0 21 1

Limit (cost=91,529.30..91,529.35 rows=21 width=148) (actual time=307.622..307.646 rows=21 loops=1)

4.          

CTE parent

5. 52.148 196.075 ↑ 4.0 53,050 1

Recursive Union (cost=667.11..82,614.55 rows=210,049 width=80) (actual time=2.114..196.075 rows=53,050 loops=1)

6. 4.930 6.801 ↑ 1.1 13,454 1

Bitmap Heap Scan on reel reel_1 (cost=667.11..3,410.10 rows=14,799 width=80) (actual time=2.107..6.801 rows=13,454 loops=1)

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

Bitmap Index Scan on reel_workspace_id_index (cost=0.00..663.41 rows=14,799 width=0) (actual time=1.871..1.871 rows=13,454 loops=1)

  • Index Cond: (workspace_id = '1f32d99e-2062-43cb-b9a8-7ae13ab7c977'::uuid)
8. 120.609 137.126 ↑ 5.4 3,600 11

Hash Join (cost=3,595.08..7,500.35 rows=19,525 width=80) (actual time=0.910..12.466 rows=3,600 loops=11)

  • Hash Cond: (parent_1.parent_id = r.id)
9. 6.677 6.677 ↑ 30.7 4,823 11

WorkTable Scan on parent parent_1 (cost=0.00..2,959.80 rows=147,990 width=64) (actual time=0.000..0.607 rows=4,823 loops=11)

10. 3.260 9.840 ↑ 1.1 13,454 1

Hash (cost=3,410.10..3,410.10 rows=14,799 width=53) (actual time=9.840..9.840 rows=13,454 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 1217kB
11. 4.666 6.580 ↑ 1.1 13,454 1

Bitmap Heap Scan on reel r (cost=667.11..3,410.10 rows=14,799 width=53) (actual time=2.148..6.580 rows=13,454 loops=1)

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

Bitmap Index Scan on reel_workspace_id_index (cost=0.00..663.41 rows=14,799 width=0) (actual time=1.914..1.914 rows=13,454 loops=1)

  • Index Cond: (workspace_id = '1f32d99e-2062-43cb-b9a8-7ae13ab7c977'::uuid)
13. 5.164 307.637 ↑ 6.6 21 1

Sort (cost=8,914.75..8,915.10 rows=139 width=148) (actual time=307.621..307.637 rows=21 loops=1)

  • Sort Key: reel.updated_at DESC, reel.id DESC
  • Sort Method: top-N heapsort Memory: 34kB
14. 27.009 302.473 ↓ 96.8 13,454 1

Hash Left Join (cost=3,642.95..8,911.00 rows=139 width=148) (actual time=16.427..302.473 rows=13,454 loops=1)

  • Hash Cond: (reel.id = is_in_board_subquery.board_id)
15. 22.179 275.265 ↓ 96.8 13,454 1

Hash Right Join (cost=3,595.08..8,860.87 rows=139 width=147) (actual time=16.206..275.265 rows=13,454 loops=1)

  • Hash Cond: (parent.root_id = reel.id)
  • Filter: (parent.parent_id IS NULL)
  • Rows Removed by Filter: 39596
16. 241.413 241.413 ↑ 4.0 53,050 1

CTE Scan on parent (cost=0.00..4,200.98 rows=210,049 width=64) (actual time=2.117..241.413 rows=53,050 loops=1)

17. 4.152 11.673 ↑ 1.1 13,454 1

Hash (cost=3,410.10..3,410.10 rows=14,799 width=115) (actual time=11.673..11.673 rows=13,454 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 1894kB
18. 5.596 7.521 ↑ 1.1 13,454 1

Bitmap Heap Scan on reel (cost=667.11..3,410.10 rows=14,799 width=115) (actual time=2.202..7.521 rows=13,454 loops=1)

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

Bitmap Index Scan on reel_workspace_id_index (cost=0.00..663.41 rows=14,799 width=0) (actual time=1.925..1.925 rows=13,454 loops=1)

  • Index Cond: (workspace_id = '1f32d99e-2062-43cb-b9a8-7ae13ab7c977'::uuid)
20. 0.027 0.199 ↓ 1.0 143 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
21. 0.016 0.172 ↓ 1.0 143 1

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

22. 0.064 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
23. 0.049 0.092 ↑ 1.0 143 1

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

  • Recheck Cond: (account_id = '0b4c3803-a3e6-4a65-8901-3de66db88db2'::uuid)
  • Heap Blocks: exact=30
24. 0.043 0.043 ↓ 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.043..0.043 rows=151 loops=1)

  • Index Cond: (account_id = '0b4c3803-a3e6-4a65-8901-3de66db88db2'::uuid)
25. 0.006 1.071 ↑ 358.0 1 21

Materialize (cost=399.97..414.29 rows=358 width=56) (actual time=0.051..0.051 rows=1 loops=21)

26. 0.001 1.065 ↑ 358.0 1 1

Subquery Scan on members_subquery (cost=399.97..412.50 rows=358 width=56) (actual time=1.065..1.065 rows=1 loops=1)

27. 0.037 1.064 ↑ 358.0 1 1

GroupAggregate (cost=399.97..408.92 rows=358 width=56) (actual time=1.064..1.064 rows=1 loops=1)

  • Group Key: personal_board_1.board_id
28. 0.011 1.027 ↑ 358.0 1 1

Sort (cost=399.97..400.87 rows=358 width=150) (actual time=1.027..1.027 rows=1 loops=1)

  • Sort Key: personal_board_1.board_id
  • Sort Method: quicksort Memory: 25kB
29. 0.003 1.016 ↑ 358.0 1 1

Nested Loop Left Join (cost=135.74..384.79 rows=358 width=150) (actual time=1.006..1.016 rows=1 loops=1)

30. 0.019 1.001 ↑ 358.0 1 1

Hash Join (cost=135.45..199.00 rows=358 width=48) (actual time=0.992..1.001 rows=1 loops=1)

  • Hash Cond: (workspace_member.account_id = personal_board_1.account_id)
31. 0.030 0.054 ↑ 1.1 13 1

Bitmap Heap Scan on workspace_member (cost=4.40..50.53 rows=14 width=16) (actual time=0.036..0.054 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
32. 0.024 0.024 ↑ 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.024..0.024 rows=15 loops=1)

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

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

  • Buckets: 8192 Batches: 1 Memory Usage: 337kB
34. 0.358 0.358 ↓ 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.358 rows=4,365 loops=1)

35. 0.012 0.012 ↑ 1.0 1 1

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

  • Index Cond: (id = personal_board_1.account_id)
36. 114.392 3,350.613 ↓ 3.5 61,126 21

Materialize (cost=420,693.60..421,131.57 rows=17,519 width=24) (actual time=153.453..159.553 rows=61,126 loops=21)

37. 464.971 3,236.221 ↓ 3.5 61,126 1

HashAggregate (cost=420,693.60..420,868.79 rows=17,519 width=24) (actual time=3,222.461..3,236.221 rows=61,126 loops=1)

  • Group Key: reel_clip.reel_id
38. 1,168.745 2,771.250 ↓ 1.1 1,853,734 1

Hash Join (cost=305,110.40..412,352.97 rows=1,668,125 width=32) (actual time=1,365.880..2,771.250 rows=1,853,734 loops=1)

  • Hash Cond: (reel_clip.clip_id = clip.id)
39. 237.522 237.522 ↓ 1.0 1,959,019 1

Seq Scan on reel_clip (cost=0.00..37,930.35 rows=1,945,435 width=32) (actual time=0.004..237.522 rows=1,959,019 loops=1)

40. 517.409 1,364.983 ↓ 1.1 2,986,361 1

Hash (cost=255,719.08..255,719.08 rows=2,841,386 width=16) (actual time=1,364.983..1,364.983 rows=2,986,361 loops=1)

  • Buckets: 131072 Batches: 64 Memory Usage: 3218kB
41. 847.574 847.574 ↓ 1.1 2,986,361 1

Index Only Scan using clip_list_recorded_at_index on clip (cost=0.56..255,719.08 rows=2,841,386 width=16) (actual time=0.035..847.574 rows=2,986,361 loops=1)

  • Heap Fetches: 37464
42.          

SubPlan (for Nested Loop Left Join)

43. 0.063 1.617 ↑ 1.0 1 21

Result (cost=249.40..249.41 rows=1 width=32) (actual time=0.077..0.077 rows=1 loops=21)

44.          

Initplan (for Result)

45. 0.021 1.554 ↑ 1.0 4 21

Limit (cost=1.71..249.40 rows=4 width=40) (actual time=0.036..0.074 rows=4 loops=21)

46. 0.063 1.533 ↑ 7.0 4 21

Nested Loop (cost=1.71..1,735.52 rows=28 width=40) (actual time=0.036..0.073 rows=4 loops=21)

47. 0.054 1.050 ↑ 13.2 4 21

Nested Loop (cost=1.28..1,635.87 rows=53 width=16) (actual time=0.029..0.050 rows=4 loops=21)

48. 0.063 0.441 ↑ 157.8 5 21

Nested Loop (cost=0.85..581.21 rows=789 width=16) (actual time=0.019..0.021 rows=5 loops=21)

49. 0.189 0.189 ↑ 30.0 1 21

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

  • Index Cond: (ancestor_id = reel.id)
50. 0.189 0.189 ↑ 22.2 5 21

Index Only Scan using reel_clip_pkey on reel_clip reel_clip_1 (cost=0.43..14.10 rows=111 width=32) (actual time=0.007..0.009 rows=5 loops=21)

  • Index Cond: (reel_id = board_ancestor.id)
  • Heap Fetches: 2
51. 0.555 0.555 ↑ 1.0 1 111

Index Scan using clip_pkey on clip clip_1 (cost=0.43..1.33 rows=1 width=32) (actual time=0.005..0.005 rows=1 loops=111)

  • Index Cond: (id = reel_clip_1.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: 0
52. 0.420 0.420 ↑ 1.0 1 84

Index Scan using clip_media_pkey on clip_media (cost=0.43..1.87 rows=1 width=56) (actual time=0.005..0.005 rows=1 loops=84)

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