Spracherkennung für: .out vermutete Sprache: Unknown {[0] [0] [0]} [Methode: Schwerpunktbildung, einfache Gewichte, sechs Dimensionen]
--
-- Const squashing functionality
--
CREATE EXTENSION pg_stat_statements;
--
-- Simple Lists
--
CREATE TABLE test_squash (id int, data int);
-- single element will not be squashed
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
t
---
t
(1 row)
SELECT * FROM test_squash WHERE id IN (1);
id | data
----+------
(0 rows)
SELECT ARRAY[1];
array
-------
{1}
(1 row)
SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
query | calls
----------------------------------------------------+-------
SELECT * FROM test_squash WHERE id IN ($1) | 1
SELECT ARRAY[$1] | 1
SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
(3 rows)
-- more than 1 element in a list will be squashed
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
t
---
t
(1 row)
SELECT * FROM test_squash WHERE id IN (1, 2, 3);
id | data
----+------
(0 rows)
SELECT * FROM test_squash WHERE id IN (1, 2, 3, 4);
id | data
----+------
(0 rows)
SELECT * FROM test_squash WHERE id IN (1, 2, 3, 4, 5);
id | data
----+------
(0 rows)
SELECT ARRAY[1, 2, 3];
array
---------
{1,2,3}
(1 row)
SELECT ARRAY[1, 2, 3, 4];
array
-----------
{1,2,3,4}
(1 row)
SELECT ARRAY[1, 2, 3, 4, 5];
array
-------------
{1,2,3,4,5}
(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)
-- built-in functions will be squashed
-- the IN and ARRAY forms of this statement will have the same queryId
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
t
---
t
(1 row)
SELECT WHERE 1 IN (1, int4(1), int4(2), 2);
--
(1 row)
SELECT WHERE 1 = ANY (ARRAY[1, int4(1), int4(2), 2]);
--
(1 row)
SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
query | calls
----------------------------------------------------+-------
SELECT WHERE $1 IN ($2 /*, ... */) | 2
SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
(2 rows)
-- external parameters will be squashed
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
t
---
t
(1 row)
SELECT * FROM test_squash WHERE id IN ($1, $2, $3, $4, $5) \bind 1 2 3 4 5
;
id | data
----+------
(0 rows)
SELECT * FROM test_squash WHERE id::text = ANY(ARRAY[$1, $2, $3, $4, $5]) \bind 1 2 3 4 5
;
id | data
----+------
(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::text = ANY(ARRAY[$1 /*, ... */]) | 1
SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
(3 rows)
-- prepared statements will also be squashed
-- the IN and ARRAY forms of this statement will have the same queryId
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
t
---
t
(1 row)
PREPARE p1(int, int, int, int, int) AS
SELECT * FROM test_squash WHERE id IN ($1, $2, $3, $4, $5);
EXECUTE p1(1, 2, 3, 4, 5);
id | data
----+------
(0 rows)
DEALLOCATE p1;
PREPARE p1(int, int, int, int, int) AS
SELECT * FROM test_squash WHERE id = ANY(ARRAY[$1, $2, $3, $4, $5]);
EXECUTE p1(1, 2, 3, 4, 5);
id | data
----+------
(0 rows)
DEALLOCATE p1;
SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
query | calls
-------------------------------------------------------+-------
DEALLOCATE $1 | 2
PREPARE p1(int, int, int, int, int) AS +| 2
SELECT * FROM test_squash WHERE id IN ($1 /*, ... */) |
SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
(3 rows)
-- More conditions in the query
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
t
---
t
(1 row)
SELECT * FROM test_squash WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9) AND data = 2;
id | data
----+------
(0 rows)
SELECT * FROM test_squash WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10) AND data = 2;
id | data
----+------
(0 rows)
SELECT * FROM test_squash WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11) AND data = 2;
id | data
----+------
(0 rows)
SELECT * FROM test_squash WHERE id = ANY (ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9]) AND data = 2;
id | data
----+------
(0 rows)
SELECT * FROM test_squash WHERE id = ANY (ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10]) AND data = 2;
id | data
----+------
(0 rows)
SELECT * FROM test_squash WHERE id = ANY (ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11]) AND data = 2;
id | data
----+------
(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)
-- Multiple squashed intervals
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
t
---
t
(1 row)
SELECT * FROM test_squash WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9)
AND data IN (1, 2, 3, 4, 5, 6, 7, 8, 9);
id | data
----+------
(0 rows)
SELECT * FROM test_squash WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
AND data IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10);
id | data
----+------
(0 rows)
SELECT * FROM test_squash WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11)
AND data IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11);
id | data
----+------
(0 rows)
SELECT * FROM test_squash WHERE id = ANY (ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9])
AND data = ANY (ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9]);
id | data
----+------
(0 rows)
SELECT * FROM test_squash WHERE id = ANY (ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10])
AND data = ANY (ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10]);
id | data
----+------
(0 rows)
SELECT * FROM test_squash WHERE id = ANY (ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11])
AND data = ANY (ARRAY[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_squash WHERE id IN ($1 /*, ... */)+| 6
AND data IN ($2 /*, ... */) |
SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
(2 rows)
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
t
---
t
(1 row)
-- No constants squashing for OpExpr
-- The IN and ARRAY forms of this statement will have the same queryId
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
t
---
t
(1 row)
SELECT * FROM test_squash WHERE id IN
(1 + 1, 2 + 2, 3 + 3, 4 + 4, 5 + 5, 6 + 6, 7 + 7, 8 + 8, 9 + 9);
id | data
----+------
(0 rows)
SELECT * FROM test_squash WHERE id IN
(@ '-1', @ '-2', @ '-3', @ '-4', @ '-5', @ '-6', @ '-7', @ '-8', @ '-9');
id | data
----+------
(0 rows)
SELECT * FROM test_squash WHERE id = ANY(ARRAY
[1 + 1, 2 + 2, 3 + 3, 4 + 4, 5 + 5, 6 + 6, 7 + 7, 8 + 8, 9 + 9]);
id | data
----+------
(0 rows)
SELECT * FROM test_squash WHERE id = ANY(ARRAY
[@ '-1', @ '-2', @ '-3', @ '-4', @ '-5', @ '-6', @ '-7', @ '-8', @ '-9']);
id | data
----+------
(0 rows)
SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
query | calls
----------------------------------------------------------------------------------------------------+-------
SELECT * FROM test_squash WHERE id IN +| 2
($1 + $2, $3 + $4, $5 + $6, $7 + $8, $9 + $10, $11 + $12, $13 + $14, $15 + $16, $17 + $18) |
SELECT * FROM test_squash WHERE id IN +| 2
(@ $1, @ $2, @ $3, @ $4, @ $5, @ $6, @ $7, @ $8, @ $9) |
SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
(3 rows)
--
-- FuncExpr
--
-- Verify multiple type representation end up with the same query_id
CREATE TABLE test_float (data float);
-- The casted ARRAY expressions will have the same queryId as the IN clause
-- form of the query
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
t
---
t
(1 row)
SELECT data FROM test_float WHERE data IN (1, 2);
data
------
(0 rows)
SELECT data FROM test_float WHERE data IN (1, '2');
data
------
(0 rows)
SELECT data FROM test_float WHERE data IN ('1', 2);
data
------
(0 rows)
SELECT data FROM test_float WHERE data IN ('1', '2');
data
------
(0 rows)
SELECT data FROM test_float WHERE data IN (1.0, 1.0);
data
------
(0 rows)
SELECT data FROM test_float WHERE data = ANY(ARRAY['1'::double precision, '2'::double precision]);
data
------
(0 rows)
SELECT data FROM test_float WHERE data = ANY(ARRAY[1.0::double precision, 1.0::double precision]);
data
------
(0 rows)
SELECT data FROM test_float WHERE data = ANY(ARRAY[1, 2]);
data
------
(0 rows)
SELECT data FROM test_float WHERE data = ANY(ARRAY[1, '2']);
data
------
(0 rows)
SELECT data FROM test_float WHERE data = ANY(ARRAY['1', 2]);
data
------
(0 rows)
SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
query | calls
--------------------------------------------------------------------+-------
SELECT data FROM test_float WHERE data = ANY(ARRAY[$1 /*, ... */]) | 3
SELECT data FROM test_float WHERE data IN ($1 /*, ... */) | 7
SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
(3 rows)
-- Numeric type, implicit cast is squashed
CREATE TABLE test_squash_numeric (id int, data numeric(5, 2));
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
t
---
t
(1 row)
SELECT * FROM test_squash_numeric WHERE data IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11);
id | data
----+------
(0 rows)
SELECT * FROM test_squash_numeric WHERE data = ANY(ARRAY[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_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
(3 rows)
-- Bigint, implicit cast is squashed
CREATE TABLE test_squash_bigint (id int, data bigint);
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
t
---
t
(1 row)
SELECT * FROM test_squash_bigint WHERE data IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11);
id | data
----+------
(0 rows)
SELECT * FROM test_squash_bigint WHERE data = ANY(ARRAY[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_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
(3 rows)
-- Bigint, explicit cast is squashed
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
t
---
t
(1 row)
SELECT * FROM test_squash_bigint WHERE data IN
(1::bigint, 2::bigint, 3::bigint, 4::bigint, 5::bigint, 6::bigint,
7::bigint, 8::bigint, 9::bigint, 10::bigint, 11::bigint);
id | data
----+------
(0 rows)
SELECT * FROM test_squash_bigint WHERE data = ANY(ARRAY[
1::bigint, 2::bigint, 3::bigint, 4::bigint, 5::bigint, 6::bigint,
7::bigint, 8::bigint, 9::bigint, 10::bigint, 11::bigint]);
id | data
----+------
(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)
-- Bigint, long tokens with parenthesis, will not squash
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
t
---
t
(1 row)
SELECT * FROM test_squash_bigint WHERE id IN
(abs(100), abs(200), abs(300), abs(400), abs(500), abs(600), abs(700),
abs(800), abs(900), abs(1000), ((abs(1100))));
id | data
----+------
(0 rows)
SELECT * FROM test_squash_bigint WHERE id = ANY(ARRAY[
abs(100), abs(200), abs(300), abs(400), abs(500), abs(600), abs(700),
abs(800), abs(900), abs(1000), ((abs(1100)))]);
id | data
----+------
(0 rows)
SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
query | calls
-------------------------------------------------------------------------+-------
SELECT * FROM test_squash_bigint WHERE id IN +| 2
(abs($1), abs($2), abs($3), abs($4), abs($5), abs($6), abs($7),+|
abs($8), abs($9), abs($10), ((abs($11)))) |
SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
(2 rows)
-- Multiple FuncExpr's. Will not squash
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
t
---
t
(1 row)
SELECT WHERE 1 IN (1::int::bigint::int, 2::int::bigint::int);
--
(1 row)
SELECT WHERE 1 = ANY(ARRAY[1::int::bigint::int, 2::int::bigint::int]);
--
(1 row)
SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
query | calls
----------------------------------------------------+-------
SELECT WHERE $1 IN ($2 /*, ... */) | 2
SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
(2 rows)
--
-- CoerceViaIO
--
-- Create some dummy type to force CoerceViaIO
CREATE TYPE casttesttype;
CREATE FUNCTION casttesttype_in(cstring)
RETURNS casttesttype
AS 'textin'
LANGUAGE internal STRICT IMMUTABLE;
NOTICE: return type casttesttype is only a shell
CREATE FUNCTION casttesttype_out(casttesttype)
RETURNS cstring
AS 'textout'
LANGUAGE internal STRICT IMMUTABLE;
NOTICE: argument type casttesttype is only a shell
LINE 1: CREATE FUNCTION casttesttype_out(casttesttype)
^
CREATE TYPE casttesttype (
internallength = variable,
input = casttesttype_in,
output = casttesttype_out,
alignment = int4
);
CREATE CAST (int4 AS casttesttype) WITH INOUT;
CREATE FUNCTION casttesttype_eq(casttesttype, casttesttype)
returns boolean language sql immutable as $$
SELECT true
$$;
CREATE OPERATOR = (
leftarg = casttesttype,
rightarg = casttesttype,
procedure = casttesttype_eq,
commutator = =);
CREATE TABLE test_squash_cast (id int, data casttesttype);
-- Use the introduced type to construct a list of CoerceViaIO around Const
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
t
---
t
(1 row)
SELECT * FROM test_squash_cast WHERE data IN
(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);
id | data
----+------
(0 rows)
SELECT * FROM test_squash_cast WHERE data = ANY (ARRAY
[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]);
id | data
----+------
(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)
-- Some casting expression are simplified to Const
CREATE TABLE test_squash_jsonb (id int, data jsonb);
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
t
---
t
(1 row)
SELECT * FROM test_squash_jsonb WHERE data IN
(('"1"')::jsonb, ('"2"')::jsonb, ('"3"')::jsonb, ('"4"')::jsonb,
('"5"')::jsonb, ('"6"')::jsonb, ('"7"')::jsonb, ('"8"')::jsonb,
('"9"')::jsonb, ('"10"')::jsonb);
id | data
----+------
(0 rows)
SELECT * FROM test_squash_jsonb WHERE data = ANY (ARRAY
[('"1"')::jsonb, ('"2"')::jsonb, ('"3"')::jsonb, ('"4"')::jsonb,
('"5"')::jsonb, ('"6"')::jsonb, ('"7"')::jsonb, ('"8"')::jsonb,
('"9"')::jsonb, ('"10"')::jsonb]);
id | data
----+------
(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)
-- CoerceViaIO, SubLink instead of a Const. Will not squash
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
t
---
t
(1 row)
SELECT * FROM test_squash_jsonb WHERE data IN
((SELECT '"1"')::jsonb, (SELECT '"2"')::jsonb, (SELECT '"3"')::jsonb,
(SELECT '"4"')::jsonb, (SELECT '"5"')::jsonb, (SELECT '"6"')::jsonb,
(SELECT '"7"')::jsonb, (SELECT '"8"')::jsonb, (SELECT '"9"')::jsonb,
(SELECT '"10"')::jsonb);
id | data
----+------
(0 rows)
SELECT * FROM test_squash_jsonb WHERE data = ANY(ARRAY
[(SELECT '"1"')::jsonb, (SELECT '"2"')::jsonb, (SELECT '"3"')::jsonb,
(SELECT '"4"')::jsonb, (SELECT '"5"')::jsonb, (SELECT '"6"')::jsonb,
(SELECT '"7"')::jsonb, (SELECT '"8"')::jsonb, (SELECT '"9"')::jsonb,
(SELECT '"10"')::jsonb]);
id | data
----+------
(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
((SELECT $1)::jsonb, (SELECT $2)::jsonb, (SELECT $3)::jsonb,+|
(SELECT $4)::jsonb, (SELECT $5)::jsonb, (SELECT $6)::jsonb,+|
(SELECT $7)::jsonb, (SELECT $8)::jsonb, (SELECT $9)::jsonb,+|
(SELECT $10)::jsonb) |
SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
(2 rows)
-- Multiple CoerceViaIO are squashed
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
t
---
t
(1 row)
SELECT WHERE 1 IN (1::text::int::text::int, 1::text::int::text::int);
--
(1 row)
SELECT WHERE 1 = ANY(ARRAY[1::text::int::text::int, 1::text::int::text::int]);
--
(1 row)
SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
query | calls
----------------------------------------------------+-------
SELECT WHERE $1 IN ($2 /*, ... */) | 2
SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
(2 rows)
--
-- RelabelType
--
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
t
---
t
(1 row)
-- However many layers of RelabelType there are, the list will be squashable.
SELECT * FROM test_squash WHERE id IN
(1::oid, 2::oid, 3::oid, 4::oid, 5::oid, 6::oid, 7::oid, 8::oid, 9::oid);
id | data
----+------
(0 rows)
SELECT ARRAY[1::oid, 2::oid, 3::oid, 4::oid, 5::oid, 6::oid, 7::oid, 8::oid, 9::oid];
array
---------------------
{1,2,3,4,5,6,7,8,9}
(1 row)
SELECT * FROM test_squash WHERE id IN (1::oid, 2::oid::int::oid);
id | data
----+------
(0 rows)
SELECT * FROM test_squash WHERE id = ANY(ARRAY[1::oid, 2::oid::int::oid]);
id | data
----+------
(0 rows)
-- RelabelType together with CoerceViaIO is also squashable
SELECT * FROM test_squash WHERE id = ANY(ARRAY[1::oid::text::int::oid, 2::oid::int::oid]);
id | data
----+------
(0 rows)
SELECT * FROM test_squash WHERE id = ANY(ARRAY[1::text::int::oid, 2::oid::int::oid]);
id | data
----+------
(0 rows)
SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
query | calls
----------------------------------------------------+-------
SELECT * FROM test_squash WHERE id IN +| 5
($1 /*, ... */) |
SELECT ARRAY[$1 /*, ... */] | 1
SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
(3 rows)
--
-- edge cases
--
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
t
---
t
(1 row)
-- for nested arrays, only constants are squashed
SELECT ARRAY[
ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10],
ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10],
ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10],
ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10]
];
array
-----------------------------------------------------------------------------------------------
{{1,2,3,4,5,6,7,8,9,10},{1,2,3,4,5,6,7,8,9,10},{1,2,3,4,5,6,7,8,9,10},{1,2,3,4,5,6,7,8,9,10}}
(1 row)
SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
query | calls
----------------------------------------------------+-------
SELECT ARRAY[ +| 1
ARRAY[$1 /*, ... */], +|
ARRAY[$2 /*, ... */], +|
ARRAY[$3 /*, ... */], +|
ARRAY[$4 /*, ... */] +|
] |
SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
(2 rows)
-- Test constants evaluation in a CTE, which was causing issues in the past
WITH cte AS (
SELECT 'const' as const FROM test_squash
)
SELECT ARRAY['a', 'b', 'c', const::varchar] AS result
FROM cte;
result
--------
(0 rows)
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
t
---
t
(1 row)
-- Rewritten as an OpExpr, so it will not be squashed
select where '1' IN ('1'::int, '2'::int::text);
--
(1 row)
-- Rewritten as an ArrayExpr, so it will be squashed
select where '1' IN ('1'::int, '2'::int);
--
(1 row)
SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
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::text) | 1
(3 rows)
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
t
---
t
(1 row)
-- Both of these queries will be rewritten as an ArrayExpr, so they
-- will be squashed, and have a similar queryId
select where '1' IN ('1'::int::text, '2'::int::text);
--
(1 row)
select where '1' = ANY (array['1'::int::text, '2'::int::text]);
--
(1 row)
SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
query | calls
----------------------------------------------------+-------
SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
select where $1 IN ($2 /*, ... */) | 2
(2 rows)
-- composite function with row expansion
create table test_composite(x integer);
CREATE FUNCTION composite_f(a integer[], out x integer, out y integer) returns
record as $$ begin
x = a[1];
y = a[2];
end;
$$ language plpgsql;
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
t
---
t
(1 row)
SELECT ((composite_f(array[1, 2]))).* FROM test_composite;
x | y
---+---
(0 rows)
SELECT ((composite_f(array[1, 2, 3]))).* FROM test_composite;
x | y
---+---
(0 rows)
SELECT ((composite_f(array[1, 2, 3]))).*, 1, 2, 3, ((composite_f(array[1, 2, 3]))).*, 1, 2
FROM test_composite
WHERE x IN (1, 2, 3);
x | y | ?column? | ?column? | ?column? | x | y | ?column? | ?column?
---+---+----------+----------+----------+---+---+----------+----------
(0 rows)
SELECT ((composite_f(array[1, $1, 3]))).*, 1 FROM test_composite \bind 1
;
x | y | ?column?
---+---+----------
(0 rows)
-- ROW() expression with row expansion
SELECT (ROW(ARRAY[1,2])).*;
f1
-------
{1,2}
(1 row)
SELECT (ROW(ARRAY[1, 2], ARRAY[1, 2, 3])).*;
f1 | f2
-------+---------
{1,2} | {1,2,3}
(1 row)
SELECT 1, 2, (ROW(ARRAY[1, 2], ARRAY[1, 2, 3])).*, 3, 4;
?column? | ?column? | f1 | f2 | ?column? | ?column?
----------+----------+-------+---------+----------+----------
1 | 2 | {1,2} | {1,2,3} | 3 | 4
(1 row)
SELECT (ROW(ARRAY[1, 2], ARRAY[1, $1, 3])).*, 1 \bind 1
;
f1 | f2 | ?column?
-------+---------+----------
{1,2} | {1,1,3} | 1
(1 row)
-- IN and ANY clauses with Vars are not squashed.
SELECT * FROM test_squash a, test_squash b WHERE a.id IN (1, 2, 3, b.id, b.id + 1);
id | data | id | data
----+------+----+------
(0 rows)
SELECT * FROM test_squash a, test_squash b WHERE a.id = ANY (array[1, ((b.id + b.id * 2)), 5]);
id | data | id | data
----+------+----+------
(0 rows)
SELECT * FROM test_squash a, test_squash b WHERE a.id IN ($1, $2, $3, b.id, b.id + $4) \bind 1 2 3 1
;
id | data | id | data
----+------+----+------
(0 rows)
SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
query | calls
-------------------------------------------------------------------------------------------------------------+-------
SELECT $1, $2, (ROW(ARRAY[$3 /*, ... */], ARRAY[$4 /*, ... */])).*, $5, $6 | 1
SELECT ((composite_f(array[$1 /*, ... */]))).* FROM test_composite | 2
SELECT ((composite_f(array[$1 /*, ... */]))).*, $2 FROM test_composite | 1
SELECT ((composite_f(array[$1 /*, ... */]))).*, $2, $3, $4, ((composite_f(array[$5 /*, ... */]))).*, $6, $7+| 1
FROM test_composite +|
WHERE x IN ($8 /*, ... */) |
SELECT (ROW(ARRAY[$1 /*, ... */])).* | 1
SELECT (ROW(ARRAY[$1 /*, ... */], ARRAY[$2 /*, ... */])).* | 1
SELECT (ROW(ARRAY[$1 /*, ... */], ARRAY[$2 /*, ... */])).*, $3 | 1
SELECT * FROM test_squash a, test_squash b WHERE a.id = ANY (array[$1, ((b.id + b.id * $2)), $3]) | 1
SELECT * FROM test_squash a, test_squash b WHERE a.id IN ($1, $2, $3, b.id, b.id + $4) | 1
SELECT * FROM test_squash a, test_squash b WHERE a.id IN ($1, $2, $3, b.id, b.id + $4) | 1
SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
(11 rows)
--
-- cleanup
--
DROP TABLE test_squash;
DROP TABLE test_float;
DROP TABLE test_squash_numeric;
DROP TABLE test_squash_bigint;
DROP TABLE test_squash_cast CASCADE;
DROP TABLE test_squash_jsonb;
DROP TABLE test_composite;
DROP FUNCTION composite_f;
