explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lbQE

Settings
# exclusive inclusive rows x rows loops node
1. 14.341 452.933 ↓ 5.0 36,617 1

HashAggregate (cost=177,208.91..177,282.29 rows=7,338 width=16) (actual time=448.535..452.933 rows=36,617 loops=1)

  • Group Key: clip.id
2. 2.422 438.592 ↓ 5.0 36,635 1

Append (cost=13,460.88..177,190.57 rows=7,338 width=16) (actual time=64.351..438.592 rows=36,635 loops=1)

3. 9.313 65.054 ↑ 536.1 7 1

Bitmap Heap Scan on clip (cost=13,460.88..29,319.73 rows=3,753 width=16) (actual time=64.350..65.054 rows=7 loops=1)

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

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

5. 0.002 0.742 ↓ 0.0 0 1

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

6. 0.325 0.325 ↑ 11.1 1,488 1

Bitmap Index Scan on clip_title_description_simple_idx (cost=0.00..179.80 rows=16,506 width=0) (actual time=0.325..0.325 rows=1,488 loops=1)

  • Index Cond: (to_tsvector('simple'::regconfig, ((COALESCE(title, ''::text) || ' '::text) || COALESCE(description, ''::text))) @@ '''drink'''::tsquery)
7. 0.415 0.415 ↑ 7.0 2,374 1

Bitmap Index Scan on clip_title_description_english_idx (cost=0.00..183.80 rows=16,506 width=0) (actual time=0.415..0.415 rows=2,374 loops=1)

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

  • Index Cond: (workspace_id = '1f32d99e-2062-43cb-b9a8-7ae13ab7c977'::uuid)
9. 1.007 333.577 ↑ 21.9 113 1

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

10. 199.452 328.757 ↑ 17.1 1,271 1

Hash Join (cost=840.01..48,899.64 rows=21,701 width=16) (actual time=3.971..328.757 rows=1,271 loops=1)

  • Hash Cond: (clip_tag.tag_id = tag.id)
11. 129.214 129.214 ↓ 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.003..129.214 rows=2,183,893 loops=1)

12. 0.010 0.091 ↑ 7.6 45 1

Hash (cost=835.72..835.72 rows=343 width=16) (actual time=0.091..0.091 rows=45 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
13. 0.049 0.081 ↑ 7.6 45 1

Bitmap Heap Scan on tag (cost=26.75..835.72 rows=343 width=16) (actual time=0.038..0.081 rows=45 loops=1)

  • Recheck Cond: ((to_tsvector('simple'::regconfig, COALESCE(label, ''::text)) @@ '''drink'''::tsquery) OR (to_tsvector('english'::regconfig, COALESCE(label, ''::text)) @@ '''drink'''::tsquery))
  • Heap Blocks: exact=43
14. 0.001 0.032 ↓ 0.0 0 1

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

15. 0.018 0.018 ↑ 7.8 22 1

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

  • Index Cond: (to_tsvector('simple'::regconfig, COALESCE(label, ''::text)) @@ '''drink'''::tsquery)
16. 0.013 0.013 ↑ 3.8 45 1

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

  • Index Cond: (to_tsvector('english'::regconfig, COALESCE(label, ''::text)) @@ '''drink'''::tsquery)
17. 3.813 3.813 ↓ 0.0 0 1,271

Index Scan using clip_pkey on clip clip_1 (cost=0.43..1.20 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=1,271)

  • Index Cond: (id = clip_tag.clip_id)
  • Filter: (workspace_id = '1f32d99e-2062-43cb-b9a8-7ae13ab7c977'::uuid)
  • Rows Removed by Filter: 1
18. 3.709 37.539 ↓ 32.8 36,515 1

Nested Loop (cost=356.20..72,695.26 rows=1,112 width=16) (actual time=12.428..37.539 rows=36,515 loops=1)

19. 2.519 2.901 ↑ 11.0 1 1

Bitmap Heap Scan on smart_tag (cost=309.25..2,561.65 rows=11 width=16) (actual time=2.877..2.901 rows=1 loops=1)

  • Recheck Cond: (workspace_id = '1f32d99e-2062-43cb-b9a8-7ae13ab7c977'::uuid)
  • Filter: (to_tsvector('simple'::regconfig, COALESCE(label, ''::text)) @@ '''drink'''::tsquery)
  • Rows Removed by Filter: 1765
  • Heap Blocks: exact=449
20. 0.382 0.382 ↑ 1.3 1,766 1

Bitmap Index Scan on smart_tag_workspace_id_label_key (cost=0.00..309.24 rows=2,243 width=0) (actual time=0.382..0.382 rows=1,766 loops=1)

  • Index Cond: (workspace_id = '1f32d99e-2062-43cb-b9a8-7ae13ab7c977'::uuid)
21. 24.613 30.929 ↓ 19.7 36,515 1

Bitmap Heap Scan on clip_smart_tag (cost=46.95..6,357.21 rows=1,857 width=32) (actual time=9.549..30.929 rows=36,515 loops=1)

  • Recheck Cond: (smart_tag_id = smart_tag.id)
  • Heap Blocks: exact=19147
22. 6.316 6.316 ↓ 19.7 36,515 1

Bitmap Index Scan on clip_smart_tag_id_index (cost=0.00..46.49 rows=1,857 width=0) (actual time=6.316..6.316 rows=36,515 loops=1)

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