explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bXON

Settings
# exclusive inclusive rows x rows loops node
1. 0.087 315.128 ↓ 8.1 73 1

Sort (cost=96,359.96..96,359.98 rows=9 width=210) (actual time=315.121..315.128 rows=73 loops=1)

  • Sort Key: reel.updated_at DESC, reel.id DESC
  • Sort Method: quicksort Memory: 63kB
2. 0.095 315.041 ↓ 8.1 73 1

Merge Right Join (cost=94,125.23..96,359.81 rows=9 width=210) (actual time=308.492..315.041 rows=73 loops=1)

  • Merge Cond: (personal_board.board_id = reel.id)
3. 0.029 1.108 ↑ 358.0 1 1

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

  • Group Key: personal_board.board_id
4. 0.009 1.079 ↑ 358.0 1 1

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

  • Sort Key: personal_board.board_id
  • Sort Method: quicksort Memory: 25kB
5. 0.003 1.070 ↑ 358.0 1 1

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

6. 0.046 1.059 ↑ 358.0 1 1

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

  • Hash Cond: (workspace_member.account_id = personal_board.account_id)
7. 0.030 0.050 ↑ 1.1 13 1

Bitmap Heap Scan on workspace_member (cost=4.40..50.48 rows=14 width=16) (actual time=0.031..0.050 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
8. 0.020 0.020 ↑ 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.020..0.020 rows=15 loops=1)

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

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

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

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

11. 0.008 0.008 ↑ 1.0 1 1

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

  • Index Cond: (id = personal_board.account_id)
12. 0.065 307.195 ↓ 8.1 73 1

Sort (cost=93,725.30..93,725.33 rows=9 width=146) (actual time=307.186..307.195 rows=73 loops=1)

  • Sort Key: reel.id
  • Sort Method: quicksort Memory: 46kB
13. 0.061 307.130 ↓ 8.1 73 1

Nested Loop (cost=93,124.00..93,725.16 rows=9 width=146) (actual time=306.790..307.130 rows=73 loops=1)

14. 0.010 306.777 ↑ 1.0 73 1

Limit (cost=93,123.58..93,123.76 rows=73 width=57) (actual time=306.756..306.777 rows=73 loops=1)

15.          

CTE parent

16. 51.544 193.615 ↑ 3.9 51,248 1

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

17. 6.310 8.234 ↑ 1.1 13,454 1

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

  • Recheck Cond: (workspace_id = '1f32d99e-2062-43cb-b9a8-7ae13ab7c977'::uuid)
  • Heap Blocks: exact=1909
18. 1.924 1.924 ↑ 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.924..1.924 rows=13,454 loops=1)

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

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

  • Hash Cond: (parent_1.parent_id = r.id)
20. 8.877 8.877 ↑ 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.807 rows=4,495 loops=11)

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

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

  • Buckets: 16384 Batches: 1 Memory Usage: 1217kB
22. 5.363 7.248 ↑ 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.119..7.248 rows=13,454 loops=1)

  • Recheck Cond: (workspace_id = '1f32d99e-2062-43cb-b9a8-7ae13ab7c977'::uuid)
  • Heap Blocks: exact=1909
23. 1.885 1.885 ↑ 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.885..1.885 rows=13,454 loops=1)

  • Index Cond: (workspace_id = '1f32d99e-2062-43cb-b9a8-7ae13ab7c977'::uuid)
24. 0.535 306.767 ↑ 1.8 73 1

Sort (cost=8,129.04..8,129.36 rows=128 width=57) (actual time=306.755..306.767 rows=73 loops=1)

  • Sort Key: reel_1.updated_at DESC, reel_1.id DESC
  • Sort Method: top-N heapsort Memory: 36kB
25. 2.556 306.232 ↓ 14.1 1,802 1

Hash Left Join (cost=3,619.06..8,124.56 rows=128 width=57) (actual time=23.226..306.232 rows=1,802 loops=1)

  • Hash Cond: (reel_1.id = is_in_board_subquery.board_id)
26. 1.236 303.470 ↓ 14.1 1,802 1

Hash Join (cost=3,571.19..8,074.60 rows=128 width=56) (actual time=23.000..303.470 rows=1,802 loops=1)

  • Hash Cond: (parent.root_id = reel_1.id)
27. 292.867 292.867 ↓ 1.8 1,802 1

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

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

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

  • Buckets: 16384 Batches: 1 Memory Usage: 864kB
29. 5.109 7.076 ↑ 1.1 13,454 1

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

  • Recheck Cond: (workspace_id = '1f32d99e-2062-43cb-b9a8-7ae13ab7c977'::uuid)
  • Heap Blocks: exact=1909
30. 1.967 1.967 ↑ 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.967..1.967 rows=13,454 loops=1)

  • Index Cond: (workspace_id = '1f32d99e-2062-43cb-b9a8-7ae13ab7c977'::uuid)
31. 0.023 0.206 ↓ 1.0 143 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
32. 0.014 0.183 ↓ 1.0 143 1

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

33. 0.068 0.169 ↓ 1.0 143 1

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

  • Group Key: personal_board_1.board_id
34. 0.057 0.101 ↑ 1.0 143 1

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

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

  • Index Cond: (account_id = '0b4c3803-a3e6-4a65-8901-3de66db88db2'::uuid)
36. 0.292 0.292 ↑ 1.0 1 73

Index Scan using reel_pkey on reel (cost=0.42..8.22 rows=1 width=113) (actual time=0.004..0.004 rows=1 loops=73)

  • Index Cond: (id = reel_1.id)
  • Filter: (workspace_id = '1f32d99e-2062-43cb-b9a8-7ae13ab7c977'::uuid)
37.          

SubPlan (for Merge Right Join)

38. 0.073 6.643 ↑ 1.0 1 73

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

39.          

Initplan (for Result)

40. 0.073 6.570 ↑ 1.0 4 73

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

41. 0.045 6.497 ↑ 6.8 4 73

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

42. 0.174 5.037 ↑ 13.0 4 73

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

43. 0.219 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)

44. 0.292 0.292 ↑ 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.004 rows=1 loops=73)

  • Index Cond: (ancestor_id = reel.id)
45. 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
46. 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
47. 1.415 1.415 ↑ 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.005 rows=1 loops=283)

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