Kürzlich hat mich ein Kunde gefragt, ob das “aufwändige” Index-Prüfen nicht einem Index-Optimierer überlassen werden könnte. Klar geht das…
Was wollen wir den genau überprüfen?
Tabellen ohne Primary Key
Doppelte Indices
Teilweise redundante Indices
Ungenutzte Indices
MariaDB, MySQL und Percona Server
Tabellen ohne 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
Bei MariaDB muss das PERFORMANCE_SCHEMA zuerst eingeschaltet werden.
Die Informationen sind korrekt seit dem letzten Datenbank-Neustart. Wurde ein Index das letzte mal VOR dem letzten Neustart genutzt, wir er hier als ungenutzt angezeigt.
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)
An den Swiss PGDay2026(s) hat Bertrand Hartwig sein Tool PG Assistant vorgestellt. In diesem Zusammenhang wollte ich es gleich mal ausprobieren…
Fehlende Primary Keys und doppelte Indices konnte PG Assistant finden. Teilweise redundante Indices oder ungenutzte Indices hat er mir nicht angezeigt, kann aber auch an mir liegen…
PG Assistant: Dashboard / Dev advisor PG Assistant: Global Advisor / Dev advisor PG Assistant: Strictly duplicate unused index
