This query generates a list of all indexes with an explicitly configured, non-default FILLFACTOR for the purpose of a performance audit. The query is aware of the different default FILLFACTOR values associated with various index access methods (e.g., 90 for B-tree, 100 for others like GiST/GIN). This allows administrators to quickly identify and review all instances of customized index storage parameters to assess if these non-standard configurations are justified and still effective.
Type
General (Overview of some aspect of the database.)
WITH indexes_reloptions AS (SELECT
pg_class.relname AS index_name,
pg_namespace.nspname AS index_schema,
amname AS index_type,
unnest(reloptions) AS reloptions,
pg_get_indexdef(pg_class.oid) AS index_definition
FROM
pg_catalog.pg_class,
pg_catalog.pg_namespace,
pg_am
WHERE
pg_class.relnamespace = pg_namespace.oid
AND pg_class.relam=pg_am.oid
AND relkind='i')
SELECT index_name, index_schema, index_type, reloptions, index_definition
FROM indexes_reloptions
WHERE (index_type='btree' AND reloptions ILIKE '%fillfactor%' AND reloptions NOT LIKE '%fillfactor=90%')
OR (index_type<>'btree' AND reloptions ILIKE '%fillfactor%')
ORDER BY index_name;
Collections
This query belongs to the following collections:
Name
Description
Find problems by overview
Queries that results point to different aspects of database that might have problems. A human reviewer has to decide based on the results as to whether there are problems or not .
Categories
This query is classified under the following categories:
Name
Description
Data at the database physical level
Queries of this category provide information about the disk usage.
Performance
Queries of this category provide information about indexes in a database.