mv_stats extension ====================================== The mv_stats extension is a means for tracking some statistics of all materialized views in a database. The extension must be loaded using the the PostgreSQL's clause `CREATE EXTENSION` When `mv_stat` is loaded, it begin to tracks statistics about materialized views in this databases. To access and manipulate these statistics, the module provides a view named `mv_stats`, and the utility function and `mv_activity_reset_stats`. The statistics gathered by the module are made available via a view named mv_stats. This view contains one row for each distinct materialized view in the database, the columns of the view are shown in the following table. | Column | Type | Description | |-----------------------|---------------|--------------| | mv_name | text | Name of MV schema-qualified| | create_mv | timestamp | Timestamp of MV creation (`CREATE MATERIALIZED VIEW`), `NULL` means that MV existed before the extension and was loaded when creating the extension | | mod_mv | timestamp | Timestamp of MV Modification (`ALTER MATERIALIZED VIEW`) | | refresh_mv_last | timestamp | Timestamp of last time that MV was refreshed (`REFRESH MATERIALIZED VIEW`)| | refresh_count | int | Number of times refreshed | | refresh_mv_time_last | interval | Duration of last refresh time | | refresh_mv_time_total | interval | Total refresh time | | refresh_mv_time_min | interval | Min refresh time | | refresh_mv_time_max | interval | Max refresh time | | reset_last | timestamp | Timestamp of last stats reset | Install -------- *Required PG10+ (tested)* Run: ``` make install ``` If not install, you must make sure you can see the binary pg_config, maybe setting PostgreSQL binary path in the OS or setting PG_CONFIG = /path_to_pg_config/ in the makefile or run: `make install PG_CONFIG=/path_to_pg_config/` In your database execute: ``` CREATE EXTENSION mv_stats; ``` If you have some views created previously they will be automatically added to the stats on blank, and after the refresh, stats will be fit ``` test=# SELECT mv_name,create_mv,mod_mv,refresh_mv_last as refresh_last, refresh_count, refresh_mv_time_last as refresh_time_last , refresh_mv_time_total as refresh_time_total, refresh_mv_time_min as refresh_time_min,refresh_mv_time_max as refresh_time_max, reset_last FROM mv_stats ; mv_name | create_mv | mod_mv | refresh_last | refresh_count | refresh_time_last | refresh_time_total | refresh_time_min | refresh_time_max | reset_last -------------------+----------------------------+--------+---------------------------+---------------+-------------------+--------------------+------------------+------------------+------- public.mv1 | | | | 0 | | 00:00:00 | | | ``` --Function: `mv_activity_reset_stats (mview):` Reset the statistics collected, `mview` default value is `*`, means all MV, but can be define a specific MV passing the name of this view using the schema-qualified name, only for superuser Example of use: -------- Can check the statistics collected by the extension quering the view `mv_stats`: ``` test=# CREATE MATERIALIZED VIEW mv_example AS SELECT * FROM pg_stat_activity ; test=# REFRESH MATERIALIZED VIEW mv_example ; test=# SELECT mv_name,create_mv,mod_mv,refresh_mv_last as refresh_last, refresh_count, refresh_mv_time_last as refresh_time_last , refresh_mv_time_total as refresh_time_total, refresh_mv_time_min as refresh_time_min,refresh_mv_time_max as refresh_time_max, reset_last FROM mv_stats ; mv_name | create_mv | mod_mv | refresh_last | refresh_count | refresh_time_last | refresh_time_total | refresh_time_min | refresh_time_max | reset_last -------------------+----------------------------+--------+---------------------------+---------------+-------------------+--------------------+------------------+------------------+------- public.mv_example | 2021-02-03 15:32:35.826251 | | 2021-02-03 15:32:45.37572 | 1 | 00:00:00.45811 | 00:00:00.45811 | 00:00:00.45811 | 00:00:00.45811 | (1 row) ``` To reset the statistic collected you can use the function `mv_activity_reset_stats` ``` -- for specific MV test=# SELECT * FROM mv_activity_reset_stats ('public.mv_example'); mv_activity_reset_stats ------------------------- public.mv_example (1 row) test=# SELECT mv_name,create_mv,mod_mv,refresh_mv_last as refresh_last, refresh_count, refresh_mv_time_last as refresh_time_last , refresh_mv_time_total as refresh_time_total, refresh_mv_time_min as refresh_time_min,refresh_mv_time_max as refresh_time_max, reset_last FROM mv_stats ; mv_name | create_mv | mod_mv | refresh_last | refresh_count | refresh_time_last | refresh_time_total | refresh_time_min | refresh_time_max | reset_last -------------------+----------------------------+--------+--------------+---------------+-------------------+--------------------+------------------+------------------+----------------- public.mv1 | | | | 0 | | 00:00:00 | | | public.mv_example | 2021-02-03 15:32:35.826251 | | | 0 | | 00:00:00 | | | 2021-02-03 15:38:37.540717 (2 rows) -- for all views test=# SELECT * FROM mv_activity_reset_stats (); mv_activity_reset_stats ------------------------- public.mv_example public.mv1 (2 rows) ``` The "extension" can be used in a PostgreSQL installation where you can not install extra extension (such us RDS, etc), you just must load the script mv_stats--0.2.0.sql in your database and enjoy it, to “remove the extension" in this case you can use the function select _mv_drop_objects(); ### UPDATE extension procedure After installing the new version of the extension please execute the extension update command inside the database _Note: after Update the extension the data for the view mv_stats will be deleted, if you want to keep it, please make a copy from this view in another table before updating the extension:_ ```sql CREATE TABLE mv_stats_old_data AS SELECT * FROM mv_stats; ALTER EXTENSION mv_stats UPDATE ; ``` ### Regression Tests ``` make installcheck or make installcheck PG_CONFIG=/path_to_pg_config/ ``` IMPORTANT: If you find some bugs in the existing version, please contact to me. Anthony R. Sotolongo León asotolongo@ongres.com asotolongo@gmail.com