pg_show_plans

亦凉 2022-10-29 12:24 195阅读 0赞

os: centos 7.4  
db: postgresql 12.6

pg\_show\_plans、pg\_store\_plans 都可以被用来查看 postgresql 当前执行语句的执行计划。  
本篇blog介绍 pg\_show\_plans 的初步使用.  
pg\_store\_plans 可以参考另外一篇blog<<[pg\_store\_plans][pg_store_plans]>>

# 版本 #

# cat /etc/centos-release
    CentOS Linux release 7.4.1708 (Core) 
    
    # yum list installed |grep -i postgresql
    postgresql12.x86_64                12.6-1PGDG.rhel7                    @pgdg12  
    postgresql12-contrib.x86_64        12.6-1PGDG.rhel7                    @pgdg12  
    postgresql12-devel.x86_64          12.6-1PGDG.rhel7                    @pgdg12  
    postgresql12-docs.x86_64           12.6-1PGDG.rhel7                    @pgdg12  
    postgresql12-libs.x86_64           12.6-1PGDG.rhel7                    @pgdg12  
    postgresql12-llvmjit.x86_64        12.6-1PGDG.rhel7                    @pgdg12  
    postgresql12-odbc.x86_64           13.00.0000-1PGDG.rhel7              @pgdg12  
    postgresql12-odbc-debuginfo.x86_64 12.02.0000-1PGDG.rhel7              @pgdg-common
    postgresql12-plperl.x86_64         12.6-1PGDG.rhel7                    @pgdg12  
    postgresql12-plpython.x86_64       12.6-1PGDG.rhel7                    @pgdg12  
    postgresql12-plpython3.x86_64      12.6-1PGDG.rhel7                    @pgdg12  
    postgresql12-pltcl.x86_64          12.6-1PGDG.rhel7                    @pgdg12  
    postgresql12-server.x86_64         12.6-1PGDG.rhel7                    @pgdg12  
    postgresql12-tcl.x86_64            2.7.5-1.rhel7                       @pgdg12  
    postgresql12-test.x86_64           12.6-1PGDG.rhel7                    @pgdg12

# 下载安装 #

# su - postgres
    $ cat ~/.bash_profile
    export PGHOME=/usr/pgsql-12
    export PGDATA=/var/lib/pgsql/12/data
    
    export MANPATH=$PGHOME/share/man:$MANPATH
    export LD_LIBRARY_PATH=$PGHOME/lib
    export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib
    export PATH=$PGHOME/bin:$PATH
    
    export TEMP=/tmp
    export TMPDIR=/tmp

$ cd /var/lib/pgsql
    $ git clone https://github.com/cybertec-postgresql/pg_show_plans.git
    
    $ cd pg_show_plans
    $ USE_PGXS=1 make
    $ sudo USE_PGXS=1 make
    
    /usr/bin/mkdir -p '/usr/pgsql-12/lib'
    /usr/bin/mkdir -p '/usr/pgsql-12/share/extension'
    /usr/bin/mkdir -p '/usr/pgsql-12/share/extension'
    /usr/bin/install -c -m 755  pg_show_plans.so '/usr/pgsql-12/lib/pg_show_plans.so'
    /usr/bin/install -c -m 644 .//pg_show_plans.control '/usr/pgsql-12/share/extension/'
    /usr/bin/install -c -m 644 .//pg_show_plans--1.0.sql  '/usr/pgsql-12/share/extension/'
    /usr/bin/mkdir -p '/usr/pgsql-12/lib/bitcode/pg_show_plans'
    /usr/bin/mkdir -p '/usr/pgsql-12/lib/bitcode'/pg_show_plans/
    /usr/bin/install -c -m 644 pg_show_plans.bc '/usr/pgsql-12/lib/bitcode'/pg_show_plans/./
    cd '/usr/pgsql-12/lib/bitcode' && /usr/lib64/llvm5.0/bin/llvm-lto -thinlto -thinlto-action=thinlink -o pg_show_plans.index.bc pg_show_plans/pg_show_plans.bc

# 使用 #

# vi /var/lib/pgsql/12/data/postgresql.conf
    
    shared_preload_libraries = 'pg_show_plans,pg_stat_statements'
    
    
    
    #  systemctl restart postgresql-12.service
    
    # su - postgres
    $ psql
    psql (12.6)
    Type "help" for help.
    
    postgres=# CREATE EXTENSION pg_show_plans;
    
    postgres=# \dxS+ pg_show_plans
    Objects in extension "pg_show_plans"
            Object description        
    ----------------------------------
     function pg_show_plans()
     function pg_show_plans_disable()
     function pg_show_plans_enable()
     function pgsp_format_json()
     function pgsp_format_text()
     view pg_show_plans
    (6 rows)

postgres=#  SELECT p.pid, p.level, p.plan, a.query 
      FROM pg_show_plans p 
       LEFT JOIN pg_stat_activity a
       ON p.pid = a.pid AND p.level = 0 ORDER BY p.pid, p.level;
    
     pid  | level |                                              plan                                              |                            query                             
    ------+-------+------------------------------------------------------------------------------------------------+--------------------------------------------------------------
     3392 |     0 | Sort  (cost=72.08..74.58 rows=1000 width=80)                                                  +| SELECT p.pid, p.level, p.plan, a.query                      +
          |       |   Sort Key: pg_show_plans.pid, pg_show_plans.level                                            +|   FROM pg_show_plans p                                      +
          |       |   ->  Hash Left Join  (cost=2.25..22.25 rows=1000 width=80)                                   +|    LEFT JOIN pg_stat_activity a                             +
          |       |         Hash Cond: (pg_show_plans.pid = s.pid)                                                +|    ON p.pid = a.pid AND p.level = 0 ORDER BY p.pid, p.level;
          |       |         Join Filter: (pg_show_plans.level = 0)                                                +| 
          |       |         ->  Function Scan on pg_show_plans  (cost=0.00..10.00 rows=1000 width=48)             +| 
          |       |         ->  Hash  (cost=1.00..1.00 rows=100 width=44)                                         +| 
          |       |               ->  Function Scan on pg_stat_get_activity s  (cost=0.00..1.00 rows=100 width=44) | 
    (1 row)

使用 explain 查看

postgres=#  explain analyze SELECT p.pid, p.level, p.plan, a.query 
      FROM pg_show_plans p 
       LEFT JOIN pg_stat_activity a
       ON p.pid = a.pid AND p.level = 0 ORDER BY p.pid, p.level;
    
                                                                    QUERY PLAN                                                                
    ------------------------------------------------------------------------------------------------------------------------------------------
     Sort  (cost=72.08..74.58 rows=1000 width=80) (actual time=0.133..0.135 rows=1 loops=1)
       Sort Key: pg_show_plans.pid, pg_show_plans.level
       Sort Method: quicksort  Memory: 26kB
       ->  Hash Left Join  (cost=2.25..22.25 rows=1000 width=80) (actual time=0.113..0.115 rows=1 loops=1)
             Hash Cond: (pg_show_plans.pid = s.pid)
             Join Filter: (pg_show_plans.level = 0)
             ->  Function Scan on pg_show_plans  (cost=0.00..10.00 rows=1000 width=48) (actual time=0.017..0.018 rows=1 loops=1)
             ->  Hash  (cost=1.00..1.00 rows=100 width=44) (actual time=0.079..0.080 rows=6 loops=1)
                   Buckets: 1024  Batches: 1  Memory Usage: 9kB
                   ->  Function Scan on pg_stat_get_activity s  (cost=0.00..1.00 rows=100 width=44) (actual time=0.068..0.071 rows=6 loops=1)
     Planning Time: 0.317 ms
     Execution Time: 0.527 ms
    (12 rows)

参考：  
https://github.com/cybertec-postgresql/pg\_show\_plans

[pg_store_plans]: https://blog.csdn.net/ctypyb2002/article/details/88991144