Database Reference
In-Depth Information
Some queries used to get the size/rows of the index and table are as
follows:
warehouse_db=# CREATE TABLE table_name AS SELECT * FROM
generate_series(1, 9999999) AS
COLUMN_KEY;
SELECT 9999999
warehouse_db=# CREATE INDEX index_name ON table_name
(column_key);
CREATE INDEX
warehouse_db=# SELECT table_len/(1024*1024) table_size,
tuple_count total_rows FROM
pgstattuple('table_name');
table_size | total_rows
------------+------------
345 | 9999999
(1 row)
warehouse_db=# SELECT table_len/(1024*1024) table_size,
tuple_count total_rows FROM
pgstattuple('index_name');
table_size | total_rows
------------+------------
214 | 9999999
(1 row)
Reindexing
If an index becomes ineficient due to bloating or data becomes randomly scattered,
then reindexing is required to get the maximum performance from the index.
Its syntax is as follows:
warehouse_db=# REINDEX TABLE item;
