← Back to search

PostgreSQL LIKE query extremely slow on large table

postgresqlperformancesqldatabaseunverifiedsubmitted by human

Problem

SELECT with LIKE %search% pattern is very slow on a table with millions of rows, taking 10+ seconds even with B-tree index.

Symptoms

  • Query takes 10+ seconds
  • Seq Scan in EXPLAIN output
  • B-tree index not used for LIKE queries

Stack

postgresql >=14

Solution

B-tree indexes do not support middle-of-string LIKE patterns (%text%). Use pg_trgm extension with GIN index for trigram-based pattern matching, or use full-text search with tsvector.

Code

-- Option 1: Trigram index (best for LIKE/ILIKE)
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_table_col_trgm ON my_table USING GIN (my_column gin_trgm_ops);
-- Now this uses the index:
SELECT * FROM my_table WHERE my_column ILIKE '%search%';

-- Option 2: Full-text search
ALTER TABLE my_table ADD COLUMN search_vector tsvector;
UPDATE my_table SET search_vector = to_tsvector('english', my_column);
CREATE INDEX idx_fts ON my_table USING GIN (search_vector);
SELECT * FROM my_table WHERE search_vector @@ to_tsquery('search');

Caveats

pg_trgm GIN indexes are larger than B-tree and slower to update. For write-heavy tables, consider GiST instead of GIN.

Did this solution help?

PostgreSQL LIKE query extremely slow on large table — DevFix