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

[khan Affan <bawag773@xxxxxxxxx>]

Hi

Let me clarify this for you. Although pgstattuple is a widely used extension included in PostgreSQL’s source code,

It is not precompiled with the database installation. To use it, you first need to build it from the source code in the 'contrib' directory. 

After building the extension, start your PostgreSQL server and connect to the database using psql.

For your case:

postgres=# select * from pgstattuple('employees_systems_access');
ERROR:  "employees_systems_access" (gin index) is not supported; I misprinted table_name with gindex_name.

this is true pgstattuple  support table and standard indexes 

I consider analyzing the table and index sizes directly,
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.

And used pgstattuple for tables and standard indexes.
pgstatginindex specifically for GIN indexes to get relevant statistics and detect bloat.

Then, if needed, use re-index or vaccum as per requirement.

 Make sure the pgstattuple extension is installed and properly configured in your PostgreSQL database before using these functions.  

I recently build the source code from 17 beta. And attaching the extension version 1.5 file as well.

 

On Fri, Jul 26, 2024 at 6:11 PM Keith Fiske <keith.fiske@xxxxxxxxxxxxxxx> wrote:

On Fri, Jul 26, 2024 at 6:49 AM khan Affan <bawag773@xxxxxxxxx> 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

Attachment: pgstattuple--1.4--1.5.sql
Description: Binary data