Recently, a client asked me if the “time-consuming” task of checking indexes could be left to an index optimizer. Of course it can…
What exactly do we want to check?
Tables without a Primary Key
Duplicate indexes
Partially redundant indexes
Unused indexes
MariaDB, MySQL, and Percona Server
Tables without a Primary Key
SQL> SELECT DISTINCT t.table_schema, t.table_name
FROM information_schema.tables AS t
LEFT JOIN information_schema.columns AS c ON t.table_schema = c.table_schema AND t.table_name = c.table_name
AND c.column_key = "PRI"
WHERE t.table_schema NOT IN ('information_schema', 'mysql', 'performance_schema')
AND c.table_name IS NULL AND t.table_type NOT IN('VIEW', 'SEQUENCE')
AND t.table_schema = 'testtest'
;
+--------------+------------+
| table_schema | table_name |
+--------------+------------+
| testtest | archived |
+--------------+------------+
1 row in set
For MariaDB, the PERFORMANCE_SCHEMA must be enabled first.
The information is accurate as of the last database restart. If an index was last used BEFORE the most recent restart, it will be shown here as unused.
SQL> SELECT tab.table_schema, tab.table_name
FROM information_schema.tables tab
LEFT JOIN information_schema.table_constraints tco
ON tab.table_schema = tco.table_schema
AND tab.table_name = tco.table_name
AND tco.constraint_type = 'PRIMARY KEY'
WHERE tab.table_type = 'BASE TABLE'
AND tab.table_schema NOT IN ('pg_catalog', 'information_schema')
AND tco.constraint_name IS NULL
ORDER BY table_schema, table_name
;
table_schema | table_name
--------------+------------
public | archived
(1 row)
SQL> WITH schema_flattened_keys AS (
SELECT sai.relid, sai.indexrelid
, sai.schemaname AS table_schema, sai.relname AS table_name, sai.indexrelname AS index_name
, CASE pi.indisunique WHEN 'f' THEN 1 ELSE 0 END AS non_unique
, index_columns.columns AS index_columns
FROM pg_stat_all_indexes AS sai
JOIN pg_index AS pi ON pi.indexrelid = sai.indexrelid
JOIN (
SELECT attrelid, string_agg(attname, ',' ORDER BY attnum ASC) AS columns
FROM pg_attribute GROUP BY attrelid
) AS index_columns ON index_columns.attrelid = sai.indexrelid
WHERE sai.schemaname NOT IN ('pg_toast', 'pg_catalog')
)
SELECT redundant_keys.table_schema AS table_schema, redundant_keys.table_name AS table_name, redundant_keys.index_name AS redundant_index_name
, redundant_keys.index_columns AS redundant_index_columns, redundant_keys.non_unique AS redundant_index_non_unique
, dominant_keys.index_name AS dominant_index_name, dominant_keys.index_columns AS dominant_index_columns, dominant_keys.non_unique AS dominant_index_non_unique
, CONCAT('ALTER TABLE ', redundant_keys.table_schema, '.', redundant_keys.table_name, ' DROP INDEX ', redundant_keys.index_name, '') AS sql_drop_index
FROM schema_flattened_keys redundant_keys
JOIN schema_flattened_keys dominant_keys ON redundant_keys.table_schema = dominant_keys.table_schema AND redundant_keys.table_name = dominant_keys.table_name
WHERE (redundant_keys.index_name <> dominant_keys.index_name
AND ((redundant_keys.index_columns = dominant_keys.index_columns)
AND ((redundant_keys.non_unique > dominant_keys.non_unique) OR (redundant_keys.non_unique = dominant_keys.non_unique))
)
OR ((POSITION(CONCAT(redundant_keys.index_columns,',') IN dominant_keys.index_columns) = 1) AND (redundant_keys.non_unique = 1))
OR ((POSITION(CONCAT(dominant_keys.index_columns,',') IN redundant_keys.index_columns) = 1) AND (dominant_keys.non_unique = 0))
)
AND redundant_keys.index_columns = dominant_keys.index_columns
;
table_schema | table_name | redundant_index_name | redundant_index_columns | redundant_index_non_unique | dominant_index_name | dominant_index_columns | dominant_index_non_unique | sql_drop_index
--------------+------------+----------------------+-------------------------+----------------------------+---------------------+------------------------+---------------------------+----------------------------------------------
public | archived | dupl1 | category_id | 1 | dupl2 | category_id | 1 | ALTER TABLE public.archived DROP INDEX dupl1
public | archived | dupl2 | category_id | 1 | dupl1 | category_id | 1 | ALTER TABLE public.archived DROP INDEX dupl2
(2 rows)
SQL> WITH schema_flattened_keys AS (
SELECT sai.relid, sai.indexrelid
, sai.schemaname AS table_schema, sai.relname AS table_name, sai.indexrelname AS index_name
, CASE pi.indisunique WHEN 'f' THEN 1 ELSE 0 END AS non_unique
, index_columns.columns AS index_columns
FROM pg_stat_all_indexes AS sai
JOIN pg_index AS pi ON pi.indexrelid = sai.indexrelid
JOIN (
SELECT attrelid, string_agg(attname, ',' ORDER BY attnum ASC) AS columns
FROM pg_attribute GROUP BY attrelid
) AS index_columns ON index_columns.attrelid = sai.indexrelid
WHERE sai.schemaname NOT IN ('pg_toast', 'pg_catalog')
)
SELECT redundant_keys.table_schema AS table_schema, redundant_keys.table_name AS table_name, redundant_keys.index_name AS redundant_index_name
, redundant_keys.index_columns AS redundant_index_columns, redundant_keys.non_unique AS redundant_index_non_unique
, dominant_keys.index_name AS dominant_index_name, dominant_keys.index_columns AS dominant_index_columns, dominant_keys.non_unique AS dominant_index_non_unique
, CONCAT('ALTER TABLE ', redundant_keys.table_schema, '.', redundant_keys.table_name, ' DROP INDEX ', redundant_keys.index_name, '') AS sql_drop_index
FROM schema_flattened_keys redundant_keys
JOIN schema_flattened_keys dominant_keys ON redundant_keys.table_schema = dominant_keys.table_schema AND redundant_keys.table_name = dominant_keys.table_name
WHERE (redundant_keys.index_name <> dominant_keys.index_name
AND ((redundant_keys.index_columns = dominant_keys.index_columns)
AND ((redundant_keys.non_unique > dominant_keys.non_unique) OR (redundant_keys.non_unique = dominant_keys.non_unique))
)
OR ((POSITION(CONCAT(redundant_keys.index_columns,',') IN dominant_keys.index_columns) = 1) AND (redundant_keys.non_unique = 1))
OR ((POSITION(CONCAT(dominant_keys.index_columns,',') IN redundant_keys.index_columns) = 1) AND (dominant_keys.non_unique = 0))
)
;
table_schema | table_name | redundant_index_name | redundant_index_columns | redundant_index_non_unique | dominant_index_name | dominant_index_columns | dominant_index_non_unique | sql_drop_index
--------------+-----------------------+------------------------------------------+-------------------------+----------------------------+-------------------------------------------------+-----------------------------------------+---------------------------+---------------------------------------------------------------------------------------------
public | numbers | numbers_customer_idx | customer | 1 | numbers_customer_text_dtype_text_dnumber_idx | customer,text_dtype,text_dnumber | 1 | ALTER TABLE public.numbers DROP INDEX numbers_customer_idx
public | numbers | numbers_customer_idx | customer | 1 | numbers_customer_fax_dtype_fax_dnumber_idx | customer,fax_dtype,fax_dnumber | 1 | ALTER TABLE public.numbers DROP INDEX numbers_customer_idx
public | numbers | numbers_customer_idx | customer | 1 | numbers_pkey | customer,stype,snumber | 0 | ALTER TABLE public.numbers DROP INDEX numbers_customer_idx
public | numbers | numbers_dtype_idx | dtype | 1 | numbers_dtype_dnumber_idx | dtype,dnumber | 1 | ALTER TABLE public.numbers DROP INDEX numbers_dtype_idx
public | number_callers | number_callers_dtype_idx | dtype | 1 | number_callers_dtype_dnumber_idx | dtype,dnumber | 1 | ALTER TABLE public.number_callers DROP INDEX number_callers_dtype_idx
public | prefixes | prefixes_customer_idx | customer | 1 | prefixes_customer_dtype_dnumber_idx | customer,dtype,dnumber | 1 | ALTER TABLE public.prefixes DROP INDEX prefixes_customer_idx
public | number_times | number_times_dtype_idx | dtype | 1 | number_times_dtype_dnumber_idx | dtype,dnumber | 1 | ALTER TABLE public.number_times DROP INDEX number_times_dtype_idx
public | phones | phones_customer_idx | customer | 1 | phones_customer_callerid_location_idx | customer,callerid_location | 1 | ALTER TABLE public.phones DROP INDEX phones_customer_idx
public | phones | phones_customer_idx | customer | 1 | phones_customer_callerid_external_idx | customer,callerid_external | 1 | ALTER TABLE public.phones DROP INDEX phones_customer_idx
public | phones | phones_customer_idx | customer | 1 | phones_customer_callerid_internal_idx | customer,callerid_internal | 1 | ALTER TABLE public.phones DROP INDEX phones_customer_idx
public | phones_hardware | phones_hardware_phone_idx | phone | 1 | phones_hardware_phone_hardware_address_idx | phone,hardware_address | 0 | ALTER TABLE public.phones_hardware DROP INDEX phones_hardware_phone_idx
public | speeddials | speeddials_stype_idx | stype | 1 | speeddials_stype_snumber_idx | stype,snumber | 1 | ALTER TABLE public.speeddials DROP INDEX speeddials_stype_idx
public | speeddials | speeddials_dtype_idx | dtype | 1 | speeddials_dtype_dnumber_idx | dtype,dnumber | 1 | ALTER TABLE public.speeddials DROP INDEX speeddials_dtype_idx
public | mailbox_destinations | mailbox_destinations_context_mailbox_idx | context,mailbox | 1 | mailbox_destinations_pkey | context,mailbox,dcustomer,dtype,dnumber | 0 | ALTER TABLE public.mailbox_destinations DROP INDEX mailbox_destinations_context_mailbox_idx
public | outgroup_times | outgroup_times_outgroup_idx | outgroup | 1 | outgroup_times_outgroup_name_idx | outgroup,name | 0 | ALTER TABLE public.outgroup_times DROP INDEX outgroup_times_outgroup_idx
public | ingroup_times | ingroup_times_ingroup_idx | ingroup | 1 | ingroup_times_ingroup_name_idx | ingroup,name | 0 | ALTER TABLE public.ingroup_times DROP INDEX ingroup_times_ingroup_idx
public | active_customers | active_customers_uniqueid_idx | uniqueid | 1 | active_customers_pkey | uniqueid,scustomer | 0 | ALTER TABLE public.active_customers DROP INDEX active_customers_uniqueid_idx
public | access | access_customer_idx | customer | 1 | access_customer_callerid_external_idx | customer,callerid_external | 1 | ALTER TABLE public.access DROP INDEX access_customer_idx
public | access | access_customer_idx | customer | 1 | access_customer_callerid_internal_idx | customer,callerid_internal | 1 | ALTER TABLE public.access DROP INDEX access_customer_idx
public | unlimited_access | unlimited_access_customer_idx | customer | 1 | unlimited_access_customer_callerid_external_idx | customer,callerid_external | 1 | ALTER TABLE public.unlimited_access DROP INDEX unlimited_access_customer_idx
public | unlimited_access | unlimited_access_customer_idx | customer | 1 | unlimited_access_customer_callerid_internal_idx | customer,callerid_internal | 1 | ALTER TABLE public.unlimited_access DROP INDEX unlimited_access_customer_idx
public | texts | texts_dcustomer_idx | dcustomer | 1 | texts_dcustomer_dtype_dnumber_idx | dcustomer,dtype,dnumber | 1 | ALTER TABLE public.texts DROP INDEX texts_dcustomer_idx
public | texts_media | texts_media_uniqueid_idx | uniqueid | 1 | texts_media_pkey | uniqueid,filename | 0 | ALTER TABLE public.texts_media DROP INDEX texts_media_uniqueid_idx
public | number_calleridgroups | number_calleridgroups_dtype_idx | dtype | 1 | number_calleridgroups_dtype_dnumber_idx | dtype,dnumber | 1 | ALTER TABLE public.number_calleridgroups DROP INDEX number_calleridgroups_dtype_idx
public | analytics_include | analytics_i | analytics | 1 | analytics_include_pkey | analytics,feature,dtype,dnumber | 0 | ALTER TABLE public.analytics_include DROP INDEX analytics_i
public | archived | dupl1 | category_id | 1 | dupl2 | category_id | 1 | ALTER TABLE public.archived DROP INDEX dupl1
public | archived | dupl2 | category_id | 1 | dupl1 | category_id | 1 | ALTER TABLE public.archived DROP INDEX dupl2
(27 rows)
At the Swiss PGDay2026(s), Bertrand Hartwig presented his tool PG Assistant. In that context, I wanted to try it out right away…
PG Assistant was able to find missing Primary Keys and duplicate indexes. It didn’t show me any partially redundant or unused indexes, but that might just be on my end…
PG Assistant: Dashboard / Dev advisor PG Assistant: Global Advisor / Dev advisor PG Assistant: Strictly duplicate unused index
