Message search cannot use any index: @@ to_tsquery paired with a gin_trgm_ops index

Describe the bug

Message search cannot use any index: @@ to_tsquery paired with a gin_trgm_ops index

Summary

SearchService#filter_messages_with_gin filters with the full-text search operator @@, but the only GIN index on messages.content uses gin_trgm_ops (trigram). A trigram index cannot serve @@ tsquery, so every message search degrades into a full filter scan over the candidate set.

The code comment states the opposite of what happens:

# app/services/search_service.rb:86-87
# Apply the text search using the GIN index
base_query.where('content @@ to_tsquery(?)', tsquery)

# app/jobs/migration/add_search_indexes_job.rb:7
add_index(:messages, :content, using: 'gin', opclass: :gin_trgm_ops, algorithm: :concurrently)

Introduced in #11107 ("feat: use gin index for message search"). Still present on master and develop as of v4.17.1.

Why no index can be used

text @@ tsquery is evaluated as to_tsvector(get_current_ts_config(), content) @@ tsquery. get_current_ts_config() is STABLE, not IMMUTABLE, so the expression is not indexable at all — this is not a missing-index problem, it is an unindexable query shape.

Demonstrated with enable_seqscan = off, which forces the planner to use any available index:

-- Chatwoot's form
EXPLAIN SELECT * FROM t WHERE content @@ to_tsquery('x');
  Seq Scan on t  (cost=10000000000.00..10000002587.00 ...)

-- explicit regconfig, with a matching GIN index
EXPLAIN SELECT * FROM t WHERE to_tsvector('english',content) @@ to_tsquery('x');
  Bitmap Index Scan on t_to_tsvector_idx

Impact (production instance, 5.4M messages)

Limit  (actual time=8478.074..8581.961 rows=8 loops=1)
  ->  Parallel Bitmap Heap Scan on messages
        Filter: ((account_id = 1) AND (content @@ to_tsquery('...')))
        Rows Removed by Filter: 42928        -- x3 workers = ~129k rows
Execution Time: 8689.655 ms

Each search burns ~8s of CPU. Sampling pg_stat_activity showed this single query accounting for 71% of active database time, saturating a 4-core host and starving Sidekiq (webhook queue backlog of ~19k jobs, WhatsApp messages delayed by minutes).

Proposed fix

Make the indexed expression explicit and add the matching index:

base_query.where("to_tsvector('english', content) @@ to_tsquery(?)", tsquery)

add_index :messages,
          "to_tsvector('english', content)",
          using: :gin,
          name: 'index_messages_on_content_fts',
          algorithm: :concurrently

Index size measured on the instance above: ~339 MB for 5.4M messages.

A better long-term option is a GENERATED ALWAYS AS ... STORED tsvector column, which also makes the text search configuration explicit instead of depending on the server's default_text_search_config (today the search silently applies English stemming to non-English content).

Note the trigram index is still needed for the ILIKE paths elsewhere in SearchService, so it should not be dropped.

Environment

• Chatwoot v4.17.1 (self-hosted, Linux)
• PostgreSQL 16.15, default_text_search_config = pg_catalog.english
• messages: 5.4M rows, 3.2 GB table / 4.0 GB indexes

To Reproduce

Application level

1. Self-hosted Chatwoot v4.17.1 with a large messages table (5.4M rows here).

2. Ensure Migration::AddSearchIndexesJob has run, so index_messages_on_content exists as gin (content gin_trgm_ops).

3. As an agent, use the conversation search box to search any term.

4. Watch pg_stat_activity while searching:

   sql复制

   SELECT now() - query_start AS duration, query
   FROM pg_stat_activity
   WHERE state = 'active' AND query ILIKE '%to_tsquery%';

5. The query runs for seconds (8.7s on our instance) and EXPLAIN shows content @@ to_tsquery(...) applied as a row-by-row Filter, never as an Index Cond.

Self-contained proof (any PostgreSQL, no Chatwoot needed)

This creates both index types and forces the planner to prefer any index (enable_seqscan = off). Chatwoot's query form still falls back to a sequential scan, proving the expression is not indexable at all:

BEGIN; CREATE TEMP TABLE messages_demo (id serial, content text); INSERT INTO messages_demo (content) SELECT 'customer message number ' || g FROM generate_series(1, 200000) g;

-- the index Chatwoot actually creates CREATE INDEX ON messages_demo USING gin (content gin_trgm_ops); -- the index the query would need CREATE INDEX ON messages_demo USING gin (to_tsvector('english', content)); ANALYZE messages_demo;

SET enable_seqscan = off;

-- (1) Chatwoot's current form EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM messages_demo WHERE content @@ to_tsquery('customer');

-- (2) explicit regconfig EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM messages_demo WHERE to_tsvector('english', content) @@ to_tsquery('customer'); ROLLBACK;

Result on PostgreSQL 16.15:

(1) Seq Scan on messages_demo (cost=10000000000.00..10000103666.00 ...) Filter: (content @@ to_tsquery('customer'::text)) Execution Time: 3854.493 ms

(2) Bitmap Index Scan on messages_demo_to_tsvector_idx Index Cond: (to_tsvector('english'::regconfig, content) @@ to_tsquery(...)) Execution Time: 86.977 ms

44x difference. The cost of 10000000000.00 in (1) is the penalty enable_seqscan = off applies — the planner had no index option at all.

Expected behavior

No response

Environment

Linux VM

Cloud Provider

AWS

Platform

Browser

Operating system

Ubuntu 24.04

Browser and version

No response

Docker (if applicable)

No response

Additional context

No response