pg_store_plans

os: ubuntu 16.04
db: postgresql 10.6

版本

# lsb_release -a
No LSB modules are available.
Distributor ID:	Ubuntu
Description:	Ubuntu 16.04.5 LTS
Release:	16.04
Codename:	xenial
#
# su - postgres
$ psql -c "select version();"
                                                     version                                                     
-----------------------------------------------------------------------------------------------------------------
 PostgreSQL 10.6 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.11) 5.4.0 20160609, 64-bit
(1 row)

下载安装

# su - postgres
$ git clone https://github.com/ossc-db/pg_store_plans.git
$ cd pg_store_plans/
$ git branch -a

此时需要将 pg_store_plans 目录放置到编译通过的PG工程的"…/contrib/"目录下

$ cp -R ~/pg_store_plans ~/postgresql-10.6/contrib/
$ cd ~/postgresql-10.6/contrib/pg_store_plans

$ make
$ make install
/bin/mkdir -p '/usr/pgsql-10/lib'
/bin/mkdir -p '/usr/pgsql-10/share/extension'
/bin/mkdir -p '/usr/pgsql-10/share/extension'
/bin/mkdir -p '/usr/pgsql-10/lib'
/usr/bin/install -c -m 755  pg_store_plans.so '/usr/pgsql-10/lib/pg_store_plans.so'
/usr/bin/install -c -m 644 ./pg_store_plans.control '/usr/pgsql-10/share/extension/'
/usr/bin/install -c -m 644 ./pg_store_plans--1.3.sql ./pg_store_plans--1.2--1.3.sql  '/usr/pgsql-10/share/extension/'
/usr/bin/install -c -m 755  pg_store_plans.so '/usr/pgsql-10/lib/'

使用

修改参数

$ vi postgresql.conf
shared_preload_libraries = 'pg_hint_plan, pg_store_plans, pg_stat_statements'
pg_store_plans.max = 10000
pg_store_plans.track = all

#pg_store_plans.max (integer)
#pg_store_plans.track (enum)
#pg_store_plans.plan_format (enum)
#pg_store_plans.min_duration (integer)
#pg_store_plans.log_analyze (boolean)
#pg_store_plans.log_buffers (boolean)
#pg_store_plans.log_timing (boolean)
#pg_store_plans.log_triggers (boolean)
#pg_store_plans.verbose (boolean)
#pg_store_plans.save (boolean)

这些参数的说明可以看 ./pg_store_plans/doc/index.html

postgres=# select * from pg_available_extensions where name like 'pg_store_plans';
      name      | default_version | installed_version |                       comment                        
----------------+-----------------+-------------------+------------------------------------------------------
 pg_store_plans | 1.3             |                   | track plan statistics of all SQL statements executed
(1 row)

postgres=# create extension pg_store_plans;
postgres=# create extension pg_stat_statements;

postgres=# \dx
                                     List of installed extensions
        Name        | Version |   Schema   |                        Description                        
--------------------+---------+------------+-----------------------------------------------------------
 pg_hint_plan       | 1.3.3   | hint_plan  | 
 pg_stat_statements | 1.6     | public     | track execution statistics of all SQL statements executed
 pg_store_plans     | 1.3     | public     | track plan statistics of all SQL statements executed
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
(4 rows)

postgres=# SELECT s.query, p.plan,p.calls as "plan calls", s.calls as "stmt calls",
             p.total_time / p.calls as "time/call", p.first_call, p.last_call
        FROM pg_stat_statements s
             JOIN pg_store_plans p 
			      ON ( p.queryid = pg_store_plans_hash_query(s.query) )
        ORDER BY query ASC, "time/call" DESC;
		

参考:
https://github.com/ossc-db/pg_store_plans