Why Are My PostgreSQL LIKE Queries So Slow, and How Can I Speed Them Up?

PostgreSQL LIKE Queries: Performance Bottlenecks and Optimization Strategies

PostgreSQL's LIKE queries, known for their computational intensity, have shown inconsistent performance against a specific database table. While some queries complete within 200-400 milliseconds, others take up to 30 seconds.

Standard Indexing Limitations

An initial attempt using a b-tree index on the owner1 field failed to improve performance. Experiments with various LIKE syntax variations also yielded no significant results.

Trigram Indexes: A Superior Solution

The pg_trgm extension offers trigram indexes (GIN/GiST) which provide substantial performance enhancements for LIKE and ILIKE operations, especially with longer strings. Importantly, these indexes also support words shorter than three characters.

Optimizing Prefix Matching

For searches without leading wildcards, consider these alternatives:

• ^@ Operator/starts_with() Function (PostgreSQL 11 ): The ^@ operator and starts_with() function, introduced in PostgreSQL 11, offer efficient prefix matching when used with SP-GiST indexes.
• COLLATE "C": Indexes created using COLLATE "C" behave similarly to the text_pattern_ops operator class, enabling optimized prefix matching with b-tree indexes.
• text_pattern_ops Operator Class: This operator class creates b-tree indexes specifically designed for left-anchored patterns (no leading wildcard).

Additional Resources

For a more detailed understanding of pattern matching in PostgreSQL, refer to these resources:

• Pattern matching with LIKE, SIMILAR TO, or regular expressions
• How is LIKE implemented?
• Efficiently finding similar strings in PostgreSQL

The above is the detailed content of Why Are My PostgreSQL LIKE Queries So Slow, and How Can I Speed Them Up?. For more information, please follow other related articles on the PHP Chinese website!