PostgreSQL query optimization for full-text search

Here are the key optimizations for PostgreSQL full-text search:

1. Add a GIN index on the tsvector expression:

sqlCopy
CREATE INDEX idx_content_fts
ON messages
USING GIN (to_tsvector('simple', content));

2. Use the 'simple' configuration for multilingual content (Chinese + English). The 'simple' tokenizer splits on whitespace and lowercases — it works for both languages without needing dictionaries.

3. Pre-compute tsvector if your data doesn't change often:

sqlCopy
ALTER TABLE messages ADD COLUMN content_tsv tsvector
  GENERATED ALWAYS AS (to_tsvector('simple', content)) STORED;

CREATE INDEX idx_content_tsv ON messages USING GIN (content_tsv);

4. Use websearch_to_tsquery for user-friendly search input:

sqlCopy
SELECT * FROM messages
WHERE content_tsv @@ websearch_to_tsquery('simple', 'react hooks state')
ORDER BY ts_rank(content_tsv, websearch_to_tsquery('simple', 'react hooks state')) DESC
LIMIT 20;

This should reduce query time from seconds to milliseconds on 100K+ rows.

Great question. For combined filtering, use a composite index or rely on PostgreSQL's bitmap scan to combine indexes efficiently:

sqlCopy
-- Option 1: Separate indexes (PostgreSQL combines via BitmapAnd)
CREATE INDEX idx_conv_platform ON conversations (platform);
CREATE INDEX idx_conv_created ON conversations (created_at DESC);
CREATE INDEX idx_conv_title_fts ON conversations USING GIN (to_tsvector('simple', title));

-- Query
SELECT c.*, ts_rank(to_tsvector('simple', c.title), q) AS rank
FROM conversations c, websearch_to_tsquery('simple', :search) q
WHERE to_tsvector('simple', c.title) @@ q
  AND c.platform = :platform
  AND c.created_at >= :start_date
ORDER BY rank DESC
LIMIT 20;

Key insight: PostgreSQL will automatically combine the GIN index with B-tree indexes via bitmap scans. You don't need a single composite index — separate indexes often perform just as well and are more flexible.

Use EXPLAIN ANALYZE to verify your queries hit the indexes.