explain.depesz.com

PostgreSQL's explain analyze made readable

Result: So9mg

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 825.820 ↓ 10.0 10 1

Limit (cost=103,785.50..103,852.41 rows=1 width=109) (actual time=825.197..825.820 rows=10 loops=1)

2.          

CTE parent

3. 126.252 500.511 ↑ 3.9 53,052 1

Recursive Union (cost=667.48..81,185.80 rows=204,920 width=80) (actual time=3.494..500.511 rows=53,052 loops=1)

4. 8.814 11.974 ↑ 1.1 13,455 1

Bitmap Heap Scan on reel reel_1 (cost=667.48..3,404.60 rows=14,330 width=80) (actual time=3.485..11.974 rows=13,455 loops=1)

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

Bitmap Index Scan on reel_workspace_id_index (cost=0.00..663.89 rows=14,330 width=0) (actual time=3.160..3.160 rows=13,455 loops=1)

  • Index Cond: (workspace_id = '1f32d99e-2062-43cb-b9a8-7ae13ab7c977'::uuid)
6. 322.913 362.285 ↑ 5.3 3,600 11

Hash Join (cost=3,583.72..7,368.28 rows=19,059 width=80) (actual time=2.290..32.935 rows=3,600 loops=11)

  • Hash Cond: (parent_1.parent_id = r.id)
7. 14.432 14.432 ↑ 29.7 4,823 11

WorkTable Scan on parent parent_1 (cost=0.00..2,866.00 rows=143,300 width=64) (actual time=0.000..1.312 rows=4,823 loops=11)

8. 5.483 24.940 ↑ 1.1 13,455 1

Hash (cost=3,404.60..3,404.60 rows=14,330 width=53) (actual time=24.940..24.940 rows=13,455 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 1217kB
9. 16.297 19.457 ↑ 1.1 13,455 1

Bitmap Heap Scan on reel r (cost=667.48..3,404.60 rows=14,330 width=53) (actual time=3.462..19.457 rows=13,455 loops=1)

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

Bitmap Index Scan on reel_workspace_id_index (cost=0.00..663.89 rows=14,330 width=0) (actual time=3.160..3.160 rows=13,455 loops=1)

  • Index Cond: (workspace_id = '1f32d99e-2062-43cb-b9a8-7ae13ab7c977'::uuid)
11. 0.015 825.814 ↓ 10.0 10 1

Result (cost=22,599.70..22,666.61 rows=1 width=109) (actual time=825.196..825.814 rows=10 loops=1)

12. 0.108 825.089 ↓ 10.0 10 1

Sort (cost=22,599.70..22,599.70 rows=1 width=77) (actual time=825.086..825.089 rows=10 loops=1)

  • Sort Key: reel.updated_at DESC
  • Sort Method: top-N heapsort Memory: 27kB
13. 22.260 824.981 ↓ 44.0 44 1

Hash Right Join (cost=17,731.74..22,599.69 rows=1 width=77) (actual time=457.605..824.981 rows=44 loops=1)

  • Hash Cond: (parent.root_id = reel.id)
  • Filter: (parent.parent_id IS NULL)
  • Rows Removed by Filter: 108
14. 602.520 602.520 ↑ 3.9 53,052 1

CTE Scan on parent (cost=0.00..4,098.40 rows=204,920 width=64) (actual time=3.497..602.520 rows=53,052 loops=1)

15. 0.053 200.201 ↑ 1.3 44 1

Hash (cost=17,731.03..17,731.03 rows=57 width=45) (actual time=200.201..200.201 rows=44 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
16. 197.015 200.148 ↑ 1.3 44 1

Bitmap Heap Scan on reel (cost=663.91..17,731.03 rows=57 width=45) (actual time=9.491..200.148 rows=44 loops=1)

  • Recheck Cond: (workspace_id = '1f32d99e-2062-43cb-b9a8-7ae13ab7c977'::uuid)
  • Filter: ((title @@ '''burger'''::tsquery) OR (title @@ '''burger'''::tsquery) OR (description @@ '''burger'''::tsquery) OR (description @@ '''burger'''::tsquery))
  • Rows Removed by Filter: 13411
  • Heap Blocks: exact=1909
17. 3.133 3.133 ↑ 1.1 13,455 1

Bitmap Index Scan on reel_workspace_id_index (cost=0.00..663.89 rows=14,330 width=0) (actual time=3.133..3.133 rows=13,455 loops=1)

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

SubPlan (for Result)

19. 0.020 0.710 ↑ 1.0 1 10

Result (cost=66.87..66.88 rows=1 width=32) (actual time=0.071..0.071 rows=1 loops=10)

20.          

Initplan (for Result)

21. 0.020 0.690 ↑ 1.0 1 10

Limit (cost=1.71..66.87 rows=1 width=40) (actual time=0.068..0.069 rows=1 loops=10)

22. 0.010 0.670 ↑ 28.0 1 10

Nested Loop (cost=1.71..1,826.16 rows=28 width=40) (actual time=0.067..0.067 rows=1 loops=10)

23. 0.028 0.580 ↑ 53.0 1 10

Nested Loop (cost=1.28..1,729.61 rows=53 width=16) (actual time=0.058..0.058 rows=1 loops=10)

24. 0.040 0.230 ↑ 158.2 5 10

Nested Loop (cost=0.85..674.02 rows=791 width=16) (actual time=0.021..0.023 rows=5 loops=10)

25. 0.070 0.070 ↑ 30.0 1 10

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

  • Index Cond: (ancestor_id = reel.id)
26. 0.120 0.120 ↑ 22.4 5 10

Index Only Scan using reel_clip_pkey on reel_clip (cost=0.43..17.18 rows=112 width=32) (actual time=0.012..0.012 rows=5 loops=10)

  • Index Cond: (reel_id = board_ancestor.id)
  • Heap Fetches: 0
27. 0.322 0.322 ↓ 0.0 0 46

Index Scan using clip_pkey on clip (cost=0.43..1.32 rows=1 width=32) (actual time=0.007..0.007 rows=0 loops=46)

  • 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
28. 0.080 0.080 ↑ 1.0 1 10

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

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