PG重建索引

postgres=# select version();
                                                        version                                                         
------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 8.1.22 on x86_64-unknown-linux-gnu, compiled by GCC gcc (SUSE Linux) 4.3.4 [gcc-4_3-branch revision 152973]
(1 row)

注意：reindex 会造成全表锁。

postgres=# reindex index t_idx;    -->重建单个索引
REINDEX


postgres=# reindex table test;     -->重建和表test有关的索引
REINDEX


postgres@linux-o8rc:~> reindexdb postgres    -->重建postgres数据库下所有的索引
NOTICE:  table "pg_class" was reindexed
NOTICE:  table "pg_class" was reindexed
NOTICE:  table "sql_features" was reindexed
NOTICE:  table "sql_features" was reindexed
NOTICE:  table "sql_implementation_info" was reindexed
NOTICE:  table "sql_implementation_info" was reindexed
NOTICE:  table "sql_languages" was reindexed
NOTICE:  table "sql_languages" was reindexed
NOTICE:  table "sql_packages" was reindexed
NOTICE:  table "sql_packages" was reindexed
NOTICE:  table "sql_sizing" was reindexed
NOTICE:  table "sql_sizing" was reindexed
NOTICE:  table "sql_sizing_profiles" was reindexed
NOTICE:  table "sql_sizing_profiles" was reindexed
NOTICE:  table "pg_statistic" was reindexed
NOTICE:  table "pg_statistic" was reindexed
NOTICE:  table "pg_type" was reindexed
NOTICE:  table "pg_type" was reindexed
NOTICE:  table "pg_attribute" was reindexed
NOTICE:  table "pg_attribute" was reindexed
NOTICE:  table "pg_autovacuum" was reindexed
NOTICE:  table "pg_autovacuum" was reindexed
NOTICE:  table "pg_inherits" was reindexed
NOTICE:  table "pg_inherits" was reindexed
NOTICE:  table "pg_index" was reindexed
NOTICE:  table "pg_index" was reindexed
NOTICE:  table "pg_operator" was reindexed
NOTICE:  table "pg_operator" was reindexed
NOTICE:  table "pg_opclass" was reindexed
NOTICE:  table "pg_opclass" was reindexed
NOTICE:  table "pg_am" was reindexed
NOTICE:  table "pg_am" was reindexed
NOTICE:  table "pg_amop" was reindexed
NOTICE:  table "pg_amop" was reindexed
NOTICE:  table "pg_amproc" was reindexed
NOTICE:  table "pg_amproc" was reindexed
NOTICE:  table "pg_language" was reindexed
NOTICE:  table "pg_language" was reindexed
NOTICE:  table "pg_largeobject" was reindexed
NOTICE:  table "pg_largeobject" was reindexed
NOTICE:  table "pg_aggregate" was reindexed
NOTICE:  table "pg_aggregate" was reindexed
NOTICE:  table "pg_trigger" was reindexed
NOTICE:  table "pg_trigger" was reindexed
NOTICE:  table "pg_cast" was reindexed
NOTICE:  table "pg_cast" was reindexed
NOTICE:  table "pg_namespace" was reindexed
NOTICE:  table "pg_namespace" was reindexed
NOTICE:  table "pg_conversion" was reindexed
NOTICE:  table "pg_conversion" was reindexed
NOTICE:  table "pg_depend" was reindexed
NOTICE:  table "pg_depend" was reindexed
NOTICE:  table "tbl_kenyon" was reindexed
NOTICE:  table "tbl_kenyon" was reindexed
NOTICE:  table "test" was reindexed
NOTICE:  table "test" was reindexed
NOTICE:  table "pg_attrdef" was reindexed
NOTICE:  table "pg_attrdef" was reindexed
NOTICE:  table "pg_constraint" was reindexed
NOTICE:  table "pg_constraint" was reindexed
NOTICE:  table "pg_description" was reindexed
NOTICE:  table "pg_description" was reindexed
NOTICE:  table "pg_proc" was reindexed
NOTICE:  table "pg_proc" was reindexed
NOTICE:  table "pg_rewrite" was reindexed
NOTICE:  table "pg_rewrite" was reindexed
REINDEX


postgres=# reindex database postgres;   -->重建postgres数据库下所有的索引
NOTICE:  table "pg_class" was reindexed
NOTICE:  table "sql_features" was reindexed
NOTICE:  table "sql_implementation_info" was reindexed
NOTICE:  table "sql_languages" was reindexed
NOTICE:  table "sql_packages" was reindexed
NOTICE:  table "sql_sizing" was reindexed
NOTICE:  table "sql_sizing_profiles" was reindexed
NOTICE:  table "pg_statistic" was reindexed
NOTICE:  table "pg_autovacuum" was reindexed
NOTICE:  table "pg_inherits" was reindexed
NOTICE:  table "pg_operator" was reindexed
NOTICE:  table "pg_opclass" was reindexed
NOTICE:  table "pg_am" was reindexed
NOTICE:  table "pg_amop" was reindexed
NOTICE:  table "pg_amproc" was reindexed
NOTICE:  table "pg_language" was reindexed
NOTICE:  table "pg_largeobject" was reindexed
NOTICE:  table "pg_aggregate" was reindexed
NOTICE:  table "pg_trigger" was reindexed
NOTICE:  table "pg_cast" was reindexed
NOTICE:  table "pg_namespace" was reindexed
NOTICE:  table "pg_conversion" was reindexed
NOTICE:  table "tbl_kenyon" was reindexed
NOTICE:  table "test" was reindexed
NOTICE:  table "pg_attrdef" was reindexed
NOTICE:  table "pg_constraint" was reindexed
NOTICE:  table "pg_description" was reindexed
NOTICE:  table "pg_proc" was reindexed
NOTICE:  table "pg_rewrite" was reindexed
NOTICE:  table "pg_type" was reindexed
NOTICE:  table "pg_attribute" was reindexed
NOTICE:  table "pg_index" was reindexed
NOTICE:  table "pg_depend" was reindexed
REINDEX