agora inbox for pgsql-hackers@postgresql.orghelp / color / mirror / Atom feed
[PATCH v3 2/3] New test for timeline-tracking of walsender 8+ messages / 2 participants [nested] [flat]
* [PATCH v3 2/3] New test for timeline-tracking of walsender @ 2020-12-09 08:21 Kyotaro Horiguchi <horikyoga.ntt@gmail.com> 0 siblings, 0 replies; 8+ messages in thread From: Kyotaro Horiguchi @ 2020-12-09 08:21 UTC (permalink / raw) Walsender should track timeline changes while sending a historic timeline. Add a test for it. --- src/test/recovery/t/001_stream_rep.pl | 41 ++++++++++++++++++++++++++- 1 file changed, 40 insertions(+), 1 deletion(-) diff --git a/src/test/recovery/t/001_stream_rep.pl b/src/test/recovery/t/001_stream_rep.pl index 9e31a53de7..e78e98fb43 100644 --- a/src/test/recovery/t/001_stream_rep.pl +++ b/src/test/recovery/t/001_stream_rep.pl @@ -2,8 +2,9 @@ use strict; use warnings; use PostgresNode; +use Time::HiRes qw(usleep); use TestLib; -use Test::More tests => 36; +use Test::More tests => 37; # Initialize primary node my $node_primary = get_new_node('primary'); @@ -409,3 +410,41 @@ ok( ($phys_restart_lsn_pre cmp $phys_restart_lsn_post) == 0, my $primary_data = $node_primary->data_dir; ok(!-f "$primary_data/pg_wal/$segment_removed", "WAL segment $segment_removed recycled after physical slot advancing"); + +# +# Check if timeline-increment works while reading a historic timeline. +my $node_primary_2 = get_new_node('primary_2'); +# archiving is needed to create .paritial segment +$node_primary_2->init(allows_streaming => 1, has_archiving => 1); +$node_primary_2->start; +$node_primary_2->backup($backup_name); +my $node_standby_3 = get_new_node('standby_3'); +$node_standby_3->init_from_backup($node_primary_2, $backup_name, + has_streaming => 1); +$node_primary_2->stop; +$node_primary_2->set_standby_mode; # increment primary timeline +$node_primary_2->start; +$node_primary_2->promote; +my $logstart = $node_standby_3->current_log_position(); +$node_standby_3->start; + +my $success = 0; +for (my $i = 0 ; $i < 1000; $i++) +{ + if ($node_standby_3->find_in_log( + "requested WAL segment [0-9A-F]+ has already been removed", + $logstart)) + { + last; + } + elsif ($node_standby_3->find_in_log( + "End of WAL reached on timeline", + $logstart)) + { + $success = 1; + last; + } + usleep(100_000); +} + +ok($success, 'Timeline increment while reading a historic timeline'); -- 2.27.0 ----Next_Part(Tue_Jan__5_17_26_02_2021_224)-- Content-Type: Text/X-Patch; charset=us-ascii Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="v3-0003-Fix-timeline-tracking-failure-while-sending-a-his.patch" ^ permalink raw reply [nested|flat] 8+ messages in thread
* [PATCH v16 4/4] Introduce query_id_const_merge_threshold @ 2023-10-16 14:52 Dmitrii Dolgov <9erthalion6@gmail.com> 0 siblings, 0 replies; 8+ messages in thread From: Dmitrii Dolgov @ 2023-10-16 14:52 UTC (permalink / raw) Replace query_id_const_merge with a threshold to allow merging only if the number of elements is larger than specified value, which could be configured using pg_stat_statements parameter query_id_const_merge_threshold. --- .../pg_stat_statements/expected/merging.out | 68 ++++++++++++++++++- .../pg_stat_statements/pg_stat_statements.c | 36 +++++----- contrib/pg_stat_statements/sql/merging.sql | 21 +++++- doc/src/sgml/pgstatstatements.sgml | 19 +++--- src/backend/nodes/queryjumblefuncs.c | 23 +++++-- src/backend/postmaster/postmaster.c | 6 +- src/include/nodes/queryjumble.h | 4 +- 7 files changed, 135 insertions(+), 42 deletions(-) diff --git a/contrib/pg_stat_statements/expected/merging.out b/contrib/pg_stat_statements/expected/merging.out index 7400870f3f6..93d59149bf0 100644 --- a/contrib/pg_stat_statements/expected/merging.out +++ b/contrib/pg_stat_statements/expected/merging.out @@ -36,7 +36,7 @@ SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; (4 rows) -- Normal scenario, too many simple constants for an IN query -SET pg_stat_statements.query_id_const_merge = on; +SET pg_stat_statements.query_id_const_merge_threshold = 1; SELECT pg_stat_statements_reset(); pg_stat_statements_reset -------------------------- @@ -218,4 +218,68 @@ FROM cte; -------- (0 rows) -RESET pg_stat_statements.query_id_const_merge; +-- With the threshold +SET pg_stat_statements.query_id_const_merge_threshold = 10; +SELECT pg_stat_statements_reset(); + pg_stat_statements_reset +-------------------------- + +(1 row) + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9); + id | data +----+------ +(0 rows) + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10); + id | data +----+------ +(0 rows) + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11); + id | data +----+------ +(0 rows) + +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + query | calls +---------------------------------------------------------------------------+------- + SELECT * FROM test_merge WHERE id IN ($1, $2, $3, $4, $5, $6, $7, $8, $9) | 1 + SELECT * FROM test_merge WHERE id IN (... [10-99 entries]) | 2 + SELECT pg_stat_statements_reset() | 1 +(3 rows) + +-- With gaps on the threshold +SELECT pg_stat_statements_reset(); + pg_stat_statements_reset +-------------------------- + +(1 row) + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4); + id | data +----+------ +(0 rows) + +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + query | calls +-------------------------------------------------------+------- + SELECT * FROM test_merge WHERE id IN ($1, $2, $3, $4) | 1 + SELECT pg_stat_statements_reset() | 1 +(2 rows) + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15); + id | data +----+------ +(0 rows) + +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + query | calls +------------------------------------------------------------------------+------- + SELECT * FROM test_merge WHERE id IN ($1, $2, $3, $4) | 1 + SELECT * FROM test_merge WHERE id IN (... [10-99 entries]) | 1 + SELECT pg_stat_statements_reset() | 1 + SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C" | 1 +(4 rows) + +RESET pg_stat_statements.query_id_const_merge_threshold; diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c index a5702c3d749..e02171c6767 100644 --- a/contrib/pg_stat_statements/pg_stat_statements.c +++ b/contrib/pg_stat_statements/pg_stat_statements.c @@ -266,8 +266,8 @@ static ExecutorFinish_hook_type prev_ExecutorFinish = NULL; static ExecutorEnd_hook_type prev_ExecutorEnd = NULL; static ProcessUtility_hook_type prev_ProcessUtility = NULL; -/* An assign hook to keep query_id_const_merge in sync */ -static void pgss_query_id_const_merge_assign_hook(bool newvalue, void *extra); +/* An assign hook to keep query_id_const_merge_threshold in sync */ +static void pgss_query_id_const_merge_assign_hook(int newvalue, void *extra); /* Links to shared memory state */ static pgssSharedState *pgss = NULL; @@ -296,8 +296,8 @@ static bool pgss_track_utility = true; /* whether to track utility commands */ static bool pgss_track_planning = false; /* whether to track planning * duration */ static bool pgss_save = true; /* whether to save stats across shutdown */ -static bool pgss_query_id_const_merge = false; /* request constants merging - * when computing query_id */ +static int pgss_query_id_const_merge_threshold = 0; /* request constants merging + * when computing query_id */ #define pgss_enabled(level) \ (!IsParallelWorker() && \ @@ -459,20 +459,22 @@ _PG_init(void) NULL, NULL); - DefineCustomBoolVariable("pg_stat_statements.query_id_const_merge", - "Whether to merge constants in a list when computing query_id.", - NULL, - &pgss_query_id_const_merge, - false, - PGC_SUSET, - 0, - NULL, - pgss_query_id_const_merge_assign_hook, - NULL); + DefineCustomIntVariable("pg_stat_statements.query_id_const_merge_threshold", + "Whether to merge constants in a list when computing query_id.", + NULL, + &pgss_query_id_const_merge_threshold, + 0, + 0, + INT_MAX, + PGC_SUSET, + 0, + NULL, + pgss_query_id_const_merge_assign_hook, + NULL); MarkGUCPrefixReserved("pg_stat_statements"); - SetQueryIdConstMerge(pgss_query_id_const_merge); + SetQueryIdConstMerge(pgss_query_id_const_merge_threshold); /* * Install hooks. @@ -2956,10 +2958,10 @@ comp_location(const void *a, const void *b) } /* - * Notify query jumbling about query_id_const_merge status + * Notify query jumbling about query_id_const_merge_threshold status */ static void -pgss_query_id_const_merge_assign_hook(bool newvalue, void *extra) +pgss_query_id_const_merge_assign_hook(int newvalue, void *extra) { SetQueryIdConstMerge(newvalue); } diff --git a/contrib/pg_stat_statements/sql/merging.sql b/contrib/pg_stat_statements/sql/merging.sql index c515e48d50c..52ee4fcb216 100644 --- a/contrib/pg_stat_statements/sql/merging.sql +++ b/contrib/pg_stat_statements/sql/merging.sql @@ -15,7 +15,7 @@ SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11); SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; -- Normal scenario, too many simple constants for an IN query -SET pg_stat_statements.query_id_const_merge = on; +SET pg_stat_statements.query_id_const_merge_threshold = 1; SELECT pg_stat_statements_reset(); SELECT * FROM test_merge WHERE id IN (1); @@ -68,4 +68,21 @@ WITH cte AS ( SELECT ARRAY['a', 'b', 'c', const::varchar] AS result FROM cte; -RESET pg_stat_statements.query_id_const_merge; +-- With the threshold +SET pg_stat_statements.query_id_const_merge_threshold = 10; + +SELECT pg_stat_statements_reset(); +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9); +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10); +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11); +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + +-- With gaps on the threshold +SELECT pg_stat_statements_reset(); +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4); +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15); +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + +RESET pg_stat_statements.query_id_const_merge_threshold; diff --git a/doc/src/sgml/pgstatstatements.sgml b/doc/src/sgml/pgstatstatements.sgml index a919696abc2..81e6ab2c0c8 100644 --- a/doc/src/sgml/pgstatstatements.sgml +++ b/doc/src/sgml/pgstatstatements.sgml @@ -549,11 +549,11 @@ In some cases, queries with visibly different texts might get merged into a single <structname>pg_stat_statements</structname> entry. Normally this will happen only for semantically equivalent queries, or if - <xref linkend="guc-query-id-const-merge"/> is enabled and the only + <xref linkend="guc-query-id-const-merge-threshold"/> is greater than 0 and the only difference between queries is the length of an array with constants they contain: <screen> -=# SET query_id_const_merge = on; +=# SET query_id_const_merge_threshold = 1; =# SELECT pg_stat_statements_reset(); =# SELECT * FROM test WHERE a IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11); =# SELECT * FROM test WHERE a IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12); @@ -881,9 +881,9 @@ calls | 1 <varlistentry> <term> - <varname>pg_stat_statements.query_id_const_merge</varname> (<type>bool</type>) + <varname>pg_stat_statements.query_id_const_merge_threshold</varname> (<type>integer</type>) <indexterm> - <primary><varname>pg_stat_statements.query_id_const_merge</varname> configuration parameter</primary> + <primary><varname>pg_stat_statements.query_id_const_merge_threshold</varname> configuration parameter</primary> </indexterm> </term> @@ -895,11 +895,12 @@ calls | 1 query will get multiple different identifiers, one for each occurrence with an array of different lenght. - If this parameter is on, an array of constants will contribute only the - first element, the last element and the number of elements to the query - identifier. It means two occurences of the same query, where the only - difference is number of constants in the array, are going to get the - same query identifier if the arrays are of similar length. + If this parameter is greater than 0, an array with more than + <varname>pg_stat_statements.query_id_const_merge_threshold</varname> + constants will contribute only the first element, the last element + and the number of elements to the query identifier. It means two + occurences of the same query, where the only difference is number of + constants in the array, are going to get the same query identifier. Such queries are represented in form <literal>'(... [10-99 entries])'</literal>. The parameter could be used to reduce amount of repeating data stored diff --git a/src/backend/nodes/queryjumblefuncs.c b/src/backend/nodes/queryjumblefuncs.c index a1d4567ca66..10be62f1331 100644 --- a/src/backend/nodes/queryjumblefuncs.c +++ b/src/backend/nodes/queryjumblefuncs.c @@ -44,8 +44,8 @@ /* GUC parameters */ int compute_query_id = COMPUTE_QUERY_ID_AUTO; -/* Whether to merge constants in a list when computing query_id */ -bool query_id_const_merge = false; +/* Lower threshold for the list length to merge constants when computing query_id */ +int query_id_const_merge_threshold = 1; /* True when compute_query_id is ON, or AUTO and a module requests them */ bool query_id_enabled = false; @@ -159,12 +159,14 @@ EnableQueryId(void) * Controls constants merging for query identifier computation. * * Third-party plugins can use this function to enable/disable merging - * of constants in a list when query identifier is computed. + * of constants in a list when query identifier is computed. The argument + * specifies the lower threshold for an array length, above which merging will + * be applied. */ void -SetQueryIdConstMerge(bool value) +SetQueryIdConstMerge(int threshold) { - query_id_const_merge = value; + query_id_const_merge_threshold = threshold; } /* @@ -240,7 +242,8 @@ RecordConstLocation(JumbleState *jstate, int location, int magnitude) /* * Verify if the provided list contains could be merged down, which means it - * contains only constant expressions. + * contains only constant expressions and the list contains more than + * query_id_const_merge_threshold elements. * * Return value is the order of magnitude (i.e. how many digits it has) for * length of the list (to use for representation purposes later on) if merging @@ -258,12 +261,18 @@ IsMergeableConstList(List *elements, Const **firstConst, Const **lastConst) if (elements == NULL) return 0; - if (!query_id_const_merge) + if (query_id_const_merge_threshold < 1) { /* Merging is disabled, process everything one by one */ return 0; } + if (elements->length < query_id_const_merge_threshold) + { + /* The list is not large enough */ + return 0; + } + firstExpr = linitial(elements); /* diff --git a/src/backend/postmaster/postmaster.c b/src/backend/postmaster/postmaster.c index 3e5c43ede81..3094d54bab8 100644 --- a/src/backend/postmaster/postmaster.c +++ b/src/backend/postmaster/postmaster.c @@ -528,7 +528,7 @@ typedef struct bool redirection_done; bool IsBinaryUpgrade; bool query_id_enabled; - bool query_id_const_merge; + int query_id_const_merge_threshold; int max_safe_fds; int MaxBackends; #ifdef WIN32 @@ -6076,7 +6076,7 @@ save_backend_variables(BackendParameters *param, Port *port, param->redirection_done = redirection_done; param->IsBinaryUpgrade = IsBinaryUpgrade; param->query_id_enabled = query_id_enabled; - param->query_id_const_merge = query_id_const_merge; + param->query_id_const_merge_threshold = query_id_const_merge_threshold; param->max_safe_fds = max_safe_fds; param->MaxBackends = MaxBackends; @@ -6308,7 +6308,7 @@ restore_backend_variables(BackendParameters *param, Port *port) redirection_done = param->redirection_done; IsBinaryUpgrade = param->IsBinaryUpgrade; query_id_enabled = param->query_id_enabled; - query_id_const_merge = param->query_id_const_merge; + query_id_const_merge_threshold = param->query_id_const_merge_threshold; max_safe_fds = param->max_safe_fds; MaxBackends = param->MaxBackends; diff --git a/src/include/nodes/queryjumble.h b/src/include/nodes/queryjumble.h index 8ee2e9afbb6..a9f8cfcbed9 100644 --- a/src/include/nodes/queryjumble.h +++ b/src/include/nodes/queryjumble.h @@ -74,10 +74,10 @@ extern PGDLLIMPORT int compute_query_id; extern const char *CleanQuerytext(const char *query, int *location, int *len); extern JumbleState *JumbleQuery(Query *query); extern void EnableQueryId(void); -extern void SetQueryIdConstMerge(bool value); +extern void SetQueryIdConstMerge(int threshold); extern PGDLLIMPORT bool query_id_enabled; -extern PGDLLIMPORT bool query_id_const_merge; +extern PGDLLIMPORT int query_id_const_merge_threshold; /* * Returns whether query identifier computation has been enabled, either -- 2.41.0 --pjkjqtbeiiimipyr-- ^ permalink raw reply [nested|flat] 8+ messages in thread
* [PATCH v17 4/4] Introduce query_id_const_merge_threshold @ 2024-01-13 13:52 Dmitrii Dolgov <9erthalion6@gmail.com> 0 siblings, 0 replies; 8+ messages in thread From: Dmitrii Dolgov @ 2024-01-13 13:52 UTC (permalink / raw) Replace query_id_const_merge with a threshold to allow merging only if the number of elements is larger than specified value, which could be configured using pg_stat_statements parameter query_id_const_merge_threshold. --- .../pg_stat_statements/expected/merging.out | 68 ++++++++++++++++++- .../pg_stat_statements/pg_stat_statements.c | 36 +++++----- contrib/pg_stat_statements/sql/merging.sql | 21 +++++- doc/src/sgml/pgstatstatements.sgml | 23 ++++--- src/backend/nodes/queryjumblefuncs.c | 23 +++++-- src/backend/postmaster/postmaster.c | 6 +- src/include/nodes/queryjumble.h | 4 +- 7 files changed, 137 insertions(+), 44 deletions(-) diff --git a/contrib/pg_stat_statements/expected/merging.out b/contrib/pg_stat_statements/expected/merging.out index 7400870f3f6..93d59149bf0 100644 --- a/contrib/pg_stat_statements/expected/merging.out +++ b/contrib/pg_stat_statements/expected/merging.out @@ -36,7 +36,7 @@ SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; (4 rows) -- Normal scenario, too many simple constants for an IN query -SET pg_stat_statements.query_id_const_merge = on; +SET pg_stat_statements.query_id_const_merge_threshold = 1; SELECT pg_stat_statements_reset(); pg_stat_statements_reset -------------------------- @@ -218,4 +218,68 @@ FROM cte; -------- (0 rows) -RESET pg_stat_statements.query_id_const_merge; +-- With the threshold +SET pg_stat_statements.query_id_const_merge_threshold = 10; +SELECT pg_stat_statements_reset(); + pg_stat_statements_reset +-------------------------- + +(1 row) + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9); + id | data +----+------ +(0 rows) + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10); + id | data +----+------ +(0 rows) + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11); + id | data +----+------ +(0 rows) + +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + query | calls +---------------------------------------------------------------------------+------- + SELECT * FROM test_merge WHERE id IN ($1, $2, $3, $4, $5, $6, $7, $8, $9) | 1 + SELECT * FROM test_merge WHERE id IN (... [10-99 entries]) | 2 + SELECT pg_stat_statements_reset() | 1 +(3 rows) + +-- With gaps on the threshold +SELECT pg_stat_statements_reset(); + pg_stat_statements_reset +-------------------------- + +(1 row) + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4); + id | data +----+------ +(0 rows) + +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + query | calls +-------------------------------------------------------+------- + SELECT * FROM test_merge WHERE id IN ($1, $2, $3, $4) | 1 + SELECT pg_stat_statements_reset() | 1 +(2 rows) + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15); + id | data +----+------ +(0 rows) + +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + query | calls +------------------------------------------------------------------------+------- + SELECT * FROM test_merge WHERE id IN ($1, $2, $3, $4) | 1 + SELECT * FROM test_merge WHERE id IN (... [10-99 entries]) | 1 + SELECT pg_stat_statements_reset() | 1 + SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C" | 1 +(4 rows) + +RESET pg_stat_statements.query_id_const_merge_threshold; diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c index a5702c3d749..e02171c6767 100644 --- a/contrib/pg_stat_statements/pg_stat_statements.c +++ b/contrib/pg_stat_statements/pg_stat_statements.c @@ -266,8 +266,8 @@ static ExecutorFinish_hook_type prev_ExecutorFinish = NULL; static ExecutorEnd_hook_type prev_ExecutorEnd = NULL; static ProcessUtility_hook_type prev_ProcessUtility = NULL; -/* An assign hook to keep query_id_const_merge in sync */ -static void pgss_query_id_const_merge_assign_hook(bool newvalue, void *extra); +/* An assign hook to keep query_id_const_merge_threshold in sync */ +static void pgss_query_id_const_merge_assign_hook(int newvalue, void *extra); /* Links to shared memory state */ static pgssSharedState *pgss = NULL; @@ -296,8 +296,8 @@ static bool pgss_track_utility = true; /* whether to track utility commands */ static bool pgss_track_planning = false; /* whether to track planning * duration */ static bool pgss_save = true; /* whether to save stats across shutdown */ -static bool pgss_query_id_const_merge = false; /* request constants merging - * when computing query_id */ +static int pgss_query_id_const_merge_threshold = 0; /* request constants merging + * when computing query_id */ #define pgss_enabled(level) \ (!IsParallelWorker() && \ @@ -459,20 +459,22 @@ _PG_init(void) NULL, NULL); - DefineCustomBoolVariable("pg_stat_statements.query_id_const_merge", - "Whether to merge constants in a list when computing query_id.", - NULL, - &pgss_query_id_const_merge, - false, - PGC_SUSET, - 0, - NULL, - pgss_query_id_const_merge_assign_hook, - NULL); + DefineCustomIntVariable("pg_stat_statements.query_id_const_merge_threshold", + "Whether to merge constants in a list when computing query_id.", + NULL, + &pgss_query_id_const_merge_threshold, + 0, + 0, + INT_MAX, + PGC_SUSET, + 0, + NULL, + pgss_query_id_const_merge_assign_hook, + NULL); MarkGUCPrefixReserved("pg_stat_statements"); - SetQueryIdConstMerge(pgss_query_id_const_merge); + SetQueryIdConstMerge(pgss_query_id_const_merge_threshold); /* * Install hooks. @@ -2956,10 +2958,10 @@ comp_location(const void *a, const void *b) } /* - * Notify query jumbling about query_id_const_merge status + * Notify query jumbling about query_id_const_merge_threshold status */ static void -pgss_query_id_const_merge_assign_hook(bool newvalue, void *extra) +pgss_query_id_const_merge_assign_hook(int newvalue, void *extra) { SetQueryIdConstMerge(newvalue); } diff --git a/contrib/pg_stat_statements/sql/merging.sql b/contrib/pg_stat_statements/sql/merging.sql index c515e48d50c..52ee4fcb216 100644 --- a/contrib/pg_stat_statements/sql/merging.sql +++ b/contrib/pg_stat_statements/sql/merging.sql @@ -15,7 +15,7 @@ SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11); SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; -- Normal scenario, too many simple constants for an IN query -SET pg_stat_statements.query_id_const_merge = on; +SET pg_stat_statements.query_id_const_merge_threshold = 1; SELECT pg_stat_statements_reset(); SELECT * FROM test_merge WHERE id IN (1); @@ -68,4 +68,21 @@ WITH cte AS ( SELECT ARRAY['a', 'b', 'c', const::varchar] AS result FROM cte; -RESET pg_stat_statements.query_id_const_merge; +-- With the threshold +SET pg_stat_statements.query_id_const_merge_threshold = 10; + +SELECT pg_stat_statements_reset(); +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9); +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10); +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11); +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + +-- With gaps on the threshold +SELECT pg_stat_statements_reset(); +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4); +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15); +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + +RESET pg_stat_statements.query_id_const_merge_threshold; diff --git a/doc/src/sgml/pgstatstatements.sgml b/doc/src/sgml/pgstatstatements.sgml index ff24153c493..5545accc560 100644 --- a/doc/src/sgml/pgstatstatements.sgml +++ b/doc/src/sgml/pgstatstatements.sgml @@ -549,12 +549,12 @@ In some cases, queries with visibly different texts might get merged into a single <structname>pg_stat_statements</structname> entry. Normally this will happen only for semantically equivalent queries, or if - <varname>pg_stat_statements.query_id_const_merge</varname> is enabled and - the only difference between queries is the length of an array with constants - they contain: + <varname>pg_stat_statements.query_id_const_merge_threshold</varname> is + enabled and the only difference between queries is the length of an array + with constants they contain: <screen> -=# SET query_id_const_merge = on; +=# SET query_id_const_merge_threshold = 1; =# SELECT pg_stat_statements_reset(); =# SELECT * FROM test WHERE a IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11); =# SELECT * FROM test WHERE a IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12); @@ -882,9 +882,9 @@ calls | 1 <varlistentry> <term> - <varname>pg_stat_statements.query_id_const_merge</varname> (<type>bool</type>) + <varname>pg_stat_statements.query_id_const_merge_threshold</varname> (<type>integer</type>) <indexterm> - <primary><varname>pg_stat_statements.query_id_const_merge</varname> configuration parameter</primary> + <primary><varname>pg_stat_statements.query_id_const_merge_threshold</varname> configuration parameter</primary> </indexterm> </term> @@ -896,11 +896,12 @@ calls | 1 query will get multiple different identifiers, one for each occurrence with an array of different lenght. - If this parameter is on, an array of constants will contribute only the - first element, the last element and the number of elements to the query - identifier. It means two occurences of the same query, where the only - difference is number of constants in the array, are going to get the - same query identifier if the arrays are of similar length. + If this parameter is greater than 0, an array with more than + <varname>pg_stat_statements.query_id_const_merge_threshold</varname> + constants will contribute only the first element, the last element + and the number of elements to the query identifier. It means two + occurences of the same query, where the only difference is number of + constants in the array, are going to get the same query identifier. Such queries are represented in form <literal>'(... [10-99 entries])'</literal>. The parameter could be used to reduce amount of repeating data stored diff --git a/src/backend/nodes/queryjumblefuncs.c b/src/backend/nodes/queryjumblefuncs.c index a1d4567ca66..10be62f1331 100644 --- a/src/backend/nodes/queryjumblefuncs.c +++ b/src/backend/nodes/queryjumblefuncs.c @@ -44,8 +44,8 @@ /* GUC parameters */ int compute_query_id = COMPUTE_QUERY_ID_AUTO; -/* Whether to merge constants in a list when computing query_id */ -bool query_id_const_merge = false; +/* Lower threshold for the list length to merge constants when computing query_id */ +int query_id_const_merge_threshold = 1; /* True when compute_query_id is ON, or AUTO and a module requests them */ bool query_id_enabled = false; @@ -159,12 +159,14 @@ EnableQueryId(void) * Controls constants merging for query identifier computation. * * Third-party plugins can use this function to enable/disable merging - * of constants in a list when query identifier is computed. + * of constants in a list when query identifier is computed. The argument + * specifies the lower threshold for an array length, above which merging will + * be applied. */ void -SetQueryIdConstMerge(bool value) +SetQueryIdConstMerge(int threshold) { - query_id_const_merge = value; + query_id_const_merge_threshold = threshold; } /* @@ -240,7 +242,8 @@ RecordConstLocation(JumbleState *jstate, int location, int magnitude) /* * Verify if the provided list contains could be merged down, which means it - * contains only constant expressions. + * contains only constant expressions and the list contains more than + * query_id_const_merge_threshold elements. * * Return value is the order of magnitude (i.e. how many digits it has) for * length of the list (to use for representation purposes later on) if merging @@ -258,12 +261,18 @@ IsMergeableConstList(List *elements, Const **firstConst, Const **lastConst) if (elements == NULL) return 0; - if (!query_id_const_merge) + if (query_id_const_merge_threshold < 1) { /* Merging is disabled, process everything one by one */ return 0; } + if (elements->length < query_id_const_merge_threshold) + { + /* The list is not large enough */ + return 0; + } + firstExpr = linitial(elements); /* diff --git a/src/backend/postmaster/postmaster.c b/src/backend/postmaster/postmaster.c index 3e5c43ede81..3094d54bab8 100644 --- a/src/backend/postmaster/postmaster.c +++ b/src/backend/postmaster/postmaster.c @@ -528,7 +528,7 @@ typedef struct bool redirection_done; bool IsBinaryUpgrade; bool query_id_enabled; - bool query_id_const_merge; + int query_id_const_merge_threshold; int max_safe_fds; int MaxBackends; #ifdef WIN32 @@ -6076,7 +6076,7 @@ save_backend_variables(BackendParameters *param, Port *port, param->redirection_done = redirection_done; param->IsBinaryUpgrade = IsBinaryUpgrade; param->query_id_enabled = query_id_enabled; - param->query_id_const_merge = query_id_const_merge; + param->query_id_const_merge_threshold = query_id_const_merge_threshold; param->max_safe_fds = max_safe_fds; param->MaxBackends = MaxBackends; @@ -6308,7 +6308,7 @@ restore_backend_variables(BackendParameters *param, Port *port) redirection_done = param->redirection_done; IsBinaryUpgrade = param->IsBinaryUpgrade; query_id_enabled = param->query_id_enabled; - query_id_const_merge = param->query_id_const_merge; + query_id_const_merge_threshold = param->query_id_const_merge_threshold; max_safe_fds = param->max_safe_fds; MaxBackends = param->MaxBackends; diff --git a/src/include/nodes/queryjumble.h b/src/include/nodes/queryjumble.h index 8ee2e9afbb6..a9f8cfcbed9 100644 --- a/src/include/nodes/queryjumble.h +++ b/src/include/nodes/queryjumble.h @@ -74,10 +74,10 @@ extern PGDLLIMPORT int compute_query_id; extern const char *CleanQuerytext(const char *query, int *location, int *len); extern JumbleState *JumbleQuery(Query *query); extern void EnableQueryId(void); -extern void SetQueryIdConstMerge(bool value); +extern void SetQueryIdConstMerge(int threshold); extern PGDLLIMPORT bool query_id_enabled; -extern PGDLLIMPORT bool query_id_const_merge; +extern PGDLLIMPORT int query_id_const_merge_threshold; /* * Returns whether query identifier computation has been enabled, either -- 2.41.0 --si27d3bgnnpsswlv-- ^ permalink raw reply [nested|flat] 8+ messages in thread
* [PATCH v18 4/4] Introduce query_id_const_merge_threshold @ 2024-01-22 20:31 Dmitrii Dolgov <9erthalion6@gmail.com> 0 siblings, 0 replies; 8+ messages in thread From: Dmitrii Dolgov @ 2024-01-22 20:31 UTC (permalink / raw) Replace query_id_const_merge with a threshold to allow merging only if the number of elements is larger than specified value, which could be configured using pg_stat_statements parameter query_id_const_merge_threshold. --- .../pg_stat_statements/expected/merging.out | 68 ++++++++++++++++++- .../pg_stat_statements/pg_stat_statements.c | 36 +++++----- contrib/pg_stat_statements/sql/merging.sql | 21 +++++- doc/src/sgml/pgstatstatements.sgml | 23 ++++--- src/backend/nodes/queryjumblefuncs.c | 23 +++++-- src/backend/postmaster/postmaster.c | 6 +- src/include/nodes/queryjumble.h | 4 +- 7 files changed, 137 insertions(+), 44 deletions(-) diff --git a/contrib/pg_stat_statements/expected/merging.out b/contrib/pg_stat_statements/expected/merging.out index 0cb4f67b8b7..552e248ff14 100644 --- a/contrib/pg_stat_statements/expected/merging.out +++ b/contrib/pg_stat_statements/expected/merging.out @@ -36,7 +36,7 @@ SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; (4 rows) -- Normal scenario, too many simple constants for an IN query -SET pg_stat_statements.query_id_const_merge = on; +SET pg_stat_statements.query_id_const_merge_threshold = 1; SELECT pg_stat_statements_reset() IS NOT NULL AS t; t --- @@ -218,4 +218,68 @@ FROM cte; -------- (0 rows) -RESET pg_stat_statements.query_id_const_merge; +-- With the threshold +SET pg_stat_statements.query_id_const_merge_threshold = 10; +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9); + id | data +----+------ +(0 rows) + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10); + id | data +----+------ +(0 rows) + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11); + id | data +----+------ +(0 rows) + +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + query | calls +---------------------------------------------------------------------------+------- + SELECT * FROM test_merge WHERE id IN ($1, $2, $3, $4, $5, $6, $7, $8, $9) | 1 + SELECT * FROM test_merge WHERE id IN (... [10-99 entries]) | 2 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 +(3 rows) + +-- With gaps on the threshold +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4); + id | data +----+------ +(0 rows) + +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + query | calls +-------------------------------------------------------+------- + SELECT * FROM test_merge WHERE id IN ($1, $2, $3, $4) | 1 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 +(2 rows) + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15); + id | data +----+------ +(0 rows) + +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + query | calls +------------------------------------------------------------------------+------- + SELECT * FROM test_merge WHERE id IN ($1, $2, $3, $4) | 1 + SELECT * FROM test_merge WHERE id IN (... [10-99 entries]) | 1 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 + SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C" | 1 +(4 rows) + +RESET pg_stat_statements.query_id_const_merge_threshold; diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c index bb8c1894e05..6b438aeeeb9 100644 --- a/contrib/pg_stat_statements/pg_stat_statements.c +++ b/contrib/pg_stat_statements/pg_stat_statements.c @@ -264,8 +264,8 @@ static ExecutorFinish_hook_type prev_ExecutorFinish = NULL; static ExecutorEnd_hook_type prev_ExecutorEnd = NULL; static ProcessUtility_hook_type prev_ProcessUtility = NULL; -/* An assign hook to keep query_id_const_merge in sync */ -static void pgss_query_id_const_merge_assign_hook(bool newvalue, void *extra); +/* An assign hook to keep query_id_const_merge_threshold in sync */ +static void pgss_query_id_const_merge_assign_hook(int newvalue, void *extra); /* Links to shared memory state */ static pgssSharedState *pgss = NULL; @@ -294,8 +294,8 @@ static bool pgss_track_utility = true; /* whether to track utility commands */ static bool pgss_track_planning = false; /* whether to track planning * duration */ static bool pgss_save = true; /* whether to save stats across shutdown */ -static bool pgss_query_id_const_merge = false; /* request constants merging - * when computing query_id */ +static int pgss_query_id_const_merge_threshold = 0; /* request constants merging + * when computing query_id */ #define pgss_enabled(level) \ (!IsParallelWorker() && \ @@ -458,20 +458,22 @@ _PG_init(void) NULL, NULL); - DefineCustomBoolVariable("pg_stat_statements.query_id_const_merge", - "Whether to merge constants in a list when computing query_id.", - NULL, - &pgss_query_id_const_merge, - false, - PGC_SUSET, - 0, - NULL, - pgss_query_id_const_merge_assign_hook, - NULL); + DefineCustomIntVariable("pg_stat_statements.query_id_const_merge_threshold", + "Whether to merge constants in a list when computing query_id.", + NULL, + &pgss_query_id_const_merge_threshold, + 0, + 0, + INT_MAX, + PGC_SUSET, + 0, + NULL, + pgss_query_id_const_merge_assign_hook, + NULL); MarkGUCPrefixReserved("pg_stat_statements"); - SetQueryIdConstMerge(pgss_query_id_const_merge); + SetQueryIdConstMerge(pgss_query_id_const_merge_threshold); /* * Install hooks. @@ -3068,10 +3070,10 @@ comp_location(const void *a, const void *b) } /* - * Notify query jumbling about query_id_const_merge status + * Notify query jumbling about query_id_const_merge_threshold status */ static void -pgss_query_id_const_merge_assign_hook(bool newvalue, void *extra) +pgss_query_id_const_merge_assign_hook(int newvalue, void *extra) { SetQueryIdConstMerge(newvalue); } diff --git a/contrib/pg_stat_statements/sql/merging.sql b/contrib/pg_stat_statements/sql/merging.sql index 657044faded..fedeb35b8f5 100644 --- a/contrib/pg_stat_statements/sql/merging.sql +++ b/contrib/pg_stat_statements/sql/merging.sql @@ -15,7 +15,7 @@ SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11); SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; -- Normal scenario, too many simple constants for an IN query -SET pg_stat_statements.query_id_const_merge = on; +SET pg_stat_statements.query_id_const_merge_threshold = 1; SELECT pg_stat_statements_reset() IS NOT NULL AS t; SELECT * FROM test_merge WHERE id IN (1); @@ -68,4 +68,21 @@ WITH cte AS ( SELECT ARRAY['a', 'b', 'c', const::varchar] AS result FROM cte; -RESET pg_stat_statements.query_id_const_merge; +-- With the threshold +SET pg_stat_statements.query_id_const_merge_threshold = 10; + +SELECT pg_stat_statements_reset() IS NOT NULL AS t; +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9); +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10); +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11); +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + +-- With gaps on the threshold +SELECT pg_stat_statements_reset() IS NOT NULL AS t; +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4); +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15); +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + +RESET pg_stat_statements.query_id_const_merge_threshold; diff --git a/doc/src/sgml/pgstatstatements.sgml b/doc/src/sgml/pgstatstatements.sgml index bac029430be..3a060935bff 100644 --- a/doc/src/sgml/pgstatstatements.sgml +++ b/doc/src/sgml/pgstatstatements.sgml @@ -605,12 +605,12 @@ In some cases, queries with visibly different texts might get merged into a single <structname>pg_stat_statements</structname> entry. Normally this will happen only for semantically equivalent queries, or if - <varname>pg_stat_statements.query_id_const_merge</varname> is enabled and - the only difference between queries is the length of an array with constants - they contain: + <varname>pg_stat_statements.query_id_const_merge_threshold</varname> is + enabled and the only difference between queries is the length of an array + with constants they contain: <screen> -=# SET query_id_const_merge = on; +=# SET query_id_const_merge_threshold = 1; =# SELECT pg_stat_statements_reset(); =# SELECT * FROM test WHERE a IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11); =# SELECT * FROM test WHERE a IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12); @@ -953,9 +953,9 @@ calls | 1 <varlistentry> <term> - <varname>pg_stat_statements.query_id_const_merge</varname> (<type>bool</type>) + <varname>pg_stat_statements.query_id_const_merge_threshold</varname> (<type>integer</type>) <indexterm> - <primary><varname>pg_stat_statements.query_id_const_merge</varname> configuration parameter</primary> + <primary><varname>pg_stat_statements.query_id_const_merge_threshold</varname> configuration parameter</primary> </indexterm> </term> @@ -967,11 +967,12 @@ calls | 1 query will get multiple different identifiers, one for each occurrence with an array of different lenght. - If this parameter is on, an array of constants will contribute only the - first element, the last element and the number of elements to the query - identifier. It means two occurences of the same query, where the only - difference is number of constants in the array, are going to get the - same query identifier if the arrays are of similar length. + If this parameter is greater than 0, an array with more than + <varname>pg_stat_statements.query_id_const_merge_threshold</varname> + constants will contribute only the first element, the last element + and the number of elements to the query identifier. It means two + occurences of the same query, where the only difference is number of + constants in the array, are going to get the same query identifier. Such queries are represented in form <literal>'(... [10-99 entries])'</literal>. The parameter could be used to reduce amount of repeating data stored diff --git a/src/backend/nodes/queryjumblefuncs.c b/src/backend/nodes/queryjumblefuncs.c index 7db7ae7f2ee..1884e91de49 100644 --- a/src/backend/nodes/queryjumblefuncs.c +++ b/src/backend/nodes/queryjumblefuncs.c @@ -44,8 +44,8 @@ /* GUC parameters */ int compute_query_id = COMPUTE_QUERY_ID_AUTO; -/* Whether to merge constants in a list when computing query_id */ -bool query_id_const_merge = false; +/* Lower threshold for the list length to merge constants when computing query_id */ +int query_id_const_merge_threshold = 1; /* True when compute_query_id is ON, or AUTO and a module requests them */ bool query_id_enabled = false; @@ -159,12 +159,14 @@ EnableQueryId(void) * Controls constants merging for query identifier computation. * * Third-party plugins can use this function to enable/disable merging - * of constants in a list when query identifier is computed. + * of constants in a list when query identifier is computed. The argument + * specifies the lower threshold for an array length, above which merging will + * be applied. */ void -SetQueryIdConstMerge(bool value) +SetQueryIdConstMerge(int threshold) { - query_id_const_merge = value; + query_id_const_merge_threshold = threshold; } /* @@ -240,7 +242,8 @@ RecordConstLocation(JumbleState *jstate, int location, int magnitude) /* * Verify if the provided list contains could be merged down, which means it - * contains only constant expressions. + * contains only constant expressions and the list contains more than + * query_id_const_merge_threshold elements. * * Return value is the order of magnitude (i.e. how many digits it has) for * length of the list (to use for representation purposes later on) if merging @@ -258,12 +261,18 @@ IsMergeableConstList(List *elements, Const **firstConst, Const **lastConst) if (elements == NULL) return 0; - if (!query_id_const_merge) + if (query_id_const_merge_threshold < 1) { /* Merging is disabled, process everything one by one */ return 0; } + if (elements->length < query_id_const_merge_threshold) + { + /* The list is not large enough */ + return 0; + } + firstExpr = linitial(elements); /* diff --git a/src/backend/postmaster/postmaster.c b/src/backend/postmaster/postmaster.c index 367ea016fb3..78de378b4a9 100644 --- a/src/backend/postmaster/postmaster.c +++ b/src/backend/postmaster/postmaster.c @@ -533,7 +533,7 @@ typedef struct bool redirection_done; bool IsBinaryUpgrade; bool query_id_enabled; - bool query_id_const_merge; + int query_id_const_merge_threshold; int max_safe_fds; int MaxBackends; #ifdef WIN32 @@ -6113,7 +6113,7 @@ save_backend_variables(BackendParameters *param, Port *port, BackgroundWorker *w param->redirection_done = redirection_done; param->IsBinaryUpgrade = IsBinaryUpgrade; param->query_id_enabled = query_id_enabled; - param->query_id_const_merge = query_id_const_merge; + param->query_id_const_merge_threshold = query_id_const_merge_threshold; param->max_safe_fds = max_safe_fds; param->MaxBackends = MaxBackends; @@ -6359,7 +6359,7 @@ restore_backend_variables(BackendParameters *param, Port **port, BackgroundWorke redirection_done = param->redirection_done; IsBinaryUpgrade = param->IsBinaryUpgrade; query_id_enabled = param->query_id_enabled; - query_id_const_merge = param->query_id_const_merge; + query_id_const_merge_threshold = param->query_id_const_merge_threshold; max_safe_fds = param->max_safe_fds; MaxBackends = param->MaxBackends; diff --git a/src/include/nodes/queryjumble.h b/src/include/nodes/queryjumble.h index 551555494e0..ae5907aed84 100644 --- a/src/include/nodes/queryjumble.h +++ b/src/include/nodes/queryjumble.h @@ -74,10 +74,10 @@ extern PGDLLIMPORT int compute_query_id; extern const char *CleanQuerytext(const char *query, int *location, int *len); extern JumbleState *JumbleQuery(Query *query); extern void EnableQueryId(void); -extern void SetQueryIdConstMerge(bool value); +extern void SetQueryIdConstMerge(int threshold); extern PGDLLIMPORT bool query_id_enabled; -extern PGDLLIMPORT bool query_id_const_merge; +extern PGDLLIMPORT int query_id_const_merge_threshold; /* * Returns whether query identifier computation has been enabled, either -- 2.41.0 --tp75mykizkxh6a6a-- ^ permalink raw reply [nested|flat] 8+ messages in thread
* [PATCH v20 4/4] Introduce query_id_const_merge_threshold @ 2024-04-03 18:03 Dmitrii Dolgov <9erthalion6@gmail.com> 0 siblings, 0 replies; 8+ messages in thread From: Dmitrii Dolgov @ 2024-04-03 18:03 UTC (permalink / raw) Replace query_id_const_merge with a threshold to allow merging only if the number of elements is larger than specified value, which could be configured using pg_stat_statements parameter query_id_const_merge_threshold. Reviewed-by: Sutou Kouhei Tested-by: Yasuo Honda --- .../pg_stat_statements/expected/merging.out | 68 ++++++++++++++++++- .../pg_stat_statements/pg_stat_statements.c | 36 +++++----- contrib/pg_stat_statements/sql/merging.sql | 21 +++++- doc/src/sgml/pgstatstatements.sgml | 23 ++++--- src/backend/nodes/queryjumblefuncs.c | 23 +++++-- src/backend/postmaster/launch_backend.c | 6 +- src/include/nodes/queryjumble.h | 4 +- 7 files changed, 137 insertions(+), 44 deletions(-) diff --git a/contrib/pg_stat_statements/expected/merging.out b/contrib/pg_stat_statements/expected/merging.out index 0cb4f67b8b7..552e248ff14 100644 --- a/contrib/pg_stat_statements/expected/merging.out +++ b/contrib/pg_stat_statements/expected/merging.out @@ -36,7 +36,7 @@ SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; (4 rows) -- Normal scenario, too many simple constants for an IN query -SET pg_stat_statements.query_id_const_merge = on; +SET pg_stat_statements.query_id_const_merge_threshold = 1; SELECT pg_stat_statements_reset() IS NOT NULL AS t; t --- @@ -218,4 +218,68 @@ FROM cte; -------- (0 rows) -RESET pg_stat_statements.query_id_const_merge; +-- With the threshold +SET pg_stat_statements.query_id_const_merge_threshold = 10; +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9); + id | data +----+------ +(0 rows) + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10); + id | data +----+------ +(0 rows) + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11); + id | data +----+------ +(0 rows) + +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + query | calls +---------------------------------------------------------------------------+------- + SELECT * FROM test_merge WHERE id IN ($1, $2, $3, $4, $5, $6, $7, $8, $9) | 1 + SELECT * FROM test_merge WHERE id IN (... [10-99 entries]) | 2 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 +(3 rows) + +-- With gaps on the threshold +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4); + id | data +----+------ +(0 rows) + +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + query | calls +-------------------------------------------------------+------- + SELECT * FROM test_merge WHERE id IN ($1, $2, $3, $4) | 1 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 +(2 rows) + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15); + id | data +----+------ +(0 rows) + +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + query | calls +------------------------------------------------------------------------+------- + SELECT * FROM test_merge WHERE id IN ($1, $2, $3, $4) | 1 + SELECT * FROM test_merge WHERE id IN (... [10-99 entries]) | 1 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 + SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C" | 1 +(4 rows) + +RESET pg_stat_statements.query_id_const_merge_threshold; diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c index 0ade4cf515f..ffa832bbfb0 100644 --- a/contrib/pg_stat_statements/pg_stat_statements.c +++ b/contrib/pg_stat_statements/pg_stat_statements.c @@ -265,8 +265,8 @@ static ExecutorFinish_hook_type prev_ExecutorFinish = NULL; static ExecutorEnd_hook_type prev_ExecutorEnd = NULL; static ProcessUtility_hook_type prev_ProcessUtility = NULL; -/* An assign hook to keep query_id_const_merge in sync */ -static void pgss_query_id_const_merge_assign_hook(bool newvalue, void *extra); +/* An assign hook to keep query_id_const_merge_threshold in sync */ +static void pgss_query_id_const_merge_assign_hook(int newvalue, void *extra); /* Links to shared memory state */ static pgssSharedState *pgss = NULL; @@ -295,8 +295,8 @@ static bool pgss_track_utility = true; /* whether to track utility commands */ static bool pgss_track_planning = false; /* whether to track planning * duration */ static bool pgss_save = true; /* whether to save stats across shutdown */ -static bool pgss_query_id_const_merge = false; /* request constants merging - * when computing query_id */ +static int pgss_query_id_const_merge_threshold = 0; /* request constants merging + * when computing query_id */ #define pgss_enabled(level) \ (!IsParallelWorker() && \ @@ -459,20 +459,22 @@ _PG_init(void) NULL, NULL); - DefineCustomBoolVariable("pg_stat_statements.query_id_const_merge", - "Whether to merge constants in a list when computing query_id.", - NULL, - &pgss_query_id_const_merge, - false, - PGC_SUSET, - 0, - NULL, - pgss_query_id_const_merge_assign_hook, - NULL); + DefineCustomIntVariable("pg_stat_statements.query_id_const_merge_threshold", + "Whether to merge constants in a list when computing query_id.", + NULL, + &pgss_query_id_const_merge_threshold, + 0, + 0, + INT_MAX, + PGC_SUSET, + 0, + NULL, + pgss_query_id_const_merge_assign_hook, + NULL); MarkGUCPrefixReserved("pg_stat_statements"); - SetQueryIdConstMerge(pgss_query_id_const_merge); + SetQueryIdConstMerge(pgss_query_id_const_merge_threshold); /* * Install hooks. @@ -3082,10 +3084,10 @@ comp_location(const void *a, const void *b) } /* - * Notify query jumbling about query_id_const_merge status + * Notify query jumbling about query_id_const_merge_threshold status */ static void -pgss_query_id_const_merge_assign_hook(bool newvalue, void *extra) +pgss_query_id_const_merge_assign_hook(int newvalue, void *extra) { SetQueryIdConstMerge(newvalue); } diff --git a/contrib/pg_stat_statements/sql/merging.sql b/contrib/pg_stat_statements/sql/merging.sql index 657044faded..fedeb35b8f5 100644 --- a/contrib/pg_stat_statements/sql/merging.sql +++ b/contrib/pg_stat_statements/sql/merging.sql @@ -15,7 +15,7 @@ SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11); SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; -- Normal scenario, too many simple constants for an IN query -SET pg_stat_statements.query_id_const_merge = on; +SET pg_stat_statements.query_id_const_merge_threshold = 1; SELECT pg_stat_statements_reset() IS NOT NULL AS t; SELECT * FROM test_merge WHERE id IN (1); @@ -68,4 +68,21 @@ WITH cte AS ( SELECT ARRAY['a', 'b', 'c', const::varchar] AS result FROM cte; -RESET pg_stat_statements.query_id_const_merge; +-- With the threshold +SET pg_stat_statements.query_id_const_merge_threshold = 10; + +SELECT pg_stat_statements_reset() IS NOT NULL AS t; +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9); +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10); +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11); +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + +-- With gaps on the threshold +SELECT pg_stat_statements_reset() IS NOT NULL AS t; +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4); +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15); +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + +RESET pg_stat_statements.query_id_const_merge_threshold; diff --git a/doc/src/sgml/pgstatstatements.sgml b/doc/src/sgml/pgstatstatements.sgml index 12ffd021909..c939c316a37 100644 --- a/doc/src/sgml/pgstatstatements.sgml +++ b/doc/src/sgml/pgstatstatements.sgml @@ -605,12 +605,12 @@ In some cases, queries with visibly different texts might get merged into a single <structname>pg_stat_statements</structname> entry. Normally this will happen only for semantically equivalent queries, or if - <varname>pg_stat_statements.query_id_const_merge</varname> is enabled and - the only difference between queries is the length of an array with constants - they contain: + <varname>pg_stat_statements.query_id_const_merge_threshold</varname> is + enabled and the only difference between queries is the length of an array + with constants they contain: <screen> -=# SET query_id_const_merge = on; +=# SET query_id_const_merge_threshold = 1; =# SELECT pg_stat_statements_reset(); =# SELECT * FROM test WHERE a IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11); =# SELECT * FROM test WHERE a IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12); @@ -959,9 +959,9 @@ calls | 1 <varlistentry> <term> - <varname>pg_stat_statements.query_id_const_merge</varname> (<type>bool</type>) + <varname>pg_stat_statements.query_id_const_merge_threshold</varname> (<type>integer</type>) <indexterm> - <primary><varname>pg_stat_statements.query_id_const_merge</varname> configuration parameter</primary> + <primary><varname>pg_stat_statements.query_id_const_merge_threshold</varname> configuration parameter</primary> </indexterm> </term> @@ -973,11 +973,12 @@ calls | 1 query will get multiple different identifiers, one for each occurrence with an array of different lenght. - If this parameter is on, an array of constants will contribute only the - first element, the last element and the number of elements to the query - identifier. It means two occurences of the same query, where the only - difference is number of constants in the array, are going to get the - same query identifier if the arrays are of similar length. + If this parameter is greater than 0, an array with more than + <varname>pg_stat_statements.query_id_const_merge_threshold</varname> + constants will contribute only the first element, the last element + and the number of elements to the query identifier. It means two + occurences of the same query, where the only difference is number of + constants in the array, are going to get the same query identifier. Such queries are represented in form <literal>'(... [10-99 entries])'</literal>. The parameter could be used to reduce amount of repeating data stored diff --git a/src/backend/nodes/queryjumblefuncs.c b/src/backend/nodes/queryjumblefuncs.c index e9ede0acb70..c25cb2be62a 100644 --- a/src/backend/nodes/queryjumblefuncs.c +++ b/src/backend/nodes/queryjumblefuncs.c @@ -44,8 +44,8 @@ /* GUC parameters */ int compute_query_id = COMPUTE_QUERY_ID_AUTO; -/* Whether to merge constants in a list when computing query_id */ -bool query_id_const_merge = false; +/* Lower threshold for the list length to merge constants when computing query_id */ +int query_id_const_merge_threshold = 1; /* * True when compute_query_id is ON or AUTO, and a module requests them. @@ -166,12 +166,14 @@ EnableQueryId(void) * Controls constants merging for query identifier computation. * * Third-party plugins can use this function to enable/disable merging - * of constants in a list when query identifier is computed. + * of constants in a list when query identifier is computed. The argument + * specifies the lower threshold for an array length, above which merging will + * be applied. */ void -SetQueryIdConstMerge(bool value) +SetQueryIdConstMerge(int threshold) { - query_id_const_merge = value; + query_id_const_merge_threshold = threshold; } /* @@ -249,7 +251,8 @@ RecordConstLocation(JumbleState *jstate, int location, int magnitude) /* * Verify if the provided list contains could be merged down, which means it - * contains only constant expressions. + * contains only constant expressions and the list contains more than + * query_id_const_merge_threshold elements. * * Return value is the order of magnitude (i.e. how many digits it has) for * length of the list (to use for representation purposes later on) if merging @@ -267,12 +270,18 @@ IsMergeableConstList(List *elements, Const **firstConst, Const **lastConst) if (elements == NIL) return 0; - if (!query_id_const_merge) + if (query_id_const_merge_threshold < 1) { /* Merging is disabled, process everything one by one */ return 0; } + if (elements->length < query_id_const_merge_threshold) + { + /* The list is not large enough */ + return 0; + } + firstExpr = linitial(elements); /* diff --git a/src/backend/postmaster/launch_backend.c b/src/backend/postmaster/launch_backend.c index 8bd73929183..491858a116e 100644 --- a/src/backend/postmaster/launch_backend.c +++ b/src/backend/postmaster/launch_backend.c @@ -122,7 +122,7 @@ typedef struct bool redirection_done; bool IsBinaryUpgrade; bool query_id_enabled; - bool query_id_const_merge; + int query_id_const_merge_threshold; int max_safe_fds; int MaxBackends; #ifdef WIN32 @@ -743,7 +743,7 @@ save_backend_variables(BackendParameters *param, ClientSocket *client_sock, param->redirection_done = redirection_done; param->IsBinaryUpgrade = IsBinaryUpgrade; param->query_id_enabled = query_id_enabled; - param->query_id_const_merge = query_id_const_merge; + param->query_id_const_merge_threshold = query_id_const_merge_threshold; param->max_safe_fds = max_safe_fds; param->MaxBackends = MaxBackends; @@ -1002,7 +1002,7 @@ restore_backend_variables(BackendParameters *param) redirection_done = param->redirection_done; IsBinaryUpgrade = param->IsBinaryUpgrade; query_id_enabled = param->query_id_enabled; - query_id_const_merge = param->query_id_const_merge; + query_id_const_merge_threshold = param->query_id_const_merge_threshold; max_safe_fds = param->max_safe_fds; MaxBackends = param->MaxBackends; diff --git a/src/include/nodes/queryjumble.h b/src/include/nodes/queryjumble.h index 0e69e420b7f..90218c60531 100644 --- a/src/include/nodes/queryjumble.h +++ b/src/include/nodes/queryjumble.h @@ -77,10 +77,10 @@ extern PGDLLIMPORT int compute_query_id; extern const char *CleanQuerytext(const char *query, int *location, int *len); extern JumbleState *JumbleQuery(Query *query); extern void EnableQueryId(void); -extern void SetQueryIdConstMerge(bool value); +extern void SetQueryIdConstMerge(int threshold); extern PGDLLIMPORT bool query_id_enabled; -extern PGDLLIMPORT bool query_id_const_merge; +extern PGDLLIMPORT int query_id_const_merge_threshold; /* * Returns whether query identifier computation has been enabled, either -- 2.41.0 --eqaczcvvorx3zguj-- ^ permalink raw reply [nested|flat] 8+ messages in thread
* [PATCH v19 4/4] Introduce query_id_const_merge_threshold @ 2024-04-03 18:03 Dmitrii Dolgov <9erthalion6@gmail.com> 0 siblings, 0 replies; 8+ messages in thread From: Dmitrii Dolgov @ 2024-04-03 18:03 UTC (permalink / raw) Replace query_id_const_merge with a threshold to allow merging only if the number of elements is larger than specified value, which could be configured using pg_stat_statements parameter query_id_const_merge_threshold. --- .../pg_stat_statements/expected/merging.out | 68 ++++++++++++++++++- .../pg_stat_statements/pg_stat_statements.c | 36 +++++----- contrib/pg_stat_statements/sql/merging.sql | 21 +++++- doc/src/sgml/pgstatstatements.sgml | 23 ++++--- src/backend/nodes/queryjumblefuncs.c | 23 +++++-- src/backend/postmaster/launch_backend.c | 6 +- src/include/nodes/queryjumble.h | 4 +- 7 files changed, 137 insertions(+), 44 deletions(-) diff --git a/contrib/pg_stat_statements/expected/merging.out b/contrib/pg_stat_statements/expected/merging.out index 0cb4f67b8b7..552e248ff14 100644 --- a/contrib/pg_stat_statements/expected/merging.out +++ b/contrib/pg_stat_statements/expected/merging.out @@ -36,7 +36,7 @@ SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; (4 rows) -- Normal scenario, too many simple constants for an IN query -SET pg_stat_statements.query_id_const_merge = on; +SET pg_stat_statements.query_id_const_merge_threshold = 1; SELECT pg_stat_statements_reset() IS NOT NULL AS t; t --- @@ -218,4 +218,68 @@ FROM cte; -------- (0 rows) -RESET pg_stat_statements.query_id_const_merge; +-- With the threshold +SET pg_stat_statements.query_id_const_merge_threshold = 10; +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9); + id | data +----+------ +(0 rows) + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10); + id | data +----+------ +(0 rows) + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11); + id | data +----+------ +(0 rows) + +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + query | calls +---------------------------------------------------------------------------+------- + SELECT * FROM test_merge WHERE id IN ($1, $2, $3, $4, $5, $6, $7, $8, $9) | 1 + SELECT * FROM test_merge WHERE id IN (... [10-99 entries]) | 2 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 +(3 rows) + +-- With gaps on the threshold +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4); + id | data +----+------ +(0 rows) + +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + query | calls +-------------------------------------------------------+------- + SELECT * FROM test_merge WHERE id IN ($1, $2, $3, $4) | 1 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 +(2 rows) + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15); + id | data +----+------ +(0 rows) + +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + query | calls +------------------------------------------------------------------------+------- + SELECT * FROM test_merge WHERE id IN ($1, $2, $3, $4) | 1 + SELECT * FROM test_merge WHERE id IN (... [10-99 entries]) | 1 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 + SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C" | 1 +(4 rows) + +RESET pg_stat_statements.query_id_const_merge_threshold; diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c index 00eec30feb1..569a74c4368 100644 --- a/contrib/pg_stat_statements/pg_stat_statements.c +++ b/contrib/pg_stat_statements/pg_stat_statements.c @@ -265,8 +265,8 @@ static ExecutorFinish_hook_type prev_ExecutorFinish = NULL; static ExecutorEnd_hook_type prev_ExecutorEnd = NULL; static ProcessUtility_hook_type prev_ProcessUtility = NULL; -/* An assign hook to keep query_id_const_merge in sync */ -static void pgss_query_id_const_merge_assign_hook(bool newvalue, void *extra); +/* An assign hook to keep query_id_const_merge_threshold in sync */ +static void pgss_query_id_const_merge_assign_hook(int newvalue, void *extra); /* Links to shared memory state */ static pgssSharedState *pgss = NULL; @@ -295,8 +295,8 @@ static bool pgss_track_utility = true; /* whether to track utility commands */ static bool pgss_track_planning = false; /* whether to track planning * duration */ static bool pgss_save = true; /* whether to save stats across shutdown */ -static bool pgss_query_id_const_merge = false; /* request constants merging - * when computing query_id */ +static int pgss_query_id_const_merge_threshold = 0; /* request constants merging + * when computing query_id */ #define pgss_enabled(level) \ (!IsParallelWorker() && \ @@ -459,20 +459,22 @@ _PG_init(void) NULL, NULL); - DefineCustomBoolVariable("pg_stat_statements.query_id_const_merge", - "Whether to merge constants in a list when computing query_id.", - NULL, - &pgss_query_id_const_merge, - false, - PGC_SUSET, - 0, - NULL, - pgss_query_id_const_merge_assign_hook, - NULL); + DefineCustomIntVariable("pg_stat_statements.query_id_const_merge_threshold", + "Whether to merge constants in a list when computing query_id.", + NULL, + &pgss_query_id_const_merge_threshold, + 0, + 0, + INT_MAX, + PGC_SUSET, + 0, + NULL, + pgss_query_id_const_merge_assign_hook, + NULL); MarkGUCPrefixReserved("pg_stat_statements"); - SetQueryIdConstMerge(pgss_query_id_const_merge); + SetQueryIdConstMerge(pgss_query_id_const_merge_threshold); /* * Install hooks. @@ -3064,10 +3066,10 @@ comp_location(const void *a, const void *b) } /* - * Notify query jumbling about query_id_const_merge status + * Notify query jumbling about query_id_const_merge_threshold status */ static void -pgss_query_id_const_merge_assign_hook(bool newvalue, void *extra) +pgss_query_id_const_merge_assign_hook(int newvalue, void *extra) { SetQueryIdConstMerge(newvalue); } diff --git a/contrib/pg_stat_statements/sql/merging.sql b/contrib/pg_stat_statements/sql/merging.sql index 657044faded..fedeb35b8f5 100644 --- a/contrib/pg_stat_statements/sql/merging.sql +++ b/contrib/pg_stat_statements/sql/merging.sql @@ -15,7 +15,7 @@ SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11); SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; -- Normal scenario, too many simple constants for an IN query -SET pg_stat_statements.query_id_const_merge = on; +SET pg_stat_statements.query_id_const_merge_threshold = 1; SELECT pg_stat_statements_reset() IS NOT NULL AS t; SELECT * FROM test_merge WHERE id IN (1); @@ -68,4 +68,21 @@ WITH cte AS ( SELECT ARRAY['a', 'b', 'c', const::varchar] AS result FROM cte; -RESET pg_stat_statements.query_id_const_merge; +-- With the threshold +SET pg_stat_statements.query_id_const_merge_threshold = 10; + +SELECT pg_stat_statements_reset() IS NOT NULL AS t; +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9); +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10); +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11); +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + +-- With gaps on the threshold +SELECT pg_stat_statements_reset() IS NOT NULL AS t; +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4); +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15); +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + +RESET pg_stat_statements.query_id_const_merge_threshold; diff --git a/doc/src/sgml/pgstatstatements.sgml b/doc/src/sgml/pgstatstatements.sgml index bac029430be..3a060935bff 100644 --- a/doc/src/sgml/pgstatstatements.sgml +++ b/doc/src/sgml/pgstatstatements.sgml @@ -605,12 +605,12 @@ In some cases, queries with visibly different texts might get merged into a single <structname>pg_stat_statements</structname> entry. Normally this will happen only for semantically equivalent queries, or if - <varname>pg_stat_statements.query_id_const_merge</varname> is enabled and - the only difference between queries is the length of an array with constants - they contain: + <varname>pg_stat_statements.query_id_const_merge_threshold</varname> is + enabled and the only difference between queries is the length of an array + with constants they contain: <screen> -=# SET query_id_const_merge = on; +=# SET query_id_const_merge_threshold = 1; =# SELECT pg_stat_statements_reset(); =# SELECT * FROM test WHERE a IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11); =# SELECT * FROM test WHERE a IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12); @@ -953,9 +953,9 @@ calls | 1 <varlistentry> <term> - <varname>pg_stat_statements.query_id_const_merge</varname> (<type>bool</type>) + <varname>pg_stat_statements.query_id_const_merge_threshold</varname> (<type>integer</type>) <indexterm> - <primary><varname>pg_stat_statements.query_id_const_merge</varname> configuration parameter</primary> + <primary><varname>pg_stat_statements.query_id_const_merge_threshold</varname> configuration parameter</primary> </indexterm> </term> @@ -967,11 +967,12 @@ calls | 1 query will get multiple different identifiers, one for each occurrence with an array of different lenght. - If this parameter is on, an array of constants will contribute only the - first element, the last element and the number of elements to the query - identifier. It means two occurences of the same query, where the only - difference is number of constants in the array, are going to get the - same query identifier if the arrays are of similar length. + If this parameter is greater than 0, an array with more than + <varname>pg_stat_statements.query_id_const_merge_threshold</varname> + constants will contribute only the first element, the last element + and the number of elements to the query identifier. It means two + occurences of the same query, where the only difference is number of + constants in the array, are going to get the same query identifier. Such queries are represented in form <literal>'(... [10-99 entries])'</literal>. The parameter could be used to reduce amount of repeating data stored diff --git a/src/backend/nodes/queryjumblefuncs.c b/src/backend/nodes/queryjumblefuncs.c index a27202cb279..66696c87475 100644 --- a/src/backend/nodes/queryjumblefuncs.c +++ b/src/backend/nodes/queryjumblefuncs.c @@ -44,8 +44,8 @@ /* GUC parameters */ int compute_query_id = COMPUTE_QUERY_ID_AUTO; -/* Whether to merge constants in a list when computing query_id */ -bool query_id_const_merge = false; +/* Lower threshold for the list length to merge constants when computing query_id */ +int query_id_const_merge_threshold = 1; /* * True when compute_query_id is ON or AUTO, and a module requests them. @@ -165,12 +165,14 @@ EnableQueryId(void) * Controls constants merging for query identifier computation. * * Third-party plugins can use this function to enable/disable merging - * of constants in a list when query identifier is computed. + * of constants in a list when query identifier is computed. The argument + * specifies the lower threshold for an array length, above which merging will + * be applied. */ void -SetQueryIdConstMerge(bool value) +SetQueryIdConstMerge(int threshold) { - query_id_const_merge = value; + query_id_const_merge_threshold = threshold; } /* @@ -246,7 +248,8 @@ RecordConstLocation(JumbleState *jstate, int location, int magnitude) /* * Verify if the provided list contains could be merged down, which means it - * contains only constant expressions. + * contains only constant expressions and the list contains more than + * query_id_const_merge_threshold elements. * * Return value is the order of magnitude (i.e. how many digits it has) for * length of the list (to use for representation purposes later on) if merging @@ -264,12 +267,18 @@ IsMergeableConstList(List *elements, Const **firstConst, Const **lastConst) if (elements == NULL) return 0; - if (!query_id_const_merge) + if (query_id_const_merge_threshold < 1) { /* Merging is disabled, process everything one by one */ return 0; } + if (elements->length < query_id_const_merge_threshold) + { + /* The list is not large enough */ + return 0; + } + firstExpr = linitial(elements); /* diff --git a/src/backend/postmaster/launch_backend.c b/src/backend/postmaster/launch_backend.c index 5bb2f00c6ef..84b214a5952 100644 --- a/src/backend/postmaster/launch_backend.c +++ b/src/backend/postmaster/launch_backend.c @@ -122,7 +122,7 @@ typedef struct bool redirection_done; bool IsBinaryUpgrade; bool query_id_enabled; - bool query_id_const_merge; + int query_id_const_merge_threshold; int max_safe_fds; int MaxBackends; #ifdef WIN32 @@ -743,7 +743,7 @@ save_backend_variables(BackendParameters *param, ClientSocket *client_sock, param->redirection_done = redirection_done; param->IsBinaryUpgrade = IsBinaryUpgrade; param->query_id_enabled = query_id_enabled; - param->query_id_const_merge = query_id_const_merge; + param->query_id_const_merge_threshold = query_id_const_merge_threshold; param->max_safe_fds = max_safe_fds; param->MaxBackends = MaxBackends; @@ -1002,7 +1002,7 @@ restore_backend_variables(BackendParameters *param) redirection_done = param->redirection_done; IsBinaryUpgrade = param->IsBinaryUpgrade; query_id_enabled = param->query_id_enabled; - query_id_const_merge = param->query_id_const_merge; + query_id_const_merge_threshold = param->query_id_const_merge_threshold; max_safe_fds = param->max_safe_fds; MaxBackends = param->MaxBackends; diff --git a/src/include/nodes/queryjumble.h b/src/include/nodes/queryjumble.h index 551555494e0..ae5907aed84 100644 --- a/src/include/nodes/queryjumble.h +++ b/src/include/nodes/queryjumble.h @@ -74,10 +74,10 @@ extern PGDLLIMPORT int compute_query_id; extern const char *CleanQuerytext(const char *query, int *location, int *len); extern JumbleState *JumbleQuery(Query *query); extern void EnableQueryId(void); -extern void SetQueryIdConstMerge(bool value); +extern void SetQueryIdConstMerge(int threshold); extern PGDLLIMPORT bool query_id_enabled; -extern PGDLLIMPORT bool query_id_const_merge; +extern PGDLLIMPORT int query_id_const_merge_threshold; /* * Returns whether query identifier computation has been enabled, either -- 2.41.0 --yzrwxjftwx5vp4wd-- ^ permalink raw reply [nested|flat] 8+ messages in thread
* [PATCH v21 4/4] Introduce query_id_const_merge_threshold @ 2024-04-03 18:03 Dmitrii Dolgov <9erthalion6@gmail.com> 0 siblings, 0 replies; 8+ messages in thread From: Dmitrii Dolgov @ 2024-04-03 18:03 UTC (permalink / raw) Replace query_id_const_merge with a threshold to allow merging only if the number of elements is larger than specified value, which could be configured using pg_stat_statements parameter query_id_const_merge_threshold. Reviewed-by: Sutou Kouhei Tested-by: Yasuo Honda --- .../pg_stat_statements/expected/merging.out | 68 ++++++++++++++++++- .../pg_stat_statements/pg_stat_statements.c | 36 +++++----- contrib/pg_stat_statements/sql/merging.sql | 21 +++++- doc/src/sgml/pgstatstatements.sgml | 23 ++++--- src/backend/nodes/queryjumblefuncs.c | 23 +++++-- src/backend/postmaster/launch_backend.c | 6 +- src/include/nodes/queryjumble.h | 4 +- 7 files changed, 137 insertions(+), 44 deletions(-) diff --git a/contrib/pg_stat_statements/expected/merging.out b/contrib/pg_stat_statements/expected/merging.out index 0cb4f67b8b..552e248ff1 100644 --- a/contrib/pg_stat_statements/expected/merging.out +++ b/contrib/pg_stat_statements/expected/merging.out @@ -36,7 +36,7 @@ SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; (4 rows) -- Normal scenario, too many simple constants for an IN query -SET pg_stat_statements.query_id_const_merge = on; +SET pg_stat_statements.query_id_const_merge_threshold = 1; SELECT pg_stat_statements_reset() IS NOT NULL AS t; t --- @@ -218,4 +218,68 @@ FROM cte; -------- (0 rows) -RESET pg_stat_statements.query_id_const_merge; +-- With the threshold +SET pg_stat_statements.query_id_const_merge_threshold = 10; +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9); + id | data +----+------ +(0 rows) + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10); + id | data +----+------ +(0 rows) + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11); + id | data +----+------ +(0 rows) + +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + query | calls +---------------------------------------------------------------------------+------- + SELECT * FROM test_merge WHERE id IN ($1, $2, $3, $4, $5, $6, $7, $8, $9) | 1 + SELECT * FROM test_merge WHERE id IN (... [10-99 entries]) | 2 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 +(3 rows) + +-- With gaps on the threshold +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4); + id | data +----+------ +(0 rows) + +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + query | calls +-------------------------------------------------------+------- + SELECT * FROM test_merge WHERE id IN ($1, $2, $3, $4) | 1 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 +(2 rows) + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15); + id | data +----+------ +(0 rows) + +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + query | calls +------------------------------------------------------------------------+------- + SELECT * FROM test_merge WHERE id IN ($1, $2, $3, $4) | 1 + SELECT * FROM test_merge WHERE id IN (... [10-99 entries]) | 1 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 + SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C" | 1 +(4 rows) + +RESET pg_stat_statements.query_id_const_merge_threshold; diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c index 1c35e10117..ae672fcead 100644 --- a/contrib/pg_stat_statements/pg_stat_statements.c +++ b/contrib/pg_stat_statements/pg_stat_statements.c @@ -265,8 +265,8 @@ static ExecutorFinish_hook_type prev_ExecutorFinish = NULL; static ExecutorEnd_hook_type prev_ExecutorEnd = NULL; static ProcessUtility_hook_type prev_ProcessUtility = NULL; -/* An assign hook to keep query_id_const_merge in sync */ -static void pgss_query_id_const_merge_assign_hook(bool newvalue, void *extra); +/* An assign hook to keep query_id_const_merge_threshold in sync */ +static void pgss_query_id_const_merge_assign_hook(int newvalue, void *extra); /* Links to shared memory state */ static pgssSharedState *pgss = NULL; @@ -295,8 +295,8 @@ static bool pgss_track_utility = true; /* whether to track utility commands */ static bool pgss_track_planning = false; /* whether to track planning * duration */ static bool pgss_save = true; /* whether to save stats across shutdown */ -static bool pgss_query_id_const_merge = false; /* request constants merging - * when computing query_id */ +static int pgss_query_id_const_merge_threshold = 0; /* request constants merging + * when computing query_id */ #define pgss_enabled(level) \ (!IsParallelWorker() && \ @@ -459,20 +459,22 @@ _PG_init(void) NULL, NULL); - DefineCustomBoolVariable("pg_stat_statements.query_id_const_merge", - "Whether to merge constants in a list when computing query_id.", - NULL, - &pgss_query_id_const_merge, - false, - PGC_SUSET, - 0, - NULL, - pgss_query_id_const_merge_assign_hook, - NULL); + DefineCustomIntVariable("pg_stat_statements.query_id_const_merge_threshold", + "Whether to merge constants in a list when computing query_id.", + NULL, + &pgss_query_id_const_merge_threshold, + 0, + 0, + INT_MAX, + PGC_SUSET, + 0, + NULL, + pgss_query_id_const_merge_assign_hook, + NULL); MarkGUCPrefixReserved("pg_stat_statements"); - SetQueryIdConstMerge(pgss_query_id_const_merge); + SetQueryIdConstMerge(pgss_query_id_const_merge_threshold); /* * Install hooks. @@ -3082,10 +3084,10 @@ comp_location(const void *a, const void *b) } /* - * Notify query jumbling about query_id_const_merge status + * Notify query jumbling about query_id_const_merge_threshold status */ static void -pgss_query_id_const_merge_assign_hook(bool newvalue, void *extra) +pgss_query_id_const_merge_assign_hook(int newvalue, void *extra) { SetQueryIdConstMerge(newvalue); } diff --git a/contrib/pg_stat_statements/sql/merging.sql b/contrib/pg_stat_statements/sql/merging.sql index 657044fade..fedeb35b8f 100644 --- a/contrib/pg_stat_statements/sql/merging.sql +++ b/contrib/pg_stat_statements/sql/merging.sql @@ -15,7 +15,7 @@ SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11); SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; -- Normal scenario, too many simple constants for an IN query -SET pg_stat_statements.query_id_const_merge = on; +SET pg_stat_statements.query_id_const_merge_threshold = 1; SELECT pg_stat_statements_reset() IS NOT NULL AS t; SELECT * FROM test_merge WHERE id IN (1); @@ -68,4 +68,21 @@ WITH cte AS ( SELECT ARRAY['a', 'b', 'c', const::varchar] AS result FROM cte; -RESET pg_stat_statements.query_id_const_merge; +-- With the threshold +SET pg_stat_statements.query_id_const_merge_threshold = 10; + +SELECT pg_stat_statements_reset() IS NOT NULL AS t; +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9); +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10); +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11); +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + +-- With gaps on the threshold +SELECT pg_stat_statements_reset() IS NOT NULL AS t; +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4); +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15); +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + +RESET pg_stat_statements.query_id_const_merge_threshold; diff --git a/doc/src/sgml/pgstatstatements.sgml b/doc/src/sgml/pgstatstatements.sgml index 12ffd02190..c939c316a3 100644 --- a/doc/src/sgml/pgstatstatements.sgml +++ b/doc/src/sgml/pgstatstatements.sgml @@ -605,12 +605,12 @@ In some cases, queries with visibly different texts might get merged into a single <structname>pg_stat_statements</structname> entry. Normally this will happen only for semantically equivalent queries, or if - <varname>pg_stat_statements.query_id_const_merge</varname> is enabled and - the only difference between queries is the length of an array with constants - they contain: + <varname>pg_stat_statements.query_id_const_merge_threshold</varname> is + enabled and the only difference between queries is the length of an array + with constants they contain: <screen> -=# SET query_id_const_merge = on; +=# SET query_id_const_merge_threshold = 1; =# SELECT pg_stat_statements_reset(); =# SELECT * FROM test WHERE a IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11); =# SELECT * FROM test WHERE a IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12); @@ -959,9 +959,9 @@ calls | 1 <varlistentry> <term> - <varname>pg_stat_statements.query_id_const_merge</varname> (<type>bool</type>) + <varname>pg_stat_statements.query_id_const_merge_threshold</varname> (<type>integer</type>) <indexterm> - <primary><varname>pg_stat_statements.query_id_const_merge</varname> configuration parameter</primary> + <primary><varname>pg_stat_statements.query_id_const_merge_threshold</varname> configuration parameter</primary> </indexterm> </term> @@ -973,11 +973,12 @@ calls | 1 query will get multiple different identifiers, one for each occurrence with an array of different lenght. - If this parameter is on, an array of constants will contribute only the - first element, the last element and the number of elements to the query - identifier. It means two occurences of the same query, where the only - difference is number of constants in the array, are going to get the - same query identifier if the arrays are of similar length. + If this parameter is greater than 0, an array with more than + <varname>pg_stat_statements.query_id_const_merge_threshold</varname> + constants will contribute only the first element, the last element + and the number of elements to the query identifier. It means two + occurences of the same query, where the only difference is number of + constants in the array, are going to get the same query identifier. Such queries are represented in form <literal>'(... [10-99 entries])'</literal>. The parameter could be used to reduce amount of repeating data stored diff --git a/src/backend/nodes/queryjumblefuncs.c b/src/backend/nodes/queryjumblefuncs.c index 1d3f36ca64..37a47072fb 100644 --- a/src/backend/nodes/queryjumblefuncs.c +++ b/src/backend/nodes/queryjumblefuncs.c @@ -44,8 +44,8 @@ /* GUC parameters */ int compute_query_id = COMPUTE_QUERY_ID_AUTO; -/* Whether to merge constants in a list when computing query_id */ -bool query_id_const_merge = false; +/* Lower threshold for the list length to merge constants when computing query_id */ +int query_id_const_merge_threshold = 1; /* * True when compute_query_id is ON or AUTO, and a module requests them. @@ -165,12 +165,14 @@ EnableQueryId(void) * Controls constants merging for query identifier computation. * * Third-party plugins can use this function to enable/disable merging - * of constants in a list when query identifier is computed. + * of constants in a list when query identifier is computed. The argument + * specifies the lower threshold for an array length, above which merging will + * be applied. */ void -SetQueryIdConstMerge(bool value) +SetQueryIdConstMerge(int threshold) { - query_id_const_merge = value; + query_id_const_merge_threshold = threshold; } /* @@ -248,7 +250,8 @@ RecordConstLocation(JumbleState *jstate, int location, int magnitude) /* * Verify if the provided list contains could be merged down, which means it - * contains only constant expressions. + * contains only constant expressions and the list contains more than + * query_id_const_merge_threshold elements. * * Return value is the order of magnitude (i.e. how many digits it has) for * length of the list (to use for representation purposes later on) if merging @@ -266,12 +269,18 @@ IsMergeableConstList(List *elements, Const **firstConst, Const **lastConst) if (elements == NIL) return 0; - if (!query_id_const_merge) + if (query_id_const_merge_threshold < 1) { /* Merging is disabled, process everything one by one */ return 0; } + if (elements->length < query_id_const_merge_threshold) + { + /* The list is not large enough */ + return 0; + } + firstExpr = linitial(elements); /* diff --git a/src/backend/postmaster/launch_backend.c b/src/backend/postmaster/launch_backend.c index da3ceceddb..f8a232b6a2 100644 --- a/src/backend/postmaster/launch_backend.c +++ b/src/backend/postmaster/launch_backend.c @@ -122,7 +122,7 @@ typedef struct bool redirection_done; bool IsBinaryUpgrade; bool query_id_enabled; - bool query_id_const_merge; + int query_id_const_merge_threshold; int max_safe_fds; int MaxBackends; #ifdef WIN32 @@ -731,7 +731,7 @@ save_backend_variables(BackendParameters *param, ClientSocket *client_sock, param->redirection_done = redirection_done; param->IsBinaryUpgrade = IsBinaryUpgrade; param->query_id_enabled = query_id_enabled; - param->query_id_const_merge = query_id_const_merge; + param->query_id_const_merge_threshold = query_id_const_merge_threshold; param->max_safe_fds = max_safe_fds; param->MaxBackends = MaxBackends; @@ -991,7 +991,7 @@ restore_backend_variables(BackendParameters *param) redirection_done = param->redirection_done; IsBinaryUpgrade = param->IsBinaryUpgrade; query_id_enabled = param->query_id_enabled; - query_id_const_merge = param->query_id_const_merge; + query_id_const_merge_threshold = param->query_id_const_merge_threshold; max_safe_fds = param->max_safe_fds; MaxBackends = param->MaxBackends; diff --git a/src/include/nodes/queryjumble.h b/src/include/nodes/queryjumble.h index 0e69e420b7..90218c6053 100644 --- a/src/include/nodes/queryjumble.h +++ b/src/include/nodes/queryjumble.h @@ -77,10 +77,10 @@ extern PGDLLIMPORT int compute_query_id; extern const char *CleanQuerytext(const char *query, int *location, int *len); extern JumbleState *JumbleQuery(Query *query); extern void EnableQueryId(void); -extern void SetQueryIdConstMerge(bool value); +extern void SetQueryIdConstMerge(int threshold); extern PGDLLIMPORT bool query_id_enabled; -extern PGDLLIMPORT bool query_id_const_merge; +extern PGDLLIMPORT int query_id_const_merge_threshold; /* * Returns whether query identifier computation has been enabled, either -- 2.45.1 --7t7bac4aexischdh-- ^ permalink raw reply [nested|flat] 8+ messages in thread
* [PATCH v22 4/4] Introduce query_id_const_merge_threshold @ 2024-04-03 18:03 Dmitrii Dolgov <9erthalion6@gmail.com> 0 siblings, 0 replies; 8+ messages in thread From: Dmitrii Dolgov @ 2024-04-03 18:03 UTC (permalink / raw) Replace query_id_const_merge with a threshold to allow merging only if the number of elements is larger than specified value, which could be configured using pg_stat_statements parameter query_id_const_merge_threshold. Reviewed-by: Sutou Kouhei Tested-by: Yasuo Honda --- .../pg_stat_statements/expected/merging.out | 68 ++++++++++++++++++- .../pg_stat_statements/pg_stat_statements.c | 36 +++++----- contrib/pg_stat_statements/sql/merging.sql | 21 +++++- doc/src/sgml/pgstatstatements.sgml | 23 ++++--- src/backend/nodes/queryjumblefuncs.c | 23 +++++-- src/backend/postmaster/launch_backend.c | 6 +- src/include/nodes/queryjumble.h | 4 +- 7 files changed, 137 insertions(+), 44 deletions(-) diff --git a/contrib/pg_stat_statements/expected/merging.out b/contrib/pg_stat_statements/expected/merging.out index 0cb4f67b8b..552e248ff1 100644 --- a/contrib/pg_stat_statements/expected/merging.out +++ b/contrib/pg_stat_statements/expected/merging.out @@ -36,7 +36,7 @@ SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; (4 rows) -- Normal scenario, too many simple constants for an IN query -SET pg_stat_statements.query_id_const_merge = on; +SET pg_stat_statements.query_id_const_merge_threshold = 1; SELECT pg_stat_statements_reset() IS NOT NULL AS t; t --- @@ -218,4 +218,68 @@ FROM cte; -------- (0 rows) -RESET pg_stat_statements.query_id_const_merge; +-- With the threshold +SET pg_stat_statements.query_id_const_merge_threshold = 10; +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9); + id | data +----+------ +(0 rows) + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10); + id | data +----+------ +(0 rows) + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11); + id | data +----+------ +(0 rows) + +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + query | calls +---------------------------------------------------------------------------+------- + SELECT * FROM test_merge WHERE id IN ($1, $2, $3, $4, $5, $6, $7, $8, $9) | 1 + SELECT * FROM test_merge WHERE id IN (... [10-99 entries]) | 2 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 +(3 rows) + +-- With gaps on the threshold +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4); + id | data +----+------ +(0 rows) + +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + query | calls +-------------------------------------------------------+------- + SELECT * FROM test_merge WHERE id IN ($1, $2, $3, $4) | 1 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 +(2 rows) + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15); + id | data +----+------ +(0 rows) + +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + query | calls +------------------------------------------------------------------------+------- + SELECT * FROM test_merge WHERE id IN ($1, $2, $3, $4) | 1 + SELECT * FROM test_merge WHERE id IN (... [10-99 entries]) | 1 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 + SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C" | 1 +(4 rows) + +RESET pg_stat_statements.query_id_const_merge_threshold; diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c index 24ab2a45ff..61b1c4ea30 100644 --- a/contrib/pg_stat_statements/pg_stat_statements.c +++ b/contrib/pg_stat_statements/pg_stat_statements.c @@ -267,8 +267,8 @@ static ExecutorFinish_hook_type prev_ExecutorFinish = NULL; static ExecutorEnd_hook_type prev_ExecutorEnd = NULL; static ProcessUtility_hook_type prev_ProcessUtility = NULL; -/* An assign hook to keep query_id_const_merge in sync */ -static void pgss_query_id_const_merge_assign_hook(bool newvalue, void *extra); +/* An assign hook to keep query_id_const_merge_threshold in sync */ +static void pgss_query_id_const_merge_assign_hook(int newvalue, void *extra); /* Links to shared memory state */ static pgssSharedState *pgss = NULL; @@ -297,8 +297,8 @@ static bool pgss_track_utility = true; /* whether to track utility commands */ static bool pgss_track_planning = false; /* whether to track planning * duration */ static bool pgss_save = true; /* whether to save stats across shutdown */ -static bool pgss_query_id_const_merge = false; /* request constants merging - * when computing query_id */ +static int pgss_query_id_const_merge_threshold = 0; /* request constants merging + * when computing query_id */ #define pgss_enabled(level) \ (!IsParallelWorker() && \ @@ -463,20 +463,22 @@ _PG_init(void) NULL, NULL); - DefineCustomBoolVariable("pg_stat_statements.query_id_const_merge", - "Whether to merge constants in a list when computing query_id.", - NULL, - &pgss_query_id_const_merge, - false, - PGC_SUSET, - 0, - NULL, - pgss_query_id_const_merge_assign_hook, - NULL); + DefineCustomIntVariable("pg_stat_statements.query_id_const_merge_threshold", + "Whether to merge constants in a list when computing query_id.", + NULL, + &pgss_query_id_const_merge_threshold, + 0, + 0, + INT_MAX, + PGC_SUSET, + 0, + NULL, + pgss_query_id_const_merge_assign_hook, + NULL); MarkGUCPrefixReserved("pg_stat_statements"); - SetQueryIdConstMerge(pgss_query_id_const_merge); + SetQueryIdConstMerge(pgss_query_id_const_merge_threshold); /* * Install hooks. @@ -3084,10 +3086,10 @@ comp_location(const void *a, const void *b) } /* - * Notify query jumbling about query_id_const_merge status + * Notify query jumbling about query_id_const_merge_threshold status */ static void -pgss_query_id_const_merge_assign_hook(bool newvalue, void *extra) +pgss_query_id_const_merge_assign_hook(int newvalue, void *extra) { SetQueryIdConstMerge(newvalue); } diff --git a/contrib/pg_stat_statements/sql/merging.sql b/contrib/pg_stat_statements/sql/merging.sql index 657044fade..fedeb35b8f 100644 --- a/contrib/pg_stat_statements/sql/merging.sql +++ b/contrib/pg_stat_statements/sql/merging.sql @@ -15,7 +15,7 @@ SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11); SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; -- Normal scenario, too many simple constants for an IN query -SET pg_stat_statements.query_id_const_merge = on; +SET pg_stat_statements.query_id_const_merge_threshold = 1; SELECT pg_stat_statements_reset() IS NOT NULL AS t; SELECT * FROM test_merge WHERE id IN (1); @@ -68,4 +68,21 @@ WITH cte AS ( SELECT ARRAY['a', 'b', 'c', const::varchar] AS result FROM cte; -RESET pg_stat_statements.query_id_const_merge; +-- With the threshold +SET pg_stat_statements.query_id_const_merge_threshold = 10; + +SELECT pg_stat_statements_reset() IS NOT NULL AS t; +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9); +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10); +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11); +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + +-- With gaps on the threshold +SELECT pg_stat_statements_reset() IS NOT NULL AS t; +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4); +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + +SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15); +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + +RESET pg_stat_statements.query_id_const_merge_threshold; diff --git a/doc/src/sgml/pgstatstatements.sgml b/doc/src/sgml/pgstatstatements.sgml index 55dfa5b50f..8d107e7706 100644 --- a/doc/src/sgml/pgstatstatements.sgml +++ b/doc/src/sgml/pgstatstatements.sgml @@ -623,12 +623,12 @@ In some cases, queries with visibly different texts might get merged into a single <structname>pg_stat_statements</structname> entry. Normally this will happen only for semantically equivalent queries, or if - <varname>pg_stat_statements.query_id_const_merge</varname> is enabled and - the only difference between queries is the length of an array with constants - they contain: + <varname>pg_stat_statements.query_id_const_merge_threshold</varname> is + enabled and the only difference between queries is the length of an array + with constants they contain: <screen> -=# SET query_id_const_merge = on; +=# SET query_id_const_merge_threshold = 1; =# SELECT pg_stat_statements_reset(); =# SELECT * FROM test WHERE a IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11); =# SELECT * FROM test WHERE a IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12); @@ -977,9 +977,9 @@ calls | 1 <varlistentry> <term> - <varname>pg_stat_statements.query_id_const_merge</varname> (<type>bool</type>) + <varname>pg_stat_statements.query_id_const_merge_threshold</varname> (<type>integer</type>) <indexterm> - <primary><varname>pg_stat_statements.query_id_const_merge</varname> configuration parameter</primary> + <primary><varname>pg_stat_statements.query_id_const_merge_threshold</varname> configuration parameter</primary> </indexterm> </term> @@ -991,11 +991,12 @@ calls | 1 query will get multiple different identifiers, one for each occurrence with an array of different lenght. - If this parameter is on, an array of constants will contribute only the - first element, the last element and the number of elements to the query - identifier. It means two occurences of the same query, where the only - difference is number of constants in the array, are going to get the - same query identifier if the arrays are of similar length. + If this parameter is greater than 0, an array with more than + <varname>pg_stat_statements.query_id_const_merge_threshold</varname> + constants will contribute only the first element, the last element + and the number of elements to the query identifier. It means two + occurences of the same query, where the only difference is number of + constants in the array, are going to get the same query identifier. Such queries are represented in form <literal>'(... [10-99 entries])'</literal>. The parameter could be used to reduce amount of repeating data stored diff --git a/src/backend/nodes/queryjumblefuncs.c b/src/backend/nodes/queryjumblefuncs.c index 8ab3261d66..88a94be933 100644 --- a/src/backend/nodes/queryjumblefuncs.c +++ b/src/backend/nodes/queryjumblefuncs.c @@ -44,8 +44,8 @@ /* GUC parameters */ int compute_query_id = COMPUTE_QUERY_ID_AUTO; -/* Whether to merge constants in a list when computing query_id */ -bool query_id_const_merge = false; +/* Lower threshold for the list length to merge constants when computing query_id */ +int query_id_const_merge_threshold = 1; /* * True when compute_query_id is ON or AUTO, and a module requests them. @@ -172,12 +172,14 @@ EnableQueryId(void) * Controls constants merging for query identifier computation. * * Third-party plugins can use this function to enable/disable merging - * of constants in a list when query identifier is computed. + * of constants in a list when query identifier is computed. The argument + * specifies the lower threshold for an array length, above which merging will + * be applied. */ void -SetQueryIdConstMerge(bool value) +SetQueryIdConstMerge(int threshold) { - query_id_const_merge = value; + query_id_const_merge_threshold = threshold; } /* @@ -255,7 +257,8 @@ RecordConstLocation(JumbleState *jstate, int location, int magnitude) /* * Verify if the provided list contains could be merged down, which means it - * contains only constant expressions. + * contains only constant expressions and the list contains more than + * query_id_const_merge_threshold elements. * * Return value is the order of magnitude (i.e. how many digits it has) for * length of the list (to use for representation purposes later on) if merging @@ -273,12 +276,18 @@ IsMergeableConstList(List *elements, Const **firstConst, Const **lastConst) if (elements == NIL) return 0; - if (!query_id_const_merge) + if (query_id_const_merge_threshold < 1) { /* Merging is disabled, process everything one by one */ return 0; } + if (elements->length < query_id_const_merge_threshold) + { + /* The list is not large enough */ + return 0; + } + firstExpr = linitial(elements); /* diff --git a/src/backend/postmaster/launch_backend.c b/src/backend/postmaster/launch_backend.c index 0e2761edd5..8e438084e5 100644 --- a/src/backend/postmaster/launch_backend.c +++ b/src/backend/postmaster/launch_backend.c @@ -115,7 +115,7 @@ typedef struct bool redirection_done; bool IsBinaryUpgrade; bool query_id_enabled; - bool query_id_const_merge; + int query_id_const_merge_threshold; int max_safe_fds; int MaxBackends; int num_pmchild_slots; @@ -745,7 +745,7 @@ save_backend_variables(BackendParameters *param, param->redirection_done = redirection_done; param->IsBinaryUpgrade = IsBinaryUpgrade; param->query_id_enabled = query_id_enabled; - param->query_id_const_merge = query_id_const_merge; + param->query_id_const_merge_threshold = query_id_const_merge_threshold; param->max_safe_fds = max_safe_fds; param->MaxBackends = MaxBackends; @@ -1006,7 +1006,7 @@ restore_backend_variables(BackendParameters *param) redirection_done = param->redirection_done; IsBinaryUpgrade = param->IsBinaryUpgrade; query_id_enabled = param->query_id_enabled; - query_id_const_merge = param->query_id_const_merge; + query_id_const_merge_threshold = param->query_id_const_merge_threshold; max_safe_fds = param->max_safe_fds; MaxBackends = param->MaxBackends; diff --git a/src/include/nodes/queryjumble.h b/src/include/nodes/queryjumble.h index 0e69e420b7..90218c6053 100644 --- a/src/include/nodes/queryjumble.h +++ b/src/include/nodes/queryjumble.h @@ -77,10 +77,10 @@ extern PGDLLIMPORT int compute_query_id; extern const char *CleanQuerytext(const char *query, int *location, int *len); extern JumbleState *JumbleQuery(Query *query); extern void EnableQueryId(void); -extern void SetQueryIdConstMerge(bool value); +extern void SetQueryIdConstMerge(int threshold); extern PGDLLIMPORT bool query_id_enabled; -extern PGDLLIMPORT bool query_id_const_merge; +extern PGDLLIMPORT int query_id_const_merge_threshold; /* * Returns whether query identifier computation has been enabled, either -- 2.45.1 --nse6ci3hmyxhp7l3-- ^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2024-04-03 18:03 UTC | newest] Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2020-12-09 08:21 [PATCH v3 2/3] New test for timeline-tracking of walsender Kyotaro Horiguchi <horikyoga.ntt@gmail.com> 2023-10-16 14:52 [PATCH v16 4/4] Introduce query_id_const_merge_threshold Dmitrii Dolgov <9erthalion6@gmail.com> 2024-01-13 13:52 [PATCH v17 4/4] Introduce query_id_const_merge_threshold Dmitrii Dolgov <9erthalion6@gmail.com> 2024-01-22 20:31 [PATCH v18 4/4] Introduce query_id_const_merge_threshold Dmitrii Dolgov <9erthalion6@gmail.com> 2024-04-03 18:03 [PATCH v20 4/4] Introduce query_id_const_merge_threshold Dmitrii Dolgov <9erthalion6@gmail.com> 2024-04-03 18:03 [PATCH v19 4/4] Introduce query_id_const_merge_threshold Dmitrii Dolgov <9erthalion6@gmail.com> 2024-04-03 18:03 [PATCH v21 4/4] Introduce query_id_const_merge_threshold Dmitrii Dolgov <9erthalion6@gmail.com> 2024-04-03 18:03 [PATCH v22 4/4] Introduce query_id_const_merge_threshold Dmitrii Dolgov <9erthalion6@gmail.com>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox