queryId constant squashing does not support prepared statements

From: Sami Imseih <samimseih(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: queryId constant squashing does not support prepared statements
Date: 2025-04-30 21:52:06
Message-ID: CAA5RZ0tRXoPG2y6bMgBCWNDt0Tn=unRerbzYM=oW0syi1=C1OA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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)

Attachment Content-Type Size
v1-0001-Allow-query-jumble-to-squash-a-list-external-para.patch application/octet-stream 4.0 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Robert Haas 2025-04-30 22:00:45 Re: pgsql: Add function to get memory context stats for processes
Previous Message Daniel Gustafsson 2025-04-30 21:24:36 Re: Prevent an error on attaching/creating a DSM/DSA from an interrupt handler.