BUG #19358: Short circuit optimization exists in generic plan but missed in custom plan

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: 798604270(at)qq(dot)com
Subject: BUG #19358: Short circuit optimization exists in generic plan but missed in custom plan
Date: 2025-12-17 12:03:44
Message-ID: 19358-013f22cf91350df1@postgresql.org
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 19358
Logged by: Chi Zhang
Email address: 798604270(at)qq(dot)com
PostgreSQL version: 17.6
Operating system: ubuntu 24.04 with docker
Description:

Hi,

In the following test case, there are two equivalent updates; the first one
is in prepared form, and the second one is a normal update. There is a
configuration that sets plan_cache_mode = force_generic_plan, which means
the prepared updates will use a generic plan. When I run this test case, I
found the prepared update can execute, but the normal update triggers an
error `result of range difference would not be contiguous`, I can understand
this because this error is skipped in the prepared update by short circuit
optimization. As shown in the document at
https://www.postgresql.org/docs/current/runtime-config-query.html that, the
generic plan maybe inefficient than the custom plan. So why the short
circuit optimization applied in the prepared update (with a generic plan)
but not in the normal update (with a custom plan).

```
SET plan_cache_mode = force_generic_plan;
CREATE TABLE t3(c0 TEXT);
INSERT INTO t3(c0) VALUES('');
PREPARE prepare_query (text, int4range, int4range) AS UPDATE t3 SET c0=$1
WHERE (((t3.c0) IN (t3.c0))OR(((t3.c0)) BETWEEN (((t3.c0)||((($2)-($3)))))
AND (t3.c0)));
EXECUTE prepare_query('', '[-993693027,1525305818]'::int4range,
'[-168306621,-163656947)'::int4range);
DEALLOCATE prepare_query;
UPDATE t3 SET c0=''::text WHERE (((t3.c0) IN (t3.c0))OR(((t3.c0 )) BETWEEN
(((t3.c0)||((('[-993693027,1525305818]'::int4range)-('[-168306621,-163656947)'::int4range)))))
AND (t3.c0))); -- ERROR: result of range difference would not be contiguous
```

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Dave Cramer 2025-12-17 12:55:25 Re: BUG #19350: Short circuit optimization missed when runningsqlscriptes in JDBC
Previous Message PG Bug reporting form 2025-12-17 06:57:30 BUG #19357: PostgreSQL generates a custom plan that performs worse than the generic plan for a certain query.