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 O’Reilly online learning.

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