0

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

Behlül
  • 3,412
  • 2
  • 29
  • 46
  • I don't think we can get very far with this info. What other columns are there? What other indexes? How often is it updated? Which columns get updated? Can we see some sample ltree data? – jjanes Apr 18 '22 at 14:22
  • It is not updated very often. There is only 31K entries anyway. How can the index size go all the way up to 182 GB from 8MB ? – Behlül Apr 18 '22 at 15:16
  • Without knowing some of the history of the table, there is no way to know. Just keep an eye on it and see if it reoccurs. Maybe you had some giant out-of-control INSERT which errored out (without ever committing). The excessive space on the table could then be trimmed off after the next vacuum, but the excessive index space would not be--it would just hang around for future reuse. – jjanes Apr 18 '22 at 18:19
  • Thanks I will keep monitoring and update if I see a usage pattern. – Behlül Apr 18 '22 at 20:01

0 Answers0