Measuring query and index block statistics

In this recipe, we will be discussing how to measure the index statistics, using various catalog views.

Getting ready

PostgreSQL offers a few catalog views and extensions, which are enough to study the index usage statistics. The catalog views are pg_stat_user_indexes and 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...

  1. 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) ...

Get PostgreSQL High Performance Cookbook now with the O’Reilly learning platform.

O’Reilly members experience live online training, plus books, videos, and digital content from nearly 200 publishers.