Goal This query identifies performance inefficiencies caused by a semantic mismatch between function-based index definitions and their usage within derived tables (views, materialized views). PostgreSQL's query optimizer generally requires the expression in a query to strictly match the expression defined in the index to trigger an index scan. This query flags instances where a column is indexed using one function (e.g., upper(col)) but accessed in a view using a different function (e.g., lower(col)). In such cases, the optimizer cannot utilize the pre-calculated index, resulting in suboptimal execution plans (typically sequential scans).
Notes In the returned subquery of view/materialized view the query replaces each newline character with the line break (br) tag for the better readability in case the query result is displayed in a web browser.
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)
Fixing Suggestion Create appropriate indexes to speed up queries. If you apply a function to a column in a query, then create a function-based index based on the function to the column.
Data Source INFORMATION_SCHEMA+system catalog
SQL Query 
WITH indexes AS (SELECT schemaname, tablename, indexname, indexdef, 'lower' AS opposite_function
FROM pg_catalog.pg_indexes
WHERE indexdef LIKE 'CREATE UNIQUE INDEX%' AND 
indexdef LIKE '%(upper(%'
AND
schemaname NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)
UNION SELECT schemaname, tablename, indexname, indexdef, 'upper' AS opposite_function
FROM pg_catalog.pg_indexes
WHERE indexdef LIKE 'CREATE UNIQUE INDEX%' AND 
indexdef LIKE '%(lower(%'
AND
schemaname NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)
),
indexes_with_opposite AS (SELECT schemaname, tablename, indexname, indexdef, opposite_function, translate(substring(indexdef,'[(][^()]*[)]'),'()','') AS indexed_column
FROM indexes),
view_usage AS (SELECT table_schema, table_name, vtu.view_schema, vtu.view_name, (SELECT view_definition FROM information_schema.views AS v WHERE v.table_schema=vtu.view_schema AND v.table_name=vtu.view_name) AS view_def
FROM information_schema.tables AS t LEFT JOIN information_schema.view_table_usage AS vtu  USING (table_schema, table_name)
WHERE t.table_type = 'BASE TABLE' AND t.table_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)
UNION SELECT DISTINCT 
source_ns.nspname as source_schema,
source_table.relname as source_table,
dependent_ns.nspname as dependent_schema,
dependent_view.relname as dependent_view,
(SELECT definition FROM pg_matviews AS mv WHERE mv.schemaname=dependent_ns.nspname AND mv.matviewname=dependent_view.relname) AS view_def
FROM pg_depend 
JOIN pg_rewrite ON pg_depend.objid = pg_rewrite.oid 
JOIN pg_class as dependent_view ON pg_rewrite.ev_class = dependent_view.oid 
JOIN pg_class as source_table ON pg_depend.refobjid = source_table.oid 
JOIN pg_namespace dependent_ns ON dependent_ns.oid = dependent_view.relnamespace
JOIN pg_namespace source_ns ON source_ns.oid = source_table.relnamespace
WHERE dependent_view.relkind='m'
AND dependent_ns.nspname NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND 
schema_owner='postgres' AND schema_name IS NOT NULL) AND 
(dependent_ns.nspname ||'.'|| dependent_view.relname<>source_ns.nspname||'.'||source_table.relname))
SELECT io.schemaname, io.tablename, io.indexname, io.indexdef, regexp_replace(vu.view_def,'[\r\n]','<br>','g') AS view_definition
FROM indexes_with_opposite AS io INNER JOIN view_usage AS vu ON io.schemaname=vu.table_schema AND io.tablename=vu.table_name
WHERE vu.view_def ILIKE '%' || io.opposite_function || '(' || io.indexed_column || '%' OR 
vu.view_def ILIKE '%' || io.opposite_function || '((' || io.indexed_column || '%' OR 
vu.view_def ILIKE '%' || io.opposite_function || '(' || io.tablename || '.' || io.indexed_column || '%' OR 
vu.view_def ILIKE '%' || io.opposite_function || '((' || io.tablename || '.' || io.indexed_column || '%'
ORDER BY schemaname, tablename, indexname;

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 .

NameDescription
Find problems automaticallyQueries, 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:

Derived tables

Queries of this category provide information about the derived tables (views, materialized views), which are used to implement virtual data layer.

Performance

Queries of this category provide information about indexes in a database.

NameDescription
Derived tablesQueries of this category provide information about the derived tables (views, materialized views), which are used to implement virtual data layer.
PerformanceQueries of this category provide information about indexes in a database.