How to reindex system catalogs
Article ID: 295619
Updated On:
Products
Issue/Introduction
In heavily used databases, the catalog indexes will occupy more space. This is known as index bloat and this article will help you understand what can be done to fix index bloat.
Environment
Cause
Resolution
To remove this index bloat, you have to recreate the index as a VACUUM.
Note: This works only on tables and not on indexes. You can schedule this activity during system maintenance, such as routine VACUUM and ANALYZE.
To recreate the index you can use either of the following options:
The keyword SYSTEM will recreate the indexes of all the catalog tables of the database specified with dbname.
REINDEX SYSTEM dbname ;
You can also reindex a specific index or the whole table with the below commands:
REINDEX TABLE tablename ; REINDEX INDEX index_name ;
Note: The reindex command will only run on the database where you have connected and will not affect other databases in the cluster.
Example
In the below example we are referring one of the indexes on the catalog pg_attribute of database gpadmin.
A quick way to check the indexes on the table is using \d.
gpadmin=# \d+ pg_attribute
Table "pg_catalog.pg_attribute"
Column | Type | Modifiers | Storage | Description
---------------+----------+-----------+---------+-------------
attrelid | oid | not null | plain |
attname | name | not null | plain |
atttypid | oid | not null | plain |
attstattarget | integer | not null | plain |
attlen | smallint | not null | plain |
attnum | smallint | not null | plain |
attndims | integer | not null | plain |
attcacheoff | integer | not null | plain |
atttypmod | integer | not null | plain |
attbyval | boolean | not null | plain |
attstorage | "char" | not null | plain |
attalign | "char" | not null | plain |
attnotnull | boolean | not null | plain |
atthasdef | boolean | not null | plain |
attisdropped | boolean | not null | plain |
attislocal | boolean | not null | plain |
attinhcount | integer | not null | plain |
Indexes:
"pg_attribute_relid_attnam_index" UNIQUE, btree (attrelid, attname)
"pg_attribute_relid_attnum_index" UNIQUE, btree (attrelid, attnum)
Has OIDs: no
Postgres functions pg_relation_size provides the size of the index.
gpadmin=# SELECT pg_relation_size('pg_attribute_relid_attnam_index') ;
pg_relation_size
------------------
20905984
(1 row)
Using pg_size_pretty below will print the size in human readable format (e.g., 1K 234M 2G).
gpadmin=# SELECT pg_size_pretty(pg_relation_size('pg_attribute_relid_attnam_index')) ;
pg_size_pretty
----------------
20 MB
(1 row)
Reindex the index and compare the size again:
gpadmin=# REINDEX INDEX pg_attribute_relid_attnam_index ;
REINDEX
gpadmin=# SELECT pg_size_pretty(pg_relation_size('pg_attribute_relid_attnam_index')) ;
pg_size_pretty
----------------
15 MB
(1 row)
Alternatively, to reindex the complete index on the table, run the following:
gpadmin=# REINDEX TABLE pg_catalog.pg_attribute ; REINDEX
To reindex all of the catalog tables of the database that you are currently connected too, run the following:
gpadmin=# REINDEX SYSTEM gpadmin ; NOTICE: table "pg_class" was reindexed NOTICE: table "pg_window" was reindexed NOTICE: table "pg_exttable" was reindexed NOTICE: table "pg_appendonly" was reindexed NOTICE: table "pg_appendonly_alter_column" was reindexed NOTICE: table "gp_fastsequence" was reindexed NOTICE: table "pg_extprotocol" was reindexed . .... output truncated . . NOTICE: table "gp_distribution_policy" was reindexed (seg4 sdw1:40014 pid=30480) NOTICE: table "gp_distribution_policy" was reindexed (seg9 sdw2:40013 pid=4926) NOTICE: table "gp_distribution_policy" was reindexed (seg6 sdw2:40010 pid=4920) REINDEX
Feedback
