Re: Performance degradation in Index searches with special characters - Mailing list pgsql-performance

### Performance Degradation with Special Characters in PostgreSQL

#### **Explanation**:
The performance degradation you're experiencing when using special characters like `<`, `@`, `#`, etc., is likely due to how PostgreSQL handles **collations**. By default, PostgreSQL uses locale-aware collations (typically UTF-8) for string comparisons, which involve complex character sorting and encoding. Special characters may be treated differently under certain locales, resulting in slower comparisons, especially with longer strings.

#### **Key Observations**:
1. **Alphabetic vs. Special Characters**:
   - Queries with alphabetic characters perform significantly faster than those with special characters.
   - This is due to differences in how the collation handles comparisons for alphabetic and non-alphabetic characters. Special characters often require more computational resources for comparison, resulting in longer execution times.

2. **Impact of Collation**:
   - When you switched the collation to **"C"**, the performance improved substantially. This is because the `"C"` collation uses **byte-wise** comparisons rather than locale-aware sorting, which simplifies the comparison logic, especially for special characters.

3. **String Length Impact**:
   - As the string length increases, the performance degrades exponentially when using special characters. This is due to the collation’s computational complexity for each additional character comparison.

#### **Recommendations**:

1. **Use the "C" Collation**:
   - The most effective improvement you observed came from using the `"C"` collation, which performs simple byte-by-byte comparisons. You can apply the `"C"` collation at the query level or alter the column definition:
     ```sql
     SELECT 1 FROM test WHERE value = repeat('<', 65536) COLLATE "C";
     ```
     or
     ```sql
     ALTER TABLE test ALTER COLUMN value SET DATA TYPE VARCHAR(10) COLLATE "C";
     ```

2. **Partial Indexing**:
   - Create an index with a specific collation for certain characters. This allows PostgreSQL to use the optimized comparison method only when dealing with special characters:
     ```sql
     CREATE INDEX idx_test_special_chars ON test (value COLLATE "C");
     ```

3. **Reduce String Length**:
   - If possible, reduce the string length for comparisons involving special characters. The performance impact grows with longer strings due to the nature of locale-aware collation.

4. **Use Functional Indexes**:
   - You can use functional indexes that apply transformations (e.g., trimming special characters or converting to ASCII) before comparison:
     ```sql
     CREATE INDEX idx_test_transformed ON test (lower(value));
     ```

5. **Optimize Queries**:
   - For repeated queries involving long strings with special characters, try caching results or using materialized views where the collation overhead is reduced by pre-computing.

6. **Custom Collations**:
   - If you need locale-aware sorting but can compromise on certain aspects, consider creating a **custom collation** that simplifies special character handling.

#### **Answers to Your Questions**:
1. **Is the performance degradation expected due to collation handling of certain special characters?**
   - Yes, this behavior is expected. Locale-aware collations can be complex for special characters, leading to longer comparison times.

2. **Are there any recommendations to improve performance without changing the column or database collation?**
   - Without changing the collation entirely, you can use:
     - **Query-level collation adjustments** (using the `"C"` collation in specific queries).
     - **Partial indexes** with the `"C"` collation.
     - **Functional indexes** or optimizations like materialized views.