legrandlegrand/pg_stat_sql_plans

pg_stat_sql_plans is a PostgreSQL extension created from pg_stat_statements, pg_store_plans and auto_explain adding a planid and many other features.

pg_stat_sql_plans's Issues

do not re-calculate planid for cached plans

with pg_stat_sql_plans

do $$ declare i int;j int; begin for i in 1..1000000 loop select 1 into j; end loop; end; $$;

is slow compared with pg_stat_statements.

This is because an explain command is executed for each query execution even when this query plan is cached. Try to find a way to avoid that.

clean up pgssp_backend_qpid() on process exit

to prevent an new process created with an already used pid to get its qpid results

do not build PlanId for simple INSERT commands (and others)

building a PlanId for
INSERT INTO table values (x,y,z)
is useless.
There is always only one plan for it, then no need to perform an EXPLAIN.

offer a way to exclude queries from being monitored

This is to prevent extension access contention

reset statistics of a specific query (or db or user)

see
https://www.postgresql-archive.org/New-function-pg-stat-statements-reset-query-to-reset-statistics-of-a-specific-query-td6025928i80.html#a6062548

add planning counters

see
https://www.postgresql-archive.org/Planning-counters-in-pg-stat-statements-td5990933.html#a6000836

expose planid for pid

as it is not yet available insique querydesc structure (as queryid),
this could be usefull for:

• identifying current pid's execution plan,
• store planid for a query, to be able to use it in many places (planning, execution, error, ...)

check which structure is the more appropriate :

• between array and hash table,
• between function pgssp_backend_planid(pid) and a view with pid, (level,) queryid, planid
  (see https://github.com/legrandlegrand/pg_show_plans/tree/queryid-and-remanent-plans)

duplicated qpid in logs when pg_stat_sql_plans.explain=true

This appears with
pgbench -c20 -t5 (a kind of query storm ...)
on my MSYS2 (windows 10), but not on linux ...

It also appears in native pg_stat_statements as described here

https://www.postgresql-archive.org/pg-stat-statements-duplicated-external-query-texts-with-MSY2-td6140505.html

In fact, in pgss_store, the query is written in external query text file, before taking the EXCLUSIVE
lock ...

	/* Append new query text to file with only shared lock held */
	stored = qtext_store( query, query_len,
						 &query_offset, &gc_count);

in case of query storm, to prevent this, there should be no action between first hash_search and exclusive lock promotion ...
removing this action (forcing usage of the qtext_store reserved for gc cases under exclusive lock ) seems to help a lot for this issue, but should slow other tests cases (with many distincts queries for exemple).

Ideal solution would be to move this part after hash entry creation, outside exclusive lock,
query length and offset being updated with counters (but this is a big change).

regression tests missing

Use explain (COSTS off) to build PlanId

this reduce calculation overhead

"order by " with different values gives same queryid

select a,b,c from t order by 1
and
select a,b,c from t order by 2

gives same queryid.

note if track_planid is ON, planid are different, giving distinct lines for (queryid,planid) values

add support for PG13

adapt
pgssp_planner(Query *parse,
const char *query_string,
int cursorOptions, ParamListInfo boundParams)

that has query_text added

add support for PG10

normalize_query syntax problems (lisibility)

Each exemple can be verified with pgssp_normalize_query() function.

1. minus sign "-" is removed more than expected:
   select +1.1, -2, 2-1, b-a, b-1
   SELECT +?, ?, ?-?, ba, b?

2. parenthesis "(" ")" are removed more than expected:
   insert into a values (null)
   INSERT INTO a VALUES ?
   or
   select f(1)
   SELECT f?

3. Blank is missing:
   select a from t where b=2 order by c
   SELECT a FROM t WHERE b=?ORDER BY c

4. non quoted identifiers are case sensitive (they should be lowercase):
   select aB,AB,ab,Ab,"xYz","Xyz" from t
   SELECT aB,AB,ab,Ab,"xYz","Xyz" FROM t

5. variables from replaced literals should be typed:
   select 1, 1.1, 'aa'
   SELECT ?, ?, ?
   (this opens others questions about NULL consideration ...)

6. following functions are not common litterals, they should not by replaced:
   select CURRENT_DATE, CURRENT_TIME, LOCALTIME, LOCALTIMESTAMP
   SELECT ?, ?, ?, ?

High (spin) contention under high DB connection counts

follow and fix
https://www.postgresql-archive.org/track-planning-causing-performance-regression-td6143624.html

store search_path for current entry

To be able to retrieve which search_path gave which query (when same objects names are used across many schemas), it is possible to get a pgssp entry per schema by:
track_planid = true;
and
explain used with (verbose on)

note : pg12 explain (SETTINGS on) that adds search_path in explain output should even work better.

Build fails on MSYS2

sqlplus: SET AUTOTRACE ON EXPLAIN equivalent

set pg_stat_sql_plans.track='all';
set pg_stat_sql_plans.track_planid=true;
set pg_stat_sql_plans.explain=true;
set log_min_error_statement=LOG;

postgres=# select id from A;
LOG: queryid: 7449492610579586729 planid: 7866381122914524383 plan:
Seq Scan on a (cost=0.00..35.50 rows=2550 width=4)
id

---

(0 rows)

--> to be added in doc

minimizing overhead when track='none'

see
https://www.postgresql-archive.org/minimizing-pg-stat-statements-performance-overhead-td6079182.html

this would disable pgssp_backend_queryid(pid) too

catch non default GUCs in execution context, impacting execution plan

see
https://www.postgresql-archive.org/explain-plans-with-information-about-modified-gucs-td6062825.html

explain is slow with tables having many columns

https://www.postgresql-archive.org/Explain-is-slow-with-tables-having-many-columns-td6047279.html#a6047280
https://www.postgresql-archive.org/Re-Explain-is-slow-with-tables-having-many-columns-td6047284.html

see how to include a customized explain.c into this extension
to fix the performances problem of colname_is_unique() (called via make_colname_unique())

rows not updated for CREATE TABLE AS SELECT statements

reset previous pgssp_backend_qpid(pid) on parsing error

In one psql session:
SELECT
pgsa.pid,
pgsa.state,
pgsa.query,
pgssp.queryid,
pgssp.planid,
pgssp_normalize_query(pgssp.query)
FROM
pg_stat_activity pgsa
LEFT OUTER JOIN pg_stat_sql_plans pgssp
ON pgssp_backend_qpid( pgsa.pid ) = pgssp.qpid
AND pgsa.datid = pgssp.dbid
AND pgsa.usesysid = pgssp.userid
WHERE pgsa.backend_type='client backend'
and pgsa.pid != pg_backend_pid();
\watch 1

In the second psql session:
select 1;
--> should appear in session one
select a;
ERROR: column "a" does not exist
LINE 1: select a;
--> select a;
appears as query from pg_stat_activity,
but pgssp_backend_qpid(pid) is not updated (see ProcEntryArray[i])