Find all indexes where FILLFACTOR is not default, i.e., it has been changed. The default is different in case of different index types is different. In case of B-tree indexes the default is 90.
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.