Query goal: | Find all base tables where FILLFACTOR is not 100, i.e., the default value. |
Notes about the query: | The query assumes that if the FILLFACTOR has been changed to other value than 100 and then back to 100, then the corresponding reloption is still registered. There are more than one possible reloptions. Thus, instead of using the condition reloptions IS NOT NULL, the query filters out reloptions that specify a new FILLFACTOR value. |
Query type: | General (Overview of some aspect of the database.) |
Query license: | MIT License |
Data source: | INFORMATION_SCHEMA+system catalog |
SQL query: | Click on query to copy it
WITH base_tables_reloptions AS (SELECT pg_class.relname AS table_name, pg_namespace.nspname AS table_schema, unnest(reloptions) AS reloptions FROM pg_catalog.pg_class, pg_catalog.pg_namespace WHERE pg_class.relnamespace = pg_namespace.oid AND relkind='r'), base_tables_fillfactor AS (SELECT table_schema, table_name, regexp_replace(reloptions,'[^0-9]','','g')::int AS fillfactor FROM base_tables_reloptions WHERE reloptions ILIKE 'fillfactor%') SELECT table_schema, table_name, fillfactor FROM base_tables_fillfactor WHERE fillfactor<>100 AND table_schema NOT IN (SELECT schema_name FROM INFORMATION_SCHEMA.schemata WHERE schema_name<>'public' AND schema_owner='postgres' AND schema_name IS NOT NULL) ORDER BY fillfactor, table_schema, table_name; |
Collection name | Collection 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 . |
Category name | Category 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. |
Reference |
---|
https://www.dbrnd.com/2016/03/postgresql-the-awesome-table-fillfactor-to-speedup-update-and-select-statement/ |
http://blog.coelho.net/database/2014/08/23/postgresql-fillfactor-and-update.html |