From: | Keith Fiske <keith(dot)fiske(at)crunchydata(dot)com> |
---|---|
To: | khan Affan <bawag773(at)gmail(dot)com> |
Cc: | "Wong, Kam Fook (TR Technology)" <kamfook(dot)wong(at)thomsonreuters(dot)com>, Pgsql-admin <pgsql-admin(at)lists(dot)postgresql(dot)org> |
Subject: | Re: How to detect if a postgresql gin index is bloated |
Date: | 2024-07-26 13:10:35 |
Message-ID: | CAODZiv6nmwDEwU5z5chvTEbnHF3yOD7b+_ZfrBsKhyzYTe6VXQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Wetmore, Matthew (CTR) | 2024-07-26 13:13:23 | RE: [EXTERNAL] Re: Queries are failing on standby server |
Previous Message | khan Affan | 2024-07-26 10:49:38 | Re: How to detect if a postgresql gin index is bloated |