| 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
```
| 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. |