Check Analytics

SELECT relname , indexrelname , idx_scan , idx_tup_read , idx_tup_fetch
FROM pg_stat_user_indexes
WHERE schemaname = 'public' and relname = 'table_name'

Information details

In PostgreSQL, the terms idx_tup_read, idx_tup_fetch, and idx_scan are related to index usage statistics, which you might encounter when analyzing query performance using EXPLAIN ANALYZE statements or viewing database statistics. Understanding these terms can help in optimizing database queries and overall database performance. Here's a brief explanation of each term:

idx_tup_read

• Definition: This metric indicates the number of index entries that were read by the query. When a query executes, PostgreSQL may read multiple index entries to find the rows that match the query criteria.
• Context: A high idx_tup_read value compared to idx_tup_fetch might indicate that the index is being scanned more than necessary to find the relevant rows, which can happen with non-unique indexes or indexes on columns with low cardinality.

idx_tup_fetch

• Definition: This metric shows the number of rows fetched after reading the index entries. Essentially, it's the count of rows actually returned or used by the query after finding them through the index.
• Context: Ideally, idx_tup_fetch should be close to idx_tup_read, indicating that most of the index entries read were relevant and used. A large difference may suggest inefficiencies, such as the index not being selective enough or the query fetching more data than needed.

idx_scan

• Definition: This metric counts the number of index scans initiated by the query. An index scan occurs when PostgreSQL uses an index to satisfy a query, as opposed to a sequential scan of the entire table.
• Context: The idx_scan count helps understand how often an index is being used. A higher count typically indicates that the index is beneficial and being utilized by queries. However, the effectiveness also depends on other factors like the specific queries and the database schema.

Practical Implications

• Optimization: Understanding these metrics can help in diagnosing performance issues and optimizing queries. For example, if idx_tup_read is significantly higher than idx_tup_fetch, it might be beneficial to consider index adjustments or query modifications.
• Index Usage: Regularly monitoring these metrics can guide decisions on creating, modifying, or removing indexes based on their usage and effectiveness in improving query performance.

By analyzing these metrics in the context of your specific database workload, you can gain insights into how well your indexes are performing and identify potential areas for optimization.

https://sgerogia.github.io/Postgres-Index-And-Queries/ (opens in a new tab)