patternsqlMinor

What's the most efficient way to perform a case insensitive search for a word in a text field?

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

casefieldthewhatinsensitivesearchtextefficientwayperform

Problem

What is the most efficient way to perform a case insensitive search for a word in a TEXT (or CLOB) field?

I've been using WHERE LOWER(body) LIKE '%word%' but I think the calls to LOWER() might be slowing the query significantly.

Solution

In PostgreSQL, you can use Text Search:

create index on foo_table using gin ( to_tsvector('simple', body) );

select 
*
from foo_table
where
  to_tsvector('simple', body) @@ plainto_tsquery('simple', 'word');

Code Snippets

create index on foo_table using gin ( to_tsvector('simple', body) );

select 
*
from foo_table
where
  to_tsvector('simple', body) @@ plainto_tsquery('simple', 'word');

Context

StackExchange Database Administrators Q#114783, answer score: 2

Revisions (0)

No revisions yet.