# pg_show_plans **Repository Path**: csudata/pg_show_plans ## Basic Information - **Project Name**: pg_show_plans - **Description**: 显示正在运行的SQL的执行计划。从github上fork: https://github.com/cybertec-postgresql/pg_show_plans - **Primary Language**: C - **License**: PostgreSQL - **Default Branch**: master - **Homepage**: https://github.com/cybertec-postgresql/pg_show_plans - **GVP Project**: No ## Statistics - **Stars**: 1 - **Forks**: 0 - **Created**: 2022-12-11 - **Last Updated**: 2024-09-19 ## Categories & Tags **Categories**: Uncategorized **Tags**: None ## README # pg_show_plans `pg_show_plans` is a module which shows the query plans of all currently running SQL statements. You can select the output format of plans: TEXT or JSON. This module supports PostgreSQL versions from 9.5 to 14. ### Note When the server starts, pg_show_plans makes a hashtable on the shared-memory in order to temporarily store query plans. The hashtable size cannot be changed, so the plans are not stored if the hashtable is full. ## Version *Version 1.0* ## Installation You can install it to do the usual way shown below. ``` $ tar xvfj postgresql-13.0.tar.bz2 $ cd postgresql-13.0/contrib $ git clone https://github.com/cybertec-postgresql/pg_show_plans.git $ cd pg_show_plans $ make && make install ``` You must add the line shown below in your postgresql.conf. ``` shared_preload_libraries = 'pg_show_plans' ``` After starting your server, you must issue `CREATE EXTENSION` statement shown below. ``` testdb=# CREATE EXTENSION pg_show_plans; ``` ## How to use By issuing the following query, it shows the query plan and related information of the currently running SQL statements. ``` testdb=# SELECT * FROM pg_show_plans; pid | level | userid | dbid | plan -------+-------+--------+-------+----------------------------------------------------------------------- 11473 | 0 | 10 | 16384 | Function Scan on pg_show_plans (cost=0.00..10.00 rows=1000 width=56) 11504 | 0 | 10 | 16384 | Function Scan on print_item (cost=0.25..10.25 rows=1000 width=524) 11504 | 1 | 10 | 16384 | Result (cost=0.00..0.01 rows=1 width=4) (3 rows) ``` If you need the query plans of running SQL statements and also the corresponding query string, you issue the following query which is combined with pg_show_plans and pg_stat_activity. ``` testdb=# \x Expanded display is on. testdb=# 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; -[ RECORD 1 ]----------------------------------------------------------------------------------------- pid | 11473 level | 0 plan | Sort (cost=72.08..74.58 rows=1000 width=80) + | Sort Key: pg_show_plans.pid, pg_show_plans.level + | -> Hash Left Join (cost=2.25..22.25 rows=1000 width=80) + | 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) + | -> 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) query | 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; -[ RECORD 2 ]----------------------------------------------------------------------------------------- pid | 11517 level | 0 plan | Function Scan on print_item (cost=0.25..10.25 rows=1000 width=524) query | SELECT * FROM print_item(1,20); -[ RECORD 3 ]----------------------------------------------------------------------------------------- pid | 11517 level | 1 plan | Result (cost=0.00..0.01 rows=1 width=4) query | ``` ## pg_show_plans View - *pid*: the pid of the process which the query is running. - *level*: the level of the query which runs the query. Top level is `0`. For example, if you execute a simple select query, the level of this query's plan is 0. If you execute a function that invokes a select query, level 0 is the plan of the function and level 1 is the plan of the select query invoked by the function. - *userid*: the userid of the user which runs the query. - *dbid*: the database id of the database which the query is running. - *plan*: the query plan of the running query. ## Functions - *pg_show_plans_disable()* disables the feature. Only superuser can execute it. - *pg_show_plans_enable()* enables the feature. Only superuser can execute it. - *pgsp_format_json()* changes the output format to `json`. Note that the format of the plans that are stored in the memory before executing this function cannot be changed. - *pgsp_format_text()* changes the output format to `text`. Note that the format of the plans that are stored in the memory before executing this function cannot be changed. ## Configuration Parameters - *pg_show_plans.plan_format* : It controls the output format of query plans. It can be selected either `text` or `json`. Default is `text`. - *pg_show_plans.max_plan_length* : It sets the maximum length of query plans. Default is `16384` [byte]. Note that this parameter must be set to an integer. Note that pg_show plans allocates approximately (max_plan_length * max_connecions) bytes on the shared memory to store plans, Therefore, if the value of max_plan_length is too large, PostgreSQL may not start due to an out of memory error. - *pg_show_plans.enable* : It controls whether this feature is enabled or not in each user. Default is 'true'. ## Change Log - 22 Mar, 2022: Version 1.0 Released. - 22 Mar, 2022: Improved memory utilization efficiency and obsoleted a parameter:pg_show_plans.enable_txid. - 30 Aug, 2021: Added a parameter:pg_show_plans.enable_txid. - 4 Feb, 2021: Added a parameter:pg_show_plans.enable. - 19 Oct, 2020: Confirmed this can be run on PostgreSQL 13.0. - 10 Apr, 2020: Version 1.0 RC3 Released. Supported Streaming Replication. This extension can be run on the standby server since this version. - 26 Mar, 2020: Version 1.0 RC2 Released. Added pgsp_format_json() and pgsp_format_text(); deleted the parameter `show_level`. - 21 Dec, 2019: Version 1.0 RC Released. Supported versions from 9.1 to 9.4. - 16 Aug, 2019: Version 0.8 Released. Supported the parameter:max_plan_length. - 12 Aug, 2019: Version 0.3 Released. Supported garbage collection. - 9 Aug, 2019: Version 0.2 Released. Supported nested queries. - 8 Aug, 2019: Version 0.1 Released.