Pivotal Knowledge Base

Follow

How to reindex system catalogs

Goal

In heavily used databases where objects are frequently created/dropped/altered, the catalog indexes will occupy more space , this is called as index bloat, this article will help you understand on what can be done to fix the Index bloat .

Solution

To remove this index bloat we have to recreate the index as vacuum works only on tables only and not on indexes , you can schedule this activity during system maintenance like routine Vacuum and Analyze.

To recreate the index you can use either 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 specific index or the whole table with the below commands :

REINDEX TABLE tablename ;
REINDEX INDEX index_name ;

PLEASE 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 is 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 comparing 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 you can too reindex the complete index on the table with

gpadmin=# REINDEX TABLE pg_catalog.pg_attribute ;
REINDEX

or reindexing all of the catalog tables of database that you are currently connected.

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

Comments

  • Avatar
    Alfredo Gomez

    Hi, I am wondering, is it REINDEX acting only on catalogue/system tables or on any table of the database?

    Thanks!

Powered by Zendesk