Why Are PostgreSQL LIKE Queries So Slow, and How Can I Make Them Faster?

Optimizing PostgreSQL LIKE Queries: Addressing Performance Issues

PostgreSQL's LIKE queries, used for substring matching, often exhibit unpredictable performance, ranging from milliseconds to seconds. This article explores the root causes and effective optimization strategies.

Understanding Performance Variability

The performance inconsistencies stem from the inherent resource demands of LIKE queries. Unlike equality checks, which leverage indexes effectively, LIKE queries typically necessitate full table scans.

Index Limitations

While B-tree indexes are crucial for performance, they are not inherently suited for LIKE operations. An index on a column (e.g., owner1) enhances exact matches (SELECT * FROM parcels WHERE owner1 = 'John Doe'), but offers limited benefit for LIKE queries.

Performance Enhancement Techniques

Several techniques can significantly improve LIKE query performance:

Full Text Search (FTS)

PostgreSQL's FTS provides efficient text searching. However, it doesn't directly support the LIKE operator and operates on words, not arbitrary substrings.

Prefix Matching Optimization

For patterns without leading wildcards (e.g., 'foo%'), utilize operator classes like text_pattern_ops or varchar_pattern_ops with a B-tree index. These optimize left-anchored pattern matching.

Trigram Indexes for Flexible Matching

The pg_trgm extension, coupled with GIN or GiST indexes, offers trigram indexing. This supports all LIKE and ILIKE patterns, regardless of wildcard position.

Further Considerations

• String Length: Indexed words shorter than three characters will function, but patterns with fewer than three characters might still trigger full index scans.
• PostgreSQL 11 and Beyond: PostgreSQL 11 introduced the ^@ operator and starts_with() function for efficient prefix matching with SP-GiST indexes.
• Collation and Indexing: Employing COLLATE "C" with indexes or operator classes tailored to specific collations can optimize prefix matching and LIKE query performance for specific scenarios.

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