Measuring query and index block statistics
In this recipe, we will be discussing how to measure the index statistics, using various catalog views.
PostgreSQL offers a few catalog views and extensions, which are enough to study the index usage statistics. The catalog views are
pg_statio_user_indexes. These give the index usage statistics, and the extension
pgstattuple provides insight into the details of the index by reading its physical files.
How to do it...
- Let's get a sample non-primary key index to measure its statistics, as follows:
benchmarksql=# SELECT indexrelid::regclass FROM pg_index WHERE indisprimary IS FALSE AND indrelid::regclass::text='bmsql_item' LIMIT 1; indexrelid ------------ pric_idx (1 row) ...