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)
   

2. Let's reset the statistics using the pg_stat_reset function, as follows:

           benchmarksql=# SELECT pg_stat_reset(); 
           pg_stat_reset  
           ---...