| Goal | This query identifies expression indexes that apply the upper() function to a column. In PostgreSQL, it is a strong community convention to standardize on the lower() function for case-insensitive indexing and comparisons. Enforcing this convention ensures consistency across the schema and prevents performance issues caused by queries failing to utilize indexes (for example, if a developer queries using lower(col) but the index is built on upper(col)). |
|---|---|
| Type | Problem detection Each row in the result could represent a flaw in the design |
| Reliability | Medium Medium number of false-positive results |
| License | MIT (opens in new tab) |
| Data Source | INFORMATION_SCHEMA+system catalog |
| SQL Query |
|
Fixing Suggestion
To resolve this issue and align with standard conventions, drop the existing expression index and recreate it using the lower() function instead of upper(). Additionally, ensure that all application queries relying on this column are updated to search using lower(col) so they continue to benefit from index acceleration.
Collections
This query belongs to the following collections:
Find problems automatically
Queries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not .
| Name | Description |
|---|---|
| Find problems automatically | Queries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not . |
Categories
This query is classified under the following categories:
Inconsistencies
Queries of this catergory provide information about inconsistencies of solving the same problem in different places.
Performance
Queries of this category provide information about indexes in a database.
| Name | Description |
|---|---|
| Inconsistencies | Queries of this catergory provide information about inconsistencies of solving the same problem in different places. |
| Performance | Queries of this category provide information about indexes in a database. |