Postgres Pro
Downloads
Home   >   mailing lists

Re: How to detect if a postgresql gin index is bloated - Mailing list pgsql-admin

From khan Affan
Subject Re: How to detect if a postgresql gin index is bloated
Date
Msg-id CAF4emOknh9AbkmGkRJ=FJyn_EAnQOcTppjpiQp5mORYEvaU4Jw@mail.gmail.com
Whole thread Raw
In response to Re: How to detect if a postgresql gin index is bloated  (Keith Fiske <keith.fiske@crunchydata.com>)
Responses RE: [EXT] Re: How to detect if a postgresql gin index is bloated
List pgsql-admin
Tree view
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.


 image.png

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


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

pgsql-admin by date:

Previous
From: Tom Lane
Date:
Subject: Re: Migration from CentOS7 to RHEL red hat Linux
Next
From: "Wong, Kam Fook (TR Technology)"
Date:
Subject: RE: [EXT] Re: How to detect if a postgresql gin index is bloated