lt_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.
When the server starts, lt_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.
lt_show_plans.plan_format
It controls the output format of query plans.
It can be selected either text or
json. Default is text.
lt_show_plans.max_plan_length
It sets the maximum length of query plans. Default is
8192 [byte]. Note that this parameter must be
set to an integer.
lt_show_plans.show_level
It controls the level of query plans. You can select one of all,top and none.
"all" shows all level of the query plan. For example, when you execute a function defined by PL/pgSQL, the caller SQL statement (level 0) and the internal SQL statements in the function (level 1) are shown. "top" shows the top level of the query plan. "none" does not store the query plans, so the lt_show_plans view does not show anything. Default is none.
You must add the line shown below in your lightdb.conf (See lt_show_plans.show_level above).
shared_preload_libraries = 'lt_show_plans' lt_show_plans.show_level = 'top'
After starting your server, you must issue CREATE EXTENSION statement shown below.
testdb=# CREATE EXTENSION lt_show_plans;
After issuing, you must call the enable function shown below.
testdb=# select pg_show_plans_enable();
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)
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.
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.
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_disable() disables the
feature. Only superuser can execute it.
pg_show_plans_enable() enables the
feature. Only superuser can execute it.