legrandlegrand / pg_stat_sql_plans Goto Github PK
View Code? Open in Web Editor NEWpg_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.
License: Other
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.
License: Other
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.
to prevent an new process created with an already used pid to get its qpid results
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.
This is to prevent extension access contention
as it is not yet available insique querydesc structure (as queryid),
this could be usefull for:
check which structure is the more appropriate :
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
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).
this reduce calculation overhead
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
adapt
pgssp_planner(Query *parse,
const char *query_string,
int cursorOptions, ParamListInfo boundParams)
that has query_text added
Each exemple can be verified with pgssp_normalize_query() function.
minus sign "-" is removed more than expected:
select +1.1, -2, 2-1, b-a, b-1
SELECT +?, ?, ?-?, ba, b?
parenthesis "(" ")" are removed more than expected:
insert into a values (null)
INSERT INTO a VALUES ?
or
select f(1)
SELECT f?
Blank is missing:
select a from t where b=2 order by c
SELECT a FROM t WHERE b=?ORDER BY c
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
variables from replaced literals should be typed:
select 1, 1.1, 'aa'
SELECT ?, ?, ?
(this opens others questions about NULL consideration ...)
following functions are not common litterals, they should not by replaced:
select CURRENT_DATE, CURRENT_TIME, LOCALTIME, LOCALTIMESTAMP
SELECT ?, ?, ?, ?
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.
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
see
https://www.postgresql-archive.org/minimizing-pg-stat-statements-performance-overhead-td6079182.html
this would disable pgssp_backend_queryid(pid) too
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())
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])
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.