On Thu, May 29, 2025 at 11:30:12AM +0900, Michael Paquier wrote: > I still need to review the rest of the patch series..
The test additions done in v7-0002 look sensible here.
--- In the following two queries the operator expressions (+) and (@) have
--- different oppno, and will be given different query_id if squashed, even
though
--- the normalized query will be the same
In v7-0002, this comment is removed, but it still applies, isn't it?
+-- The casted ARRAY expressions will have the same queryId as the IN clause
+-- form of the query
Interesting distinction that explains the differences in counts. Yes
it's a good idea to track this kind of behavior in the tests.
--- Bigint, explicit cast is not squashed
+-- Bigint, explicit cast is squashed
Seems incorrect with 0002 taken in isolation. The last cast is still
present in the normalization. It's not after v7-0003.
Already mentioned upthread, but applying only v7-0003 on top of
v7-0002 (not v7-0004) leads to various regression failures in dml.sql
and squashing.sql. The failures persist with v7-0004 applied. Please
see these as per the attached, the IN lists do not get squashed, the
array elements are. Just to make sure that I am not missing
something, I've rebuilt from scratch with no success.
IsSquashableExpressionList() includes this comment, which is outdated,
probably because squashing was originally optional behind a GUC and
the parameter has been removed while the comment has not been
refreshed:
/*
* If squashing is disabled, or the list is too short, we don't try to
* squash it.
*/
RecordExpressionLocation()'s top comment needs a refresh, talking
about constants. The simplifications gained in pgss.c's normalization
are pretty cool.
+ bool has_squashed_lists;
[...]
+ if (jstate->has_squashed_lists)
+ jstate->highest_extern_param_id = 0;
This new flag in JumbleState needs to be documented, explaining why
it needs to be here. I have to admit that it is strange to see
highest_extern_param_id, one value in JumbleState be forced to zero in
the PGSS normalization code if has_squashed_lists is set to true.
This seems like a layer violation to me: JumbleState should only be
set while in the jumbling code, not forced to something else
afterwards while in the extension.
--
Michael
diff -u /home/user/git/postgres/contrib/pg_stat_statements/expected/dml.out
/home/user/git/postgres/contrib/pg_stat_statements/results/dml.out
--- /home/user/git/postgres/contrib/pg_stat_statements/expected/dml.out
2025-05-29 10:49:32.394190162 +0900
+++ /home/user/git/postgres/contrib/pg_stat_statements/results/dml.out
2025-05-30 13:34:54.742674059 +0900
@@ -80,7 +80,7 @@
1 | 10 | INSERT INTO pgss_dml_tab VALUES(generate_series($1, $2), $3)
1 | 12 | SELECT * FROM pgss_dml_tab ORDER BY a
2 | 4 | SELECT * FROM pgss_dml_tab WHERE a > $1 ORDER BY a
- 1 | 8 | SELECT * FROM pgss_dml_tab WHERE a IN ($1 /*, ... */)
+ 1 | 8 | SELECT * FROM pgss_dml_tab WHERE a IN ($1, $2, $3, $4, $5)
1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
1 | 0 | SET pg_stat_statements.track_utility = $1
6 | 6 | UPDATE pgss_dml_tab SET b = $1 WHERE a = $2
diff -u
/home/user/git/postgres/contrib/pg_stat_statements/expected/squashing.out
/home/user/git/postgres/contrib/pg_stat_statements/results/squashing.out
--- /home/user/git/postgres/contrib/pg_stat_statements/expected/squashing.out
2025-05-30 13:30:39.208783894 +0900
+++ /home/user/git/postgres/contrib/pg_stat_statements/results/squashing.out
2025-05-30 13:34:55.710681261 +0900
@@ -73,12 +73,14 @@
(1 row)
SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
- query | calls
--------------------------------------------------------+-------
- SELECT * FROM test_squash WHERE id IN ($1 /*, ... */) | 3
- SELECT ARRAY[$1 /*, ... */] | 3
- SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
-(3 rows)
+ query | calls
+------------------------------------------------------------+-------
+ SELECT * FROM test_squash WHERE id IN ($1, $2, $3) | 1
+ SELECT * FROM test_squash WHERE id IN ($1, $2, $3, $4) | 1
+ SELECT * FROM test_squash WHERE id IN ($1, $2, $3, $4, $5) | 1
+ SELECT ARRAY[$1 /*, ... */] | 3
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
+(5 rows)
-- built-in functions will be squashed
-- the IN and ARRAY forms of this statement will have the same queryId
@@ -99,9 +101,10 @@
SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
query | calls
----------------------------------------------------+-------
- SELECT WHERE $1 IN ($2 /*, ... */) | 2
+ SELECT WHERE $1 = ANY (ARRAY[$2 /*, ... */]) | 1
+ SELECT WHERE $1 IN ($2, int4($3), int4($4), $5) | 1
SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
-(2 rows)
+(3 rows)
-- external parameters will not be squashed
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
@@ -200,11 +203,14 @@
(0 rows)
SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
- query | calls
----------------------------------------------------------------------+-------
- SELECT * FROM test_squash WHERE id IN ($1 /*, ... */) AND data = $2 | 6
- SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
-(2 rows)
+ query
| calls
+-----------------------------------------------------------------------------------------------------+-------
+ SELECT * FROM test_squash WHERE id = ANY (ARRAY[$1 /*, ... */]) AND data = $2
| 3
+ SELECT * FROM test_squash WHERE id IN ($1, $2, $3, $4, $5, $6, $7, $8, $9)
AND data = $10 | 1
+ SELECT * FROM test_squash WHERE id IN ($1, $2, $3, $4, $5, $6, $7, $8, $9,
$10) AND data = $11 | 1
+ SELECT * FROM test_squash WHERE id IN ($1, $2, $3, $4, $5, $6, $7, $8, $9,
$10, $11) AND data = $12 | 1
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t
| 1
+(5 rows)
-- Multiple squashed intervals
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
@@ -250,12 +256,18 @@
(0 rows)
SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
- query | calls
--------------------------------------------------------+-------
- SELECT * FROM test_squash WHERE id IN ($1 /*, ... */)+| 6
- AND data IN ($2 /*, ... */) |
- SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
-(2 rows)
+ query
| calls
+--------------------------------------------------------------------------------------+-------
+ SELECT * FROM test_squash WHERE id = ANY (ARRAY[$1 /*, ... */])
+| 3
+ AND data = ANY (ARRAY[$2 /*, ... */])
|
+ SELECT * FROM test_squash WHERE id IN ($1, $2, $3, $4, $5, $6, $7, $8, $9)
+| 1
+ AND data IN ($10, $11, $12, $13, $14, $15, $16, $17, $18)
|
+ SELECT * FROM test_squash WHERE id IN ($1, $2, $3, $4, $5, $6, $7, $8, $9,
$10) +| 1
+ AND data IN ($11, $12, $13, $14, $15, $16, $17, $18, $19, $20)
|
+ SELECT * FROM test_squash WHERE id IN ($1, $2, $3, $4, $5, $6, $7, $8, $9,
$10, $11)+| 1
+ AND data IN ($12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22)
|
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t
| 1
+(5 rows)
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
t
@@ -372,9 +384,14 @@
query | calls
--------------------------------------------------------------------+-------
SELECT data FROM test_float WHERE data = ANY(ARRAY[$1 /*, ... */]) | 3
- SELECT data FROM test_float WHERE data IN ($1 /*, ... */) | 7
+ SELECT data FROM test_float WHERE data = ANY(ARRAY[$1 /*, ... */]) | 2
+ SELECT data FROM test_float WHERE data IN ($1, $2) | 1
+ SELECT data FROM test_float WHERE data IN ($1, $2) | 1
+ SELECT data FROM test_float WHERE data IN ($1, $2) | 1
+ SELECT data FROM test_float WHERE data IN ($1, $2) | 1
+ SELECT data FROM test_float WHERE data IN ($1, $2) | 1
SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
-(3 rows)
+(8 rows)
-- Numeric type, implicit cast is squashed
CREATE TABLE test_squash_numeric (id int, data numeric(5, 2));
@@ -395,11 +412,11 @@
(0 rows)
SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
- query |
calls
---------------------------------------------------------------------------+-------
- SELECT * FROM test_squash_numeric WHERE data = ANY(ARRAY[$1 /*, ... */]) |
1
- SELECT * FROM test_squash_numeric WHERE data IN ($1 /*, ... */) |
1
- SELECT pg_stat_statements_reset() IS NOT NULL AS t |
1
+ query
| calls
+------------------------------------------------------------------------------------------------+-------
+ SELECT * FROM test_squash_numeric WHERE data = ANY(ARRAY[$1 /*, ... */])
| 1
+ SELECT * FROM test_squash_numeric WHERE data IN ($1, $2, $3, $4, $5, $6, $7,
$8, $9, $10, $11) | 1
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t
| 1
(3 rows)
-- Bigint, implicit cast is squashed
@@ -421,11 +438,11 @@
(0 rows)
SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
- query |
calls
--------------------------------------------------------------------------+-------
- SELECT * FROM test_squash_bigint WHERE data = ANY(ARRAY[$1 /*, ... */]) |
1
- SELECT * FROM test_squash_bigint WHERE data IN ($1 /*, ... */) |
1
- SELECT pg_stat_statements_reset() IS NOT NULL AS t |
1
+ query
| calls
+-----------------------------------------------------------------------------------------------+-------
+ SELECT * FROM test_squash_bigint WHERE data = ANY(ARRAY[$1 /*, ... */])
| 1
+ SELECT * FROM test_squash_bigint WHERE data IN ($1, $2, $3, $4, $5, $6, $7,
$8, $9, $10, $11) | 1
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t
| 1
(3 rows)
-- Bigint, explicit cast is squashed
@@ -450,12 +467,14 @@
(0 rows)
SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
- query | calls
-----------------------------------------------------+-------
- SELECT * FROM test_squash_bigint WHERE data IN +| 2
- ($1 /*, ... */) |
- SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
-(2 rows)
+ query
| calls
+----------------------------------------------------------------------------------+-------
+ SELECT * FROM test_squash_bigint WHERE data = ANY(ARRAY[$1 /*, ... */])
| 1
+ SELECT * FROM test_squash_bigint WHERE data IN
+| 1
+ ($1::bigint, $2::bigint, $3::bigint, $4::bigint, $5::bigint,
$6::bigint,+|
+ $7::bigint, $8::bigint, $9::bigint, $10::bigint, $11::bigint)
|
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t
| 1
+(3 rows)
-- Bigint, long tokens with parenthesis, will not squash
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
@@ -569,12 +588,17 @@
(0 rows)
SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
- query | calls
-----------------------------------------------------+-------
- SELECT * FROM test_squash_cast WHERE data IN +| 2
- ($1 /*, ... */) |
- SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
-(2 rows)
+ query
| calls
+----------------------------------------------------------------------------------+-------
+ SELECT * FROM test_squash_cast WHERE data = ANY (ARRAY
+| 1
+ [$1 /*, ... */])
|
+ SELECT * FROM test_squash_cast WHERE data IN
+| 1
+ ($1::int4::casttesttype, $2::int4::casttesttype,
$3::int4::casttesttype,+|
+ $4::int4::casttesttype, $5::int4::casttesttype,
$6::int4::casttesttype,+|
+ $7::int4::casttesttype, $8::int4::casttesttype,
$9::int4::casttesttype,+|
+ $10::int4::casttesttype, $11::int4::casttesttype)
|
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t
| 1
+(3 rows)
-- Some casting expression are simplified to Const
CREATE TABLE test_squash_jsonb (id int, data jsonb);
@@ -601,12 +625,16 @@
(0 rows)
SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
- query | calls
-----------------------------------------------------+-------
- SELECT * FROM test_squash_jsonb WHERE data IN +| 2
- ($1 /*, ... */) |
- SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
-(2 rows)
+ query | calls
+--------------------------------------------------------------+-------
+ SELECT * FROM test_squash_jsonb WHERE data = ANY (ARRAY +| 1
+ [$1 /*, ... */]) |
+ SELECT * FROM test_squash_jsonb WHERE data IN +| 1
+ (($1)::jsonb, ($2)::jsonb, ($3)::jsonb, ($4)::jsonb,+|
+ ($5)::jsonb, ($6)::jsonb, ($7)::jsonb, ($8)::jsonb,+|
+ ($9)::jsonb, ($10)::jsonb) |
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
+(3 rows)
-- CoerceViaIO, SubLink instead of a Const. Will not squash
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
@@ -701,13 +729,13 @@
(0 rows)
SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
- query | calls
---------------------------------------------------------------------+-------
- SELECT * FROM test_squash WHERE id IN +| 1
- ($1 /*, ... */) |
- SELECT * FROM test_squash WHERE id IN ($1::oid, $2::oid::int::oid) | 2
- SELECT ARRAY[$1 /*, ... */] | 1
- SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
+ query
| calls
+-------------------------------------------------------------------------------------------+-------
+ SELECT * FROM test_squash WHERE id IN
+| 1
+ ($1::oid, $2::oid, $3::oid, $4::oid, $5::oid, $6::oid, $7::oid,
$8::oid, $9::oid) |
+ SELECT * FROM test_squash WHERE id IN ($1::oid, $2::oid::int::oid)
| 2
+ SELECT ARRAY[$1 /*, ... */]
| 1
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t
| 1
(4 rows)
--
@@ -773,7 +801,7 @@
query | calls
----------------------------------------------------+-------
SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
- select where $1 IN ($2 /*, ... */) | 1
+ select where $1 IN ($2::int, $3::int) | 1
select where $1 IN ($2::int, $3::int::text) | 1
(3 rows)
@@ -797,8 +825,9 @@
query | calls
----------------------------------------------------+-------
SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
- select where $1 IN ($2 /*, ... */) | 2
-(2 rows)
+ select where $1 = ANY (array[$2 /*, ... */]) | 1
+ select where $1 IN ($2::int::text, $3::int::text) | 1
+(3 rows)
--
-- cleanup
signature.asc
Description: PGP signature
