From a6f22e83562d8b78293229587cd3d9430d16d466 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Mon, 27 Mar 2017 20:14:36 -0400 Subject: Show ignored constants as "$N" rather than "?" in pg_stat_statements. The trouble with the original choice here is that "?" is a valid (and indeed used) operator name, so that you could end up with ambiguous statement texts like "SELECT ? ? ?". With this patch, you instead see "SELECT $1 ? $2", which seems significantly more readable. The numbers used for this purpose begin after the last actual $N parameter in the particular query. The conflict with external parameters has its own potential for confusion of course, but it was agreed to be an improvement over the previous behavior. Lukas Fittl Discussion: https://postgr.es/m/CAP53PkxeaCuwYmF-A4J5z2-qk5fYFo5_NH3gpXGJJBxv1DMwEw@mail.gmail.com --- doc/src/sgml/pgstatstatements.sgml | 29 +++++++++++++++++++++-------- 1 file changed, 21 insertions(+), 8 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/pgstatstatements.sgml b/doc/src/sgml/pgstatstatements.sgml index d4f09fc2a3a..082994cae00 100644 --- a/doc/src/sgml/pgstatstatements.sgml +++ b/doc/src/sgml/pgstatstatements.sgml @@ -244,11 +244,12 @@ - When a constant's value has been ignored for purposes of matching the - query to other queries, the constant is replaced by ? - in the pg_stat_statements display. The rest of the query - text is that of the first query that had the particular - queryid hash value associated with the + When a constant's value has been ignored for purposes of matching the query + to other queries, the constant is replaced by a parameter symbol, such + as $1, in the pg_stat_statements + display. + The rest of the query text is that of the first query that had the + particular queryid hash value associated with the pg_stat_statements entry. @@ -301,6 +302,18 @@ replicas. If in doubt, direct testing is recommended. + + The parameter symbols used to replace constants in + representative query texts start from the next number after the + highest $n parameter in the original query + text, or $1 if there was none. It's worth noting that in + some cases there may be hidden parameter symbols that affect this + numbering. For example, PL/pgSQL uses hidden parameter + symbols to insert values of function local variables into queries, so that + a PL/pgSQL statement like SELECT i + 1 INTO j + would have representative text like SELECT i + $2. + + The representative query texts are kept in an external disk file, and do not consume shared memory. Therefore, even very lengthy query texts can @@ -481,13 +494,13 @@ bench=# SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5; -[ RECORD 1 ]--------------------------------------------------------------------- -query | UPDATE pgbench_branches SET bbalance = bbalance + ? WHERE bid = ?; +query | UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2; calls | 3000 total_time | 9609.00100000002 rows | 2836 hit_percent | 99.9778970000200936 -[ RECORD 2 ]--------------------------------------------------------------------- -query | UPDATE pgbench_tellers SET tbalance = tbalance + ? WHERE tid = ?; +query | UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2; calls | 3000 total_time | 8015.156 rows | 2990 @@ -499,7 +512,7 @@ total_time | 310.624 rows | 100000 hit_percent | 0.30395136778115501520 -[ RECORD 4 ]--------------------------------------------------------------------- -query | UPDATE pgbench_accounts SET abalance = abalance + ? WHERE aid = ?; +query | UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2; calls | 3000 total_time | 271.741999999997 rows | 3000 -- cgit v1.2.3