PostgreSQL Index Expansion

Index expansion, mainly for B-tree

Several sources of index bloat:

After a large number of deletions, the index pages are sparse, which reduces the efficiency of indexing;

PG9.0 the previous version, vacuum full will also cause the index page sparse;

Long-running transactions that prohibit vacuum cleanup of the table two cause the page sparse state to persist consistently.

Note that Reindex will cause full table lock, do not operate when the system is busy

To view the space occupied by the index:

Select Pg_relation_size (OID)/1024/1024,relname from Pg_class where relkind= ' i ' ORDER by pg_relation_size (OID) desc limit 20;

To view a specific index size

Select Pg_relation_size (' tbl_test_p1_pk ')/1024/1024;

Rebuilding a specific index

Reindex index TBL_TEST_P1_PK;

Update STATISTICS for a database

Analyze tbl_test;

To view a specific index size

Select Pg_relation_size (' tbl_test_p1_pk ')/1024/1024;

PostgreSQL Index Expansion