Re: How to detect if a postgresql gin index is bloated

On Fri, Jul 26, 2024 at 6:49 AM khan Affan <bawag773(at)gmail(dot)com> wrote:

> The pgstattuple function provides the following useful columns:
>
> - table_len: The length of the table.
> - tuple_count: The number of tuples.
> - tuple_len: The length of the tuples.
> - dead_tuple_count: The number of dead tuples.
> - dead_tuple_len: The length of dead tuples.
> - free_space: The amount of free space.
>
> Example
>
> How you might use this function:
>
>
> SELECT * FROM pgstattuple('gin_index_name');
>
> Interpreting Results
>
> - If the dead_tuple_count is high relative to the tuple_count, this
> indicates bloat.
> - If free_space is a significant portion of table_len, the index may
> be bloated.
>
> Using pg_trgm for Trigram Indexes
>
> If you're using a GIN index with the pg_trgm extension (for trigram
> indexes), you might also consider analyzing the table and index sizes
> directly:
>
>
> SELECT
> pg_size_pretty(pg_relation_size('table_name')) AS table_size,
> pg_size_pretty(pg_total_relation_size('table_name')) AS total_size,
> pg_size_pretty(pg_relation_size('gin_index_name')) AS index_size;
>
> Vacuum and Reindex
>
> If you determine that your GIN index is bloated, you can use VACUUM and
> REINDEX to reclaim space:
>
> VACUUM ANALYZE your_table_name;
> REINDEX INDEX your_gin_index_name;
>
> By following these steps, you should be able to detect and address bloat
> in your PostgreSQL GIN indexes effectively.
>
>
>
Have you tried using that pgstattuple function with a GIN index?

postgres=# select * from pgstattuple('employees_systems_access');
ERROR: "employees_systems_access" (gin index) is not supported

There is a function specifically for them and it does not return the same
info

postgres=# select * from pgstatginindex('employees_systems_access');
-[ RECORD 1 ]--+--
version | 2
pending_pages | 0
pending_tuples | 0

--
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com