How to use pg_stat_monitor on PostgreSQL

This setup using PostgreSQL 12 on CentOS 7

After this setup, you can check the query plan for query that already done on PostgreSQL.

Install PostgreSQL repository package

yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

Install extension pg_stat_monitor

yum install -y pg_stat_monitor_12

Add extension in Postgresql configuration

...
shared_preload_libraries = 'pg_stat_monitor'    # (change requires restart)
...

pg_stat_monitor.pgsm_query_max_len = 4096
pg_stat_monitor.pgsm_normalized_query = 1
pg_stat_monitor.pgsm_enable_query_plan = 1

Restart PostgreSQL service

systemctl restart postgresql-12

Enter to psql and create the extension

psql -U postgres  
postgres=# create extension pg_stat_monitor;
postgres=# select query, query_plan from pg_stat_monitor;