mirror of
https://github.com/postgres/postgres.git
synced 2025-04-18 13:44:19 +03:00
dc68515968
This is a continuation of work like 11c34b342bd7, done to reduce the bloat of pg_stat_statements by applying more normalization to query entries. This commit is able to detect and normalize values in VariableSetStmt, resulting in: SET conf_param = $1 Compared to other parse nodes, VariableSetStmt is embedded in much more places in the parser, impacting many query patterns in pg_stat_statements. A custom jumble function is used, with an extra field in the node to decide if arguments should be included in the jumbling or not, a location field being not enough for this purpose. This approach allows for a finer tuning. Clauses relying on one or more keywords are not normalized, for example: * DEFAULT * FROM CURRENT * List of keywords. SET SESSION CHARACTERISTICS AS TRANSACTION, where it is critical to differentiate different sets of options, is a good example of why normalization should not happen. Some queries use VariableSetStmt for some subclauses with SET, that also have their values normalized: - ALTER DATABASE - ALTER ROLE - ALTER SYSTEM - CREATE/ALTER FUNCTION ba90eac7a995 has added test coverage for most of the existing SET patterns. The expected output of these tests shows the difference this commit creates. Normalization could be perhaps applied to more portions of the grammar but what is done here is conservative, and good enough as a starting point. Author: Greg Sabino Mullane, Michael Paquier Discussion: https://postgr.es/m/36e5bffe-e989-194f-85c8-06e7bc88e6f7@amazon.com Discussion: https://postgr.es/m/B44FA29D-EBD0-4DD9-ABC2-16F1CB087074@amazon.com Discussion: https://postgr.es/m/CAKAnmmJtJY2jzQN91=2QAD2eAJAA-Per61eyO48-TyxEg-q0Rg@mail.gmail.com
31 lines
1.5 KiB
Plaintext
31 lines
1.5 KiB
Plaintext
--
|
|
-- Validate WAL generation metrics
|
|
--
|
|
SET pg_stat_statements.track_utility = FALSE;
|
|
CREATE TABLE pgss_wal_tab (a int, b char(20));
|
|
INSERT INTO pgss_wal_tab VALUES(generate_series(1, 10), 'aaa');
|
|
UPDATE pgss_wal_tab SET b = 'bbb' WHERE a > 7;
|
|
DELETE FROM pgss_wal_tab WHERE a > 9;
|
|
DROP TABLE pgss_wal_tab;
|
|
-- Check WAL is generated for the above statements
|
|
SELECT query, calls, rows,
|
|
wal_bytes > 0 as wal_bytes_generated,
|
|
wal_records > 0 as wal_records_generated,
|
|
wal_records >= rows as wal_records_ge_rows
|
|
FROM pg_stat_statements ORDER BY query COLLATE "C";
|
|
query | calls | rows | wal_bytes_generated | wal_records_generated | wal_records_ge_rows
|
|
--------------------------------------------------------------+-------+------+---------------------+-----------------------+---------------------
|
|
DELETE FROM pgss_wal_tab WHERE a > $1 | 1 | 1 | t | t | t
|
|
INSERT INTO pgss_wal_tab VALUES(generate_series($1, $2), $3) | 1 | 10 | t | t | t
|
|
SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 | 1 | f | f | f
|
|
SET pg_stat_statements.track_utility = $1 | 1 | 0 | f | f | t
|
|
UPDATE pgss_wal_tab SET b = $1 WHERE a > $2 | 1 | 3 | t | t | t
|
|
(5 rows)
|
|
|
|
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
|
|
t
|
|
---
|
|
t
|
|
(1 row)
|
|
|