56 lines
1.4 KiB
Markdown
56 lines
1.4 KiB
Markdown
---
|
|
title: Use tsvector for Full-Text Search
|
|
impact: MEDIUM
|
|
impactDescription: 100x faster than LIKE, with ranking support
|
|
tags: full-text-search, tsvector, gin, search
|
|
---
|
|
|
|
## Use tsvector for Full-Text Search
|
|
|
|
LIKE with wildcards can't use indexes. Full-text search with tsvector is orders of magnitude faster.
|
|
|
|
**Incorrect (LIKE pattern matching):**
|
|
|
|
```sql
|
|
-- Cannot use index, scans all rows
|
|
select * from articles where content like '%postgresql%';
|
|
|
|
-- Case-insensitive makes it worse
|
|
select * from articles where lower(content) like '%postgresql%';
|
|
```
|
|
|
|
**Correct (full-text search with tsvector):**
|
|
|
|
```sql
|
|
-- Add tsvector column and index
|
|
alter table articles add column search_vector tsvector
|
|
generated always as (to_tsvector('english', coalesce(title,'') || ' ' || coalesce(content,''))) stored;
|
|
|
|
create index articles_search_idx on articles using gin (search_vector);
|
|
|
|
-- Fast full-text search
|
|
select * from articles
|
|
where search_vector @@ to_tsquery('english', 'postgresql & performance');
|
|
|
|
-- With ranking
|
|
select *, ts_rank(search_vector, query) as rank
|
|
from articles, to_tsquery('english', 'postgresql') query
|
|
where search_vector @@ query
|
|
order by rank desc;
|
|
```
|
|
|
|
Search multiple terms:
|
|
|
|
```sql
|
|
-- AND: both terms required
|
|
to_tsquery('postgresql & performance')
|
|
|
|
-- OR: either term
|
|
to_tsquery('postgresql | mysql')
|
|
|
|
-- Prefix matching
|
|
to_tsquery('post:*')
|
|
```
|
|
|
|
Reference: [Full Text Search](https://supabase.com/docs/guides/database/full-text-search)
|