explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GTlP

Settings
# exclusive inclusive rows x rows loops node
1. 0.239 487.416 ↑ 1.0 73 1

Group (cost=588,957.95..588,963.97 rows=73 width=748) (actual time=487.184..487.416 rows=73 loops=1)

  • Group Key: clip.recorded_at, clip.id, clip.account_id, clip.workspace_id, clip.width, clip.height, clip.size, clip.ext, clip.type, clip.duration, clip.rotation, clip.audio, clip.remote, clip.status, clip.created_at, clip.updated_at, clip.digitized, clip.hash, clip.synced, clip.title, clip.description, clip.source, clip.imported_name, clip_media.playback, clip_media.preview, clip_media.seek, clip_media.image, account.first_name, account.last_name, account.avatar, clip_bookmark.clip_id
2. 0.187 487.177 ↑ 1.0 73 1

Sort (cost=588,957.95..588,958.13 rows=73 width=715) (actual time=487.167..487.177 rows=73 loops=1)

  • Sort Key: clip.recorded_at DESC, clip.id DESC, clip.account_id, clip.workspace_id, clip.width, clip.height, clip.size, clip.ext, clip.type, clip.duration, clip.rotation, clip.audio, clip.remote, clip.status, clip.created_at, clip.updated_at, clip.digitized, clip.hash, clip.synced, clip.title, clip.description, clip.source, clip.imported_name, clip_media.playback, clip_media.preview, clip_media.seek, clip_media.image, account.first_name, account.last_name, account.avatar, clip_bookmark.clip_id
  • Sort Method: quicksort Memory: 51kB
3. 0.066 486.990 ↑ 1.0 73 1

Hash Join (cost=588,271.29..588,955.69 rows=73 width=715) (actual time=486.600..486.990 rows=73 loops=1)

  • Hash Cond: (clip.account_id = account.id)
4. 0.005 480.464 ↑ 1.0 73 1

Nested Loop (cost=587,921.76..588,605.15 rows=73 width=637) (actual time=480.089..480.464 rows=73 loops=1)

5. 0.038 480.094 ↑ 1.0 73 1

Hash Right Join (cost=587,921.33..587,987.57 rows=73 width=462) (actual time=480.076..480.094 rows=73 loops=1)

  • Hash Cond: (clip_bookmark.clip_id = clip.id)
6. 0.316 0.316 ↑ 1.0 66 1

Seq Scan on clip_bookmark (cost=0.00..65.97 rows=66 width=16) (actual time=0.168..0.316 rows=66 loops=1)

  • Filter: (account_id = '0b4c3803-a3e6-4a65-8901-3de66db88db2'::uuid)
  • Rows Removed by Filter: 3233
7. 0.029 479.740 ↑ 1.0 73 1

Hash (cost=587,920.42..587,920.42 rows=73 width=446) (actual time=479.740..479.740 rows=73 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
8. 0.019 479.711 ↑ 1.0 73 1

Limit (cost=587,919.50..587,919.69 rows=73 width=446) (actual time=479.681..479.711 rows=73 loops=1)

9. 21.093 479.692 ↑ 2,270.3 73 1

Sort (cost=587,919.50..588,333.83 rows=165,730 width=446) (actual time=479.679..479.692 rows=73 loops=1)

  • Sort Key: clip.recorded_at DESC, clip.id DESC
  • Sort Method: top-N heapsort Memory: 44kB
10. 18.590 458.599 ↑ 3.8 43,681 1

Nested Loop (cost=524,713.27..581,961.66 rows=165,730 width=446) (actual time=177.861..458.599 rows=43,681 loops=1)

11. 41.371 196.801 ↓ 9.0 60,802 1

HashAggregate (cost=524,712.84..524,780.49 rows=6,765 width=16) (actual time=177.828..196.801 rows=60,802 loops=1)

  • Group Key: clip_1.id
12. 5.017 155.430 ↓ 9.0 60,802 1

Append (cost=14,338.04..524,695.93 rows=6,765 width=16) (actual time=104.290..155.430 rows=60,802 loops=1)

13. 6.462 73.415 ↓ 0.0 0 1

Bitmap Heap Scan on clip clip_1 (cost=14,338.04..30,646.62 rows=3,856 width=16) (actual time=73.415..73.415 rows=0 loops=1)

  • Recheck Cond: (((to_tsvector('simple'::regconfig, ((COALESCE(title, ''::text) || ' '::text) || COALESCE(description, ''::text))) @@ '''restaurant'''::tsquery) OR (to_tsvector('english'::regconfig, ((COALESCE(title, ''::text) || ' '::text) || COALESCE(description, ''::text))) @@ '''restaur'''::tsquery)) AND (workspace_id = '1f32d99e-2062-43cb-b9a8-7ae13ab7c977'::uuid))
  • Rows Removed by Index Recheck: 58
  • Heap Blocks: exact=56
14. 0.780 66.953 ↓ 0.0 0 1

BitmapAnd (cost=14,338.04..14,338.04 rows=3,866 width=0) (actual time=66.953..66.953 rows=0 loops=1)

15. 0.003 3.118 ↓ 0.0 0 1

BitmapOr (cost=941.17..941.17 rows=34,566 width=0) (actual time=3.118..3.118 rows=0 loops=1)

16. 1.445 1.445 ↑ 9.5 1,818 1

Bitmap Index Scan on workspace_clip_title_description_simple_idx (cost=0.00..517.62 rows=17,283 width=0) (actual time=1.445..1.445 rows=1,818 loops=1)

  • Index Cond: (to_tsvector('simple'::regconfig, ((COALESCE(title, ''::text) || ' '::text) || COALESCE(description, ''::text))) @@ '''restaurant'''::tsquery)
17. 1.670 1.670 ↑ 5.4 3,216 1

Bitmap Index Scan on workspace_clip_title_description_english_idx (cost=0.00..421.62 rows=17,283 width=0) (actual time=1.670..1.670 rows=3,216 loops=1)

  • Index Cond: (to_tsvector('english'::regconfig, ((COALESCE(title, ''::text) || ' '::text) || COALESCE(description, ''::text))) @@ '''restaur'''::tsquery)
18. 63.055 63.055 ↑ 1.0 377,031 1

Bitmap Index Scan on clip_workspace_id_index (cost=0.00..13,395.65 rows=386,563 width=0) (actual time=63.055..63.055 rows=377,031 loops=1)

  • Index Cond: (workspace_id = '1f32d99e-2062-43cb-b9a8-7ae13ab7c977'::uuid)
19. 0.010 16.824 ↓ 0.0 0 1

Hash Join (cost=15,818.98..439,363.49 rows=1,917 width=16) (actual time=16.824..16.824 rows=0 loops=1)

  • Hash Cond: (clip_2.account_id = account_1.id)
20. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on clip clip_2 (cost=13,492.29..435,568.02 rows=386,563 width=32) (never executed)

  • Recheck Cond: (workspace_id = '1f32d99e-2062-43cb-b9a8-7ae13ab7c977'::uuid)
21. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on clip_workspace_id_index (cost=0.00..13,395.65 rows=386,563 width=0) (never executed)

  • Index Cond: (workspace_id = '1f32d99e-2062-43cb-b9a8-7ae13ab7c977'::uuid)
22. 0.000 16.814 ↓ 0.0 0 1

Hash (cost=2,326.19..2,326.19 rows=40 width=16) (actual time=16.814..16.814 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
23. 16.815 16.815 ↓ 0.0 0 1

Seq Scan on account account_1 (cost=0.00..2,326.19 rows=40 width=16) (actual time=16.814..16.815 rows=0 loops=1)

  • Filter: (to_tsvector('simple'::regconfig, ((COALESCE(first_name, ''::text) || ' '::text) || COALESCE(last_name, ''::text))) @@ '''restaurant'''::tsquery)
  • Rows Removed by Filter: 8488
24. 0.001 0.090 ↓ 0.0 0 1

Nested Loop (cost=50.89..1,307.23 rows=62 width=16) (actual time=0.090..0.090 rows=0 loops=1)

25. 0.000 0.089 ↓ 0.0 0 1

Nested Loop (cost=50.46..1,275.54 rows=62 width=16) (actual time=0.089..0.089 rows=0 loops=1)

26. 0.003 0.089 ↓ 0.0 0 1

Bitmap Heap Scan on tag (cost=39.38..43.90 rows=1 width=16) (actual time=0.089..0.089 rows=0 loops=1)

  • Recheck Cond: ((workspace_id = '1f32d99e-2062-43cb-b9a8-7ae13ab7c977'::uuid) AND ((to_tsvector('simple'::regconfig, COALESCE(label, ''::text)) @@ '''restaurant'''::tsquery) OR (to_tsvector('english'::regconfig, COALESCE(label, ''::text)) @@ '''restaur'''::tsquery)))
27. 0.003 0.086 ↓ 0.0 0 1

BitmapAnd (cost=39.38..39.38 rows=1 width=0) (actual time=0.086..0.086 rows=0 loops=1)

28. 0.025 0.025 ↑ 1.0 29 1

Bitmap Index Scan on tag_workspace_id_index (cost=0.00..4.51 rows=29 width=0) (actual time=0.025..0.025 rows=29 loops=1)

  • Index Cond: (workspace_id = '1f32d99e-2062-43cb-b9a8-7ae13ab7c977'::uuid)
29. 0.003 0.058 ↓ 0.0 0 1

BitmapOr (cost=34.63..34.63 rows=350 width=0) (actual time=0.057..0.058 rows=0 loops=1)

30. 0.031 0.031 ↑ 2.1 82 1

Bitmap Index Scan on tag_label_simple_idx (cost=0.00..17.31 rows=175 width=0) (actual time=0.031..0.031 rows=82 loops=1)

  • Index Cond: (to_tsvector('simple'::regconfig, COALESCE(label, ''::text)) @@ '''restaurant'''::tsquery)
31. 0.024 0.024 ↑ 1.6 112 1

Bitmap Index Scan on tag_label_english_idx (cost=0.00..17.31 rows=175 width=0) (actual time=0.024..0.024 rows=112 loops=1)

  • Index Cond: (to_tsvector('english'::regconfig, COALESCE(label, ''::text)) @@ '''restaur'''::tsquery)
32. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on clip_tag (cost=11.07..1,228.23 rows=341 width=32) (never executed)

  • Recheck Cond: (tag_id = tag.id)
33. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on clip_tag_id_index (cost=0.00..10.99 rows=341 width=0) (never executed)

  • Index Cond: (tag_id = tag.id)
34. 0.000 0.000 ↓ 0.0 0

Index Only Scan using clip_pkey on clip clip_3 (cost=0.43..0.50 rows=1 width=16) (never executed)

  • Index Cond: (id = clip_tag.clip_id)
  • Heap Fetches: 0
35. 7.748 60.084 ↓ 65.4 60,802 1

Nested Loop (cost=426.35..53,310.93 rows=930 width=16) (actual time=13.959..60.084 rows=60,802 loops=1)

36. 0.007 0.503 ↑ 8.0 1 1

Bitmap Heap Scan on smart_tag (cost=375.23..409.66 rows=8 width=16) (actual time=0.502..0.503 rows=1 loops=1)

  • Recheck Cond: (((to_tsvector('simple'::regconfig, COALESCE(label, ''::text)) @@ '''restaurant'''::tsquery) OR (to_tsvector('english'::regconfig, COALESCE(label, ''::text)) @@ '''restaur'''::tsquery)) AND (workspace_id = '1f32d99e-2062-43cb-b9a8-7ae13ab7c977'::uuid))
  • Heap Blocks: exact=1
37. 0.017 0.496 ↓ 0.0 0 1

BitmapAnd (cost=375.23..375.23 rows=8 width=0) (actual time=0.496..0.496 rows=0 loops=1)

38. 0.000 0.101 ↓ 0.0 0 1

BitmapOr (cost=35.66..35.66 rows=488 width=0) (actual time=0.101..0.101 rows=0 loops=1)

39. 0.053 0.053 ↑ 1.3 192 1

Bitmap Index Scan on smart_tag_label_simple_idx (cost=0.00..17.83 rows=244 width=0) (actual time=0.053..0.053 rows=192 loops=1)

  • Index Cond: (to_tsvector('simple'::regconfig, COALESCE(label, ''::text)) @@ '''restaurant'''::tsquery)
40. 0.048 0.048 ↑ 1.3 192 1

Bitmap Index Scan on smart_tag_label_english_idx (cost=0.00..17.83 rows=244 width=0) (actual time=0.048..0.048 rows=192 loops=1)

  • Index Cond: (to_tsvector('english'::regconfig, COALESCE(label, ''::text)) @@ '''restaur'''::tsquery)
41. 0.378 0.378 ↑ 1.1 1,766 1

Bitmap Index Scan on smart_tag_workspace_id_label_key (cost=0.00..339.32 rows=1,987 width=0) (actual time=0.378..0.378 rows=1,766 loops=1)

  • Index Cond: (workspace_id = '1f32d99e-2062-43cb-b9a8-7ae13ab7c977'::uuid)
42. 42.387 51.833 ↓ 32.4 60,802 1

Bitmap Heap Scan on clip_smart_tag (cost=51.12..6,593.87 rows=1,879 width=32) (actual time=13.450..51.833 rows=60,802 loops=1)

  • Recheck Cond: (smart_tag_id = smart_tag.id)
  • Heap Blocks: exact=23146
43. 9.446 9.446 ↓ 32.4 60,802 1

Bitmap Index Scan on clip_smart_tag_id_index (cost=0.00..50.65 rows=1,879 width=0) (actual time=9.446..9.446 rows=60,802 loops=1)

  • Index Cond: (smart_tag_id = smart_tag.id)
44. 243.208 243.208 ↑ 1.0 1 60,802

Index Scan using clip_pkey on clip (cost=0.43..8.43 rows=1 width=446) (actual time=0.004..0.004 rows=1 loops=60,802)

  • Index Cond: (id = clip_1.id)
  • Filter: (visible AND (NOT inactive) AND (status <> 'created'::text) AND (workspace_id = '1f32d99e-2062-43cb-b9a8-7ae13ab7c977'::uuid))
  • Rows Removed by Filter: 0
45. 0.365 0.365 ↑ 1.0 1 73

Index Scan using clip_media_pkey on clip_media (cost=0.43..8.45 rows=1 width=207) (actual time=0.004..0.005 rows=1 loops=73)

  • Index Cond: (id = clip.media_id)
46. 3.257 6.460 ↓ 1.1 8,488 1

Hash (cost=248.68..248.68 rows=8,068 width=94) (actual time=6.460..6.460 rows=8,488 loops=1)

  • Buckets: 16384 (originally 8192) Batches: 1 (originally 1) Memory Usage: 656kB
47. 3.203 3.203 ↓ 1.1 8,488 1

Seq Scan on account (cost=0.00..248.68 rows=8,068 width=94) (actual time=0.006..3.203 rows=8,488 loops=1)

Planning time : 7.337 ms