The list of all the queries

Full text search columns that have no gin or gist index

Query goal: Find columns of base tabels and materialized views with the type tsvector that do not have a gin or a gist index. These are the preferred index types for text search.
Query type: Problem detection (Each row in the result could represent a flaw in the design)
Query reliability: High (Few or no false-positive results)
Query license: MIT License
Fixing suggestion: Create a gin index on each column with the tsvector type.
Data source: INFORMATION_SCHEMA+system catalog
SQL query: Click on query to copy it

WITH columns AS (SELECT nspname AS table_schema, relname AS table_name, 
CASE WHEN relkind='r' THEN 'BASE TABLE'
WHEN relkind='m' THEN 'MATERIALIZED VIEW'
END AS table_type, 
attname AS column_name,
coalesce(domain_type.typname, pg_type.typname) AS data_type
FROM pg_class INNER JOIN pg_namespace ON pg_class.relnamespace=pg_namespace.oid
INNER JOIN pg_attribute ON pg_class.oid=pg_attribute.attrelid
INNER JOIN pg_type ON pg_attribute.atttypid =pg_type.oid
LEFT JOIN pg_type AS domain_type ON domain_type.oid=pg_type.typbasetype
WHERE attnum>=1 
AND relkind IN ('r','m')
AND nspname NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)),
text_search_cols AS (SELECT table_schema, table_name, table_type, array_agg(column_name::text) AS column_name
FROM columns
WHERE data_type IN ('tsvector')
GROUP BY  table_schema, table_name, table_type),
idx AS (SELECT 
n.nspname AS index_schema,
c.relname AS index_name,
c2.relname AS table_name,
i.indrelid AS index_table_oid,
indkey AS index_col
FROM pg_catalog.pg_index AS i INNER JOIN pg_catalog.pg_class AS c ON i.indexrelid=c.oid
INNER JOIN pg_catalog.pg_class AS c2 ON i.indrelid=c2.oid
INNER JOIN pg_catalog.pg_namespace n ON c.relnamespace=n.oid
INNER JOIN pg_catalog.pg_am a ON c.relam=a.oid
WHERE 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 amname IN ('gin','gist')),
idx_unnest as (select index_name, index_schema, table_name, index_table_oid, index_col_num, ordin
from idx, unnest(idx.index_col) with ordinality as k(index_col_num, ordin)),
idx_with_names as (select index_name, index_schema, table_name, array_agg(a_idx.attname::text order by ordin) as idx_col
from idx_unnest k inner join pg_attribute a_idx on k.index_col_num = a_idx.attnum and k.index_table_oid = a_idx.attrelid and a_idx.attisdropped = false
group by index_name, index_schema, table_name)
SELECT table_schema, table_name, table_type, column_name[1]
FROM text_search_cols
WHERE NOT EXISTS (SELECT 1
FROM idx_with_names WHERE idx_with_names.index_schema=text_search_cols.table_schema AND idx_with_names.table_name=text_search_cols.table_name AND 
(idx_with_names.idx_col[1]=text_search_cols.column_name[1] OR (idx_with_names.idx_col@>text_search_cols.column_name AND text_search_cols.column_name@>idx_with_names.idx_col)))
ORDER BY table_schema, table_name, column_name;

SQL statements for generating SQL statements that help us to fix the problem

SQL queryDescription
WITH columns AS (SELECT nspname AS table_schema, relname AS table_name, 
CASE WHEN relkind='r' THEN 'BASE TABLE'
WHEN relkind='m' THEN 'MATERIALIZED VIEW'
END AS table_type, 
attname AS column_name,
coalesce(domain_type.typname, pg_type.typname) AS data_type
FROM pg_class INNER JOIN pg_namespace ON pg_class.relnamespace=pg_namespace.oid
INNER JOIN pg_attribute ON pg_class.oid=pg_attribute.attrelid
INNER JOIN pg_type ON pg_attribute.atttypid =pg_type.oid
LEFT JOIN pg_type AS domain_type ON domain_type.oid=pg_type.typbasetype
WHERE attnum>=1 
AND relkind IN ('r','m')
AND nspname NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)),
text_search_cols AS (SELECT table_schema, table_name, table_type, array_agg(column_name::text) AS column_name
FROM columns
WHERE data_type IN ('tsvector')
GROUP BY  table_schema, table_name, table_type),
idx AS (SELECT 
n.nspname AS index_schema,
c.relname AS index_name,
c2.relname AS table_name,
i.indrelid AS index_table_oid,
indkey AS index_col
FROM pg_catalog.pg_index AS i INNER JOIN pg_catalog.pg_class AS c ON i.indexrelid=c.oid
INNER JOIN pg_catalog.pg_class AS c2 ON i.indrelid=c2.oid
INNER JOIN pg_catalog.pg_namespace n ON c.relnamespace=n.oid
INNER JOIN pg_catalog.pg_am a ON c.relam=a.oid
WHERE 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 amname IN ('gin','gist')),
idx_unnest as (select index_name, index_schema, table_name, index_table_oid, index_col_num, ordin
from idx, unnest(idx.index_col) with ordinality as k(index_col_num, ordin)),
idx_with_names as (select index_name, index_schema, table_name, array_agg(a_idx.attname::text order by ordin) as idx_col
from idx_unnest k inner join pg_attribute a_idx on k.index_col_num = a_idx.attnum and k.index_table_oid = a_idx.attrelid and a_idx.attisdropped = false
group by index_name, index_schema, table_name)
SELECT format('CREATE INDEX idx_%2$s_%3$s ON %1$I.%2$I USING GIN (%3$I);', table_schema, table_name, column_name[1]) AS statements
FROM text_search_cols
WHERE NOT EXISTS (SELECT 1
FROM idx_with_names WHERE idx_with_names.index_schema=text_search_cols.table_schema AND idx_with_names.table_name=text_search_cols.table_name AND 
(idx_with_names.idx_col[1]=text_search_cols.column_name[1] OR (idx_with_names.idx_col@>text_search_cols.column_name AND text_search_cols.column_name@>idx_with_names.idx_col)))
ORDER BY table_schema, table_name, column_name;
Create the index.

Collections where the query belongs to

Collection nameCollection description
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 where the query belongs to

Category nameCategory description
Full text searchQueries of this category provide information about full text search
PerformanceQueries of this category provide information about indexes in a database.

Reference materials for further reading

Reference
https://www.postgresql.org/docs/current/textsearch-indexes.html

The list of all the queries