explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EX4g

Settings
# exclusive inclusive rows x rows loops node
1. 0.599 1,525.917 ↑ 5.1 73 1

GroupAggregate (cost=732,203.21..732,240.51 rows=373 width=768) (actual time=1,525.338..1,525.917 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.350 1,525.318 ↑ 2.7 136 1

Sort (cost=732,203.21..732,204.15 rows=373 width=742) (actual time=1,525.308..1,525.318 rows=136 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: 88kB
3. 0.021 1,524.968 ↑ 2.7 136 1

Nested Loop Left Join (cost=731,010.45..732,187.28 rows=373 width=742) (actual time=1,523.899..1,524.968 rows=136 loops=1)

4. 0.038 1,524.363 ↑ 1.0 73 1

Hash Left Join (cost=731,009.73..731,629.26 rows=73 width=703) (actual time=1,523.885..1,524.363 rows=73 loops=1)

  • Hash Cond: (clip.id = clip_bookmark.clip_id)
5. 0.091 1,524.054 ↑ 1.0 73 1

Hash Join (cost=730,951.07..731,570.13 rows=73 width=687) (actual time=1,523.594..1,524.054 rows=73 loops=1)

  • Hash Cond: (clip.account_id = account.id)
6. 0.046 1,520.534 ↑ 1.0 73 1

Nested Loop (cost=730,605.93..731,223.99 rows=73 width=608) (actual time=1,520.108..1,520.534 rows=73 loops=1)

7. 0.017 1,520.123 ↑ 1.0 73 1

Limit (cost=730,605.49..730,605.68 rows=73 width=433) (actual time=1,520.091..1,520.123 rows=73 loops=1)

8. 20.818 1,520.106 ↑ 2,215.6 73 1

Sort (cost=730,605.49..731,009.84 rows=161,740 width=433) (actual time=1,520.090..1,520.106 rows=73 loops=1)

  • Sort Key: clip.recorded_at DESC, clip.id DESC
  • Sort Method: top-N heapsort Memory: 44kB
9. 19.838 1,499.288 ↑ 3.7 43,681 1

Nested Loop (cost=636,925.38..724,791.08 rows=161,740 width=433) (actual time=1,217.003..1,499.288 rows=43,681 loops=1)

10. 38.182 1,236.242 ↓ 5.9 60,802 1

HashAggregate (cost=636,924.95..637,028.78 rows=10,383 width=16) (actual time=1,216.970..1,236.242 rows=60,802 loops=1)

  • Group Key: clip_1.id
11. 4.636 1,198.060 ↓ 5.9 60,802 1

Append (cost=13,556.88..636,898.99 rows=10,383 width=16) (actual time=1,154.576..1,198.060 rows=60,802 loops=1)

12. 6.346 68.124 ↓ 0.0 0 1

Bitmap Heap Scan on clip clip_1 (cost=13,556.88..29,415.73 rows=3,753 width=16) (actual time=68.124..68.124 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
13. 0.797 61.778 ↓ 0.0 0 1

BitmapAnd (cost=13,556.88..13,556.88 rows=3,762 width=0) (actual time=61.778..61.778 rows=0 loops=1)

14. 0.000 1.195 ↓ 0.0 0 1

BitmapOr (cost=461.47..461.47 rows=33,013 width=0) (actual time=1.195..1.195 rows=0 loops=1)

15. 0.522 0.522 ↑ 10.2 1,623 1

Bitmap Index Scan on clip_title_description_simple_idx (cost=0.00..227.80 rows=16,506 width=0) (actual time=0.522..0.522 rows=1,623 loops=1)

  • Index Cond: (to_tsvector('simple'::regconfig, ((COALESCE(title, ''::text) || ' '::text) || COALESCE(description, ''::text))) @@ '''restaurant'''::tsquery)
16. 0.674 0.674 ↑ 5.7 2,896 1

Bitmap Index Scan on clip_title_description_english_idx (cost=0.00..231.80 rows=16,506 width=0) (actual time=0.673..0.674 rows=2,896 loops=1)

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

Bitmap Index Scan on clip_workspace_id_index (cost=0.00..13,094.22 rows=376,238 width=0) (actual time=59.786..59.786 rows=377,031 loops=1)

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

Hash Join (cost=15,462.79..421,906.13 rows=1,864 width=16) (actual time=15.455..15.455 rows=0 loops=1)

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

Bitmap Heap Scan on clip clip_2 (cost=13,188.27..418,202.09 rows=376,238 width=32) (never executed)

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

Bitmap Index Scan on clip_workspace_id_index (cost=0.00..13,094.22 rows=376,238 width=0) (never executed)

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

Hash (cost=2,274.03..2,274.03 rows=39 width=16) (actual time=15.444..15.444 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
22. 15.442 15.442 ↓ 0.0 0 1

Seq Scan on account account_1 (cost=0.00..2,274.03 rows=39 width=16) (actual time=15.442..15.442 rows=0 loops=1)

  • Filter: (to_tsvector('simple'::regconfig, ((COALESCE(first_name, ''::text) || ' '::text) || COALESCE(last_name, ''::text))) @@ '''restaurant'''::tsquery)
  • Rows Removed by Filter: 7,876
23. 5.375 1,056.906 ↓ 0.0 0 1

Nested Loop (cost=840.44..75,102.20 rows=2,473 width=16) (actual time=1,056.906..1,056.906 rows=0 loops=1)

24. 257.564 398.219 ↓ 7.5 163,328 1

Hash Join (cost=840.01..48,899.64 rows=21,701 width=16) (actual time=2.733..398.219 rows=163,328 loops=1)

  • Hash Cond: (clip_tag_1.tag_id = tag_1.id)
25. 140.452 140.452 ↓ 1.0 2,183,893 1

Seq Scan on clip_tag clip_tag_1 (cost=0.00..39,692.81 rows=2,173,281 width=32) (actual time=0.005..140.452 rows=2,183,893 loops=1)

26. 0.017 0.203 ↑ 3.1 112 1

Hash (cost=835.72..835.72 rows=343 width=16) (actual time=0.203..0.203 rows=112 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 14kB
27. 0.115 0.186 ↑ 3.1 112 1

Bitmap Heap Scan on tag tag_1 (cost=26.75..835.72 rows=343 width=16) (actual time=0.085..0.186 rows=112 loops=1)

  • Recheck Cond: ((to_tsvector('simple'::regconfig, COALESCE(label, ''::text)) @@ '''restaurant'''::tsquery) OR (to_tsvector('english'::regconfig, COALESCE(label, ''::text)) @@ '''restaur'''::tsquery))
  • Heap Blocks: exact=86
28. 0.001 0.071 ↓ 0.0 0 1

BitmapOr (cost=26.75..26.75 rows=344 width=0) (actual time=0.071..0.071 rows=0 loops=1)

29. 0.043 0.043 ↑ 2.1 82 1

Bitmap Index Scan on tag_label_simple_idx (cost=0.00..13.29 rows=172 width=0) (actual time=0.043..0.043 rows=82 loops=1)

  • Index Cond: (to_tsvector('simple'::regconfig, COALESCE(label, ''::text)) @@ '''restaurant'''::tsquery)
30. 0.027 0.027 ↑ 1.5 112 1

Bitmap Index Scan on tag_label_english_idx (cost=0.00..13.29 rows=172 width=0) (actual time=0.027..0.027 rows=112 loops=1)

  • Index Cond: (to_tsvector('english'::regconfig, COALESCE(label, ''::text)) @@ '''restaur'''::tsquery)
31. 653.312 653.312 ↓ 0.0 0 163,328

Index Scan using clip_pkey on clip clip_3 (cost=0.43..1.20 rows=1 width=16) (actual time=0.004..0.004 rows=0 loops=163,328)

  • Index Cond: (id = clip_tag_1.clip_id)
  • Filter: (workspace_id = '1f32d99e-2062-43cb-b9a8-7ae13ab7c977'::uuid)
  • Rows Removed by Filter: 1
32. 6.008 52.939 ↓ 26.5 60,802 1

Nested Loop (cost=409.02..110,371.09 rows=2,293 width=16) (actual time=14.088..52.939 rows=60,802 loops=1)

33. 0.007 0.679 ↑ 18.0 1 1

Bitmap Heap Scan on smart_tag (cost=362.07..437.61 rows=18 width=16) (actual time=0.678..0.679 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
34. 0.016 0.672 ↓ 0.0 0 1

BitmapAnd (cost=362.07..362.07 rows=18 width=0) (actual time=0.672..0.672 rows=0 loops=1)

35. 0.002 0.262 ↓ 0.0 0 1

BitmapOr (cost=56.04..56.04 rows=1,071 width=0) (actual time=0.262..0.262 rows=0 loops=1)

36. 0.146 0.146 ↑ 3.1 175 1

Bitmap Index Scan on smart_tag_label_simple_idx (cost=0.00..28.02 rows=535 width=0) (actual time=0.146..0.146 rows=175 loops=1)

  • Index Cond: (to_tsvector('simple'::regconfig, COALESCE(label, ''::text)) @@ '''restaurant'''::tsquery)
37. 0.114 0.114 ↑ 3.1 175 1

Bitmap Index Scan on smart_tag_label_english_idx (cost=0.00..28.02 rows=535 width=0) (actual time=0.114..0.114 rows=175 loops=1)

  • Index Cond: (to_tsvector('english'::regconfig, COALESCE(label, ''::text)) @@ '''restaur'''::tsquery)
38. 0.394 0.394 ↑ 1.0 1,766 1

Bitmap Index Scan on smart_tag_workspace_id_label_key (cost=0.00..305.78 rows=1,781 width=0) (actual time=0.394..0.394 rows=1,766 loops=1)

  • Index Cond: (workspace_id = '1f32d99e-2062-43cb-b9a8-7ae13ab7c977'::uuid)
39. 36.853 46.252 ↓ 32.7 60,802 1

Bitmap Heap Scan on clip_smart_tag (cost=46.95..6,088.85 rows=1,857 width=32) (actual time=13.401..46.252 rows=60,802 loops=1)

  • Recheck Cond: (smart_tag_id = smart_tag.id)
  • Heap Blocks: exact=23,146
40. 9.399 9.399 ↓ 32.7 60,802 1

Bitmap Index Scan on clip_smart_tag_id_index (cost=0.00..46.49 rows=1,857 width=0) (actual time=9.399..9.399 rows=60,802 loops=1)

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

Index Scan using clip_pkey on clip (cost=0.43..8.43 rows=1 width=433) (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
42. 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.005..0.005 rows=1 loops=73)

  • Index Cond: (id = clip.media_id)
43. 1.692 3.429 ↓ 1.0 7,876 1

Hash (cost=246.73..246.73 rows=7,873 width=95) (actual time=3.429..3.429 rows=7,876 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 552kB
44. 1.737 1.737 ↓ 1.0 7,876 1

Seq Scan on account (cost=0.00..246.73 rows=7,873 width=95) (actual time=0.005..1.737 rows=7,876 loops=1)

45. 0.006 0.271 ↓ 4.0 12 1

Hash (cost=58.62..58.62 rows=3 width=16) (actual time=0.271..0.271 rows=12 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
46. 0.265 0.265 ↓ 4.0 12 1

Seq Scan on clip_bookmark (cost=0.00..58.62 rows=3 width=16) (actual time=0.230..0.265 rows=12 loops=1)

  • Filter: (account_id = '42fa284b-cd72-4c9a-83e7-3638eb822398'::uuid)
  • Rows Removed by Filter: 2,764
47. 0.118 0.584 ↑ 5.0 1 73

Nested Loop Left Join (cost=0.72..7.59 rows=5 width=55) (actual time=0.005..0.008 rows=1 loops=73)

48. 0.292 0.292 ↑ 5.0 1 73

Index Only Scan using clip_tag_clip_id_tag_id_key on clip_tag (cost=0.43..6.00 rows=5 width=32) (actual time=0.004..0.004 rows=1 loops=73)

  • Index Cond: (clip_id = clip.id)
  • Heap Fetches: 87
49. 0.174 0.174 ↑ 1.0 1 87

Index Scan using tag_pkey on tag (cost=0.29..0.31 rows=1 width=39) (actual time=0.002..0.002 rows=1 loops=87)

  • Index Cond: (id = clip_tag.tag_id)