Using gp_relaccess_stats in Yandex MPP Analytics for PostgreSQL

gp_relaccess_stats is a Yandex Cloud extension that collects the access statistics for tables and views. It registers the read, insert, update, and delete operations, as well as the timestamp of the user's last access to the object. The collected data helps find unused or improperly used objects.

Extension settingsExtension settings

For more information about the extension, see this gp_relaccess_stats guide.

Installing gp_relaccess_stats in a Greenplum® clusterInstalling gp_relaccess_stats in a Greenplum® cluster

1. Connect to the database as the owner or a user with the CREATE permission in the database and run this command:

   CREATE EXTENSION gp_relaccess_stats;
   

2. Make sure the extension has been installed:

   SELECT extname FROM pg_extension;
   

   This will bring up the list of extensions installed in the database.

Use casesUse cases

1. Create a statistics collection table:

   SELECT relaccess_stats_init();
   

   The relaccess_stats table will get new empty entries for each table and partition of the selected database.

2. Update the statistics:

   SELECT relaccess_stats_update();
   

   The relaccess_stats table will now be populated with statistics from the cache and the pg_stat folder.

3. Check the current usage of the statistics cache:

   SELECT relaccess.relaccess_stats_fillfactor();
   

   This will show the statistics cache fill percentage.

4. Query the relaccess_stats table for statistical data:

   SELECT * FROM relaccess_stats;
   

Greenplum® and Greenplum Database® are registered trademarks or trademarks of Broadcom Inc. in the United States and/or other countries.