explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VBg9

Settings
# exclusive inclusive rows x rows loops node
1. 0.660 770.664 ↑ 8.0 1,296 1

HashAggregate (cost=636,836.63..636,940.46 rows=10,383 width=16) (actual time=770.397..770.664 rows=1,296 loops=1)

  • Group Key: clip.id
2. 0.104 770.004 ↑ 8.0 1,296 1

Append (cost=13,484.88..636,810.68 rows=10,383 width=16) (actual time=255.436..770.004 rows=1,296 loops=1)

3. 1.089 58.086 ↓ 0.0 0 1

Bitmap Heap Scan on clip (cost=13,484.88..29,343.73 rows=3,753 width=16) (actual time=58.086..58.086 rows=0 loops=1)

  • Recheck Cond: (((to_tsvector('simple'::regconfig, ((COALESCE(title, ''::text) || ' '::text) || COALESCE(description, ''::text))) @@ '''emily'''::tsquery) OR (to_tsvector('english'::regconfig, ((COALESCE(title, ''::text) || ' '::text) || COALESCE(description, ''::text))) @@ '''emili'''::tsquery)) AND (workspace_id = '1f32d99e-2062-43cb-b9a8-7ae13ab7c977'::uuid))
  • Rows Removed by Index Recheck: 20
  • Heap Blocks: exact=20
4. 0.342 56.997 ↓ 0.0 0 1

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

5. 0.002 0.254 ↓ 0.0 0 1

BitmapOr (cost=389.47..389.47 rows=33,013 width=0) (actual time=0.253..0.254 rows=0 loops=1)

6. 0.126 0.126 ↑ 77.9 212 1

Bitmap Index Scan on clip_title_description_simple_idx (cost=0.00..191.80 rows=16,506 width=0) (actual time=0.126..0.126 rows=212 loops=1)

  • Index Cond: (to_tsvector('simple'::regconfig, ((COALESCE(title, ''::text) || ' '::text) || COALESCE(description, ''::text))) @@ '''emily'''::tsquery)
7. 0.126 0.126 ↑ 74.7 221 1

Bitmap Index Scan on clip_title_description_english_idx (cost=0.00..195.80 rows=16,506 width=0) (actual time=0.126..0.126 rows=221 loops=1)

  • Index Cond: (to_tsvector('english'::regconfig, ((COALESCE(title, ''::text) || ' '::text) || COALESCE(description, ''::text))) @@ '''emili'''::tsquery)
8. 56.401 56.401 ↓ 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=56.401..56.401 rows=377,031 loops=1)

  • Index Cond: (workspace_id = '1f32d99e-2062-43cb-b9a8-7ae13ab7c977'::uuid)
9. 28.258 342.905 ↑ 1.4 1,296 1

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

  • Hash Cond: (clip_1.account_id = account.id)
10. 244.163 299.345 ↓ 1.0 377,031 1

Bitmap Heap Scan on clip clip_1 (cost=13,188.27..418,202.09 rows=376,238 width=32) (actual time=61.666..299.345 rows=377,031 loops=1)

  • Recheck Cond: (workspace_id = '1f32d99e-2062-43cb-b9a8-7ae13ab7c977'::uuid)
  • Rows Removed by Index Recheck: 161353
  • Heap Blocks: exact=33897 lossy=53151
11. 55.182 55.182 ↓ 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=55.182..55.182 rows=377,031 loops=1)

  • Index Cond: (workspace_id = '1f32d99e-2062-43cb-b9a8-7ae13ab7c977'::uuid)
12. 0.013 15.302 ↑ 1.2 32 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
13. 15.289 15.289 ↑ 1.2 32 1

Seq Scan on account (cost=0.00..2,274.03 rows=39 width=16) (actual time=0.542..15.289 rows=32 loops=1)

  • Filter: (to_tsvector('simple'::regconfig, ((COALESCE(first_name, ''::text) || ' '::text) || COALESCE(last_name, ''::text))) @@ '''emily'''::tsquery)
  • Rows Removed by Filter: 7841
14. 1.229 368.841 ↓ 0.0 0 1

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

15. 206.125 344.908 ↑ 3.8 5,676 1

Hash Join (cost=840.01..48,899.64 rows=21,701 width=16) (actual time=4.504..344.908 rows=5,676 loops=1)

  • Hash Cond: (clip_tag.tag_id = tag.id)
16. 138.637 138.637 ↓ 1.0 2,183,893 1

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

17. 0.017 0.146 ↑ 4.5 77 1

Hash (cost=835.72..835.72 rows=343 width=16) (actual time=0.146..0.146 rows=77 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
18. 0.077 0.129 ↑ 4.5 77 1

Bitmap Heap Scan on tag (cost=26.75..835.72 rows=343 width=16) (actual time=0.063..0.129 rows=77 loops=1)

  • Recheck Cond: ((to_tsvector('simple'::regconfig, COALESCE(label, ''::text)) @@ '''emily'''::tsquery) OR (to_tsvector('english'::regconfig, COALESCE(label, ''::text)) @@ '''emili'''::tsquery))
  • Heap Blocks: exact=65
19. 0.000 0.052 ↓ 0.0 0 1

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

20. 0.035 0.035 ↑ 2.2 77 1

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

  • Index Cond: (to_tsvector('simple'::regconfig, COALESCE(label, ''::text)) @@ '''emily'''::tsquery)
21. 0.017 0.017 ↑ 2.2 77 1

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

  • Index Cond: (to_tsvector('english'::regconfig, COALESCE(label, ''::text)) @@ '''emili'''::tsquery)
22. 22.704 22.704 ↓ 0.0 0 5,676

Index Scan using clip_pkey on clip clip_2 (cost=0.43..1.20 rows=1 width=16) (actual time=0.004..0.004 rows=0 loops=5,676)

  • Index Cond: (id = clip_tag.clip_id)
  • Filter: (workspace_id = '1f32d99e-2062-43cb-b9a8-7ae13ab7c977'::uuid)
  • Rows Removed by Filter: 1
23. 0.001 0.068 ↓ 0.0 0 1

Nested Loop (cost=393.02..110,354.78 rows=2,293 width=16) (actual time=0.068..0.068 rows=0 loops=1)

24. 0.001 0.067 ↓ 0.0 0 1

Bitmap Heap Scan on smart_tag (cost=346.07..421.61 rows=18 width=16) (actual time=0.067..0.067 rows=0 loops=1)

  • Recheck Cond: (((to_tsvector('simple'::regconfig, COALESCE(label, ''::text)) @@ '''emily'''::tsquery) OR (to_tsvector('english'::regconfig, COALESCE(label, ''::text)) @@ '''emili'''::tsquery)) AND (workspace_id = '1f32d99e-2062-43cb-b9a8-7ae13ab7c977'::uuid))
25. 0.001 0.066 ↓ 0.0 0 1

BitmapAnd (cost=346.07..346.07 rows=18 width=0) (actual time=0.066..0.066 rows=0 loops=1)

26. 0.001 0.065 ↓ 0.0 0 1

BitmapOr (cost=40.04..40.04 rows=1,071 width=0) (actual time=0.065..0.065 rows=0 loops=1)

27. 0.036 0.036 ↓ 0.0 0 1

Bitmap Index Scan on smart_tag_label_simple_idx (cost=0.00..20.02 rows=535 width=0) (actual time=0.036..0.036 rows=0 loops=1)

  • Index Cond: (to_tsvector('simple'::regconfig, COALESCE(label, ''::text)) @@ '''emily'''::tsquery)
28. 0.028 0.028 ↓ 0.0 0 1

Bitmap Index Scan on smart_tag_label_english_idx (cost=0.00..20.02 rows=535 width=0) (actual time=0.028..0.028 rows=0 loops=1)

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

Bitmap Index Scan on smart_tag_workspace_id_label_key (cost=0.00..305.78 rows=1,781 width=0) (never executed)

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

Bitmap Heap Scan on clip_smart_tag (cost=46.95..6,088.83 rows=1,857 width=32) (never executed)

  • Recheck Cond: (smart_tag_id = smart_tag.id)
31. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on clip_smart_tag_id_index (cost=0.00..46.49 rows=1,857 width=0) (never executed)

  • Index Cond: (smart_tag_id = smart_tag.id)
Planning time : 1.463 ms