queryId constant squashing does not support prepared statements

Sami Imseih <samimseih@gmail.com>

From: Sami Imseih <samimseih@gmail.com>
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2025-04-30T21:52:06Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →
  1. Fix typo in comment

  2. Make query jumbling also squash PARAM_EXTERN params

  3. Fix squashing algorithm for query texts

  4. pg_stat_statements: Fix parameter number gaps in normalized queries

Attachments

62d712ec added the ability to squash constants from an IN LIST
for queryId computation purposes. This means that a similar
queryId will be generated for the same queries that only
different on the number of values in the IN-LIST.

The patch lacks the ability to apply this optimization to values
passed in as parameters ( i.e. parameter kind = PARAM_EXTERN )
which will be the case for SQL prepared statements and protocol level
prepared statements, i.e.

```
select from t where id in (1, 2, 3) \bind
```
or
```
prepare prp(int, int, int) as select from t where id in ($1, $2, $3);
```

Here is the current state,

```
postgres=# create table t (id int);
CREATE TABLE
postgres=# prepare prp(int, int, int) as select from t where id in ($1, $2, $3);
PREPARE
postgres=# execute prp(1, 2, 3);
postgres=# select from t where id in (1, 2, 3);
--
(0 rows)
postgres=# SELECT query, calls FROM pg_stat_statements ORDER BY query
COLLATE "C";
                                                   query
                                    | calls
-----------------------------------------------------------------------------------------------------------+-------
...
....
 select from t where id in ($1 /*, ... */)
                                    |     1
 select from t where id in ($1, $2, $3)
                                 |     1 <<- prepared statement
(6 rows)
```

but with the attached patch, the optimization applies.

```
 create table t (id int)
                                            |     1
 select from t where id in ($1 /*, ... */)
                                     |     2
(3 rows)
```

I think this is a pretty big gap as many of the common drivers such as JDBC,
which use extended query protocol, will not be able to take advantage of
the optimization in 18, which will be very disappointing.

Thoughts?

Sami Imseih
Amazon Web Services (AWS)