I have a gist index for a ltree field, declared like below:
path | ltree | | |
I realized this index grew quickly to 185GB, even though I didn't insert crazy amount of content. Note the size and count (31k) output below:
fa_category | fa_category_path_idx | 182 GB | 182 GB | 16 MB | 182 GB | 31444
After dropping and recreating the exact index, its size dropped to 8MBs, any idea what may be causing this and how to prevent it from happening again?
DROP INDEX IF EXISTS fa_category_path_idx;
CREATE INDEX IF NOT EXISTS fa_category_path_idx on fa_category using gist (path);
after recreation:
SELECT i.relname "Table Name",indexrelname "Index Name",
pg_size_pretty(pg_total_relation_size(relid)) As "Total Size",
pg_size_pretty(pg_indexes_size(relid)) as "Total Size of all Indexes",
pg_size_pretty(pg_relation_size(relid)) as "Table Size",
pg_size_pretty(pg_relation_size(indexrelid)) "Index Size",
reltuples::bigint "Estimated table row count"
FROM pg_stat_all_indexes i JOIN pg_class c ON i.relid=c.oid
WHERE i.relname = 'fa_category'
;
Table Name | Index Name | Total Size | Total Size of all Indexes | Table Size | Index Size | Estimated table row count
-------------+----------------------------------------+------------+---------------------------+------------+------------+---------------------------
fa_category | fa_category_path_idx | 31 MB | 15 MB | 16 MB | 8864 kB | 31444
I am using AWS RDS Postgres 13.4