path: root/contrib/pg_stat_statements/expected/squashing.out

--
-- Const squashing functionality
--
CREATE EXTENSION pg_stat_statements;
CREATE TABLE test_squash (id int, data int);
-- IN queries
-- Normal scenario, too many simple constants for an IN query
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 * FROM test_squash WHERE id IN (1, 2, 3);
 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 IN ($1)            |     1
 SELECT pg_stat_statements_reset() IS NOT NULL AS t    |     1
(3 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 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);
 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 /*, ... */)                  |     4
 SELECT * FROM test_squash WHERE id IN ($1)                             |     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)

-- 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 query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
                                query                                | calls 
---------------------------------------------------------------------+-------
 SELECT * FROM test_squash WHERE id IN ($1 /*, ... */) AND data = $2 |     3
 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 query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
                         query                         | calls 
-------------------------------------------------------+-------
 SELECT * FROM test_squash WHERE id IN ($1 /*, ... */)+|     3
     AND data IN ($2 /*, ... */)                       | 
 SELECT pg_stat_statements_reset() IS NOT NULL AS t    |     1
(2 rows)

-- No constants simplification for OpExpr
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
 t 
---
 t
(1 row)

-- 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
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 query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
                                               query                                                | calls 
----------------------------------------------------------------------------------------------------+-------
 SELECT * FROM test_squash WHERE id IN                                                             +|     1
         ($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                                                             +|     1
         (@ $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);
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 query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
                           query                           | calls 
-----------------------------------------------------------+-------
 SELECT data FROM test_float WHERE data IN ($1 /*, ... */) |     5
 SELECT pg_stat_statements_reset() IS NOT NULL AS t        |     1
(2 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 query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
                              query                              | calls 
-----------------------------------------------------------------+-------
 SELECT * FROM test_squash_numeric WHERE data IN ($1 /*, ... */) |     1
 SELECT pg_stat_statements_reset() IS NOT NULL AS t              |     1
(2 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 query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
                             query                              | calls 
----------------------------------------------------------------+-------
 SELECT * FROM test_squash_bigint WHERE data IN ($1 /*, ... */) |     1
 SELECT pg_stat_statements_reset() IS NOT NULL AS t             |     1
(2 rows)

-- Bigint, explicit cast is not 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 query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
                       query                        | calls 
----------------------------------------------------+-------
 SELECT * FROM test_squash_bigint WHERE data IN    +|     1
         ($1 /*, ... */::bigint)                    | 
 SELECT pg_stat_statements_reset() IS NOT NULL AS t |     1
(2 rows)

-- Bigint, long tokens with parenthesis
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 query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
                                  query                                  | calls 
-------------------------------------------------------------------------+-------
 SELECT * FROM test_squash_bigint WHERE id IN                           +|     1
         (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)

-- CoerceViaIO, SubLink instead of a 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
	((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                       +|     1
         ((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)

-- 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 query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
                       query                        | calls 
----------------------------------------------------+-------
 SELECT * FROM test_squash_cast WHERE data IN      +|     1
         ($1 /*, ... */::int4::casttesttype)        | 
 SELECT pg_stat_statements_reset() IS NOT NULL AS t |     1
(2 rows)

-- Some casting expression are simplified to Const
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 query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
                       query                        | calls 
----------------------------------------------------+-------
 SELECT * FROM test_squash_jsonb WHERE data IN     +|     1
         (($1 /*, ... */)::jsonb)                   | 
 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)

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 query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
                           query                            | calls 
------------------------------------------------------------+-------
 SELECT * FROM test_squash WHERE id IN ($1 /*, ... */::oid) |     1
 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)

-- Simple array would be squashed as well
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
 t 
---
 t
(1 row)

SELECT ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10];
         array          
------------------------
 {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 /*, ... */]                        |     1
 SELECT pg_stat_statements_reset() IS NOT NULL AS t |     1
(2 rows)