BUG #19572: Redundant predicate changes JIT decision and causes an 18x performance difference
The following bug has been logged on the website:
Bug reference: 19572
Logged by: cl hl
Email address: 2320415112@qq.com
PostgreSQL version: 17.10
Operating system: Linux LAPTOP-2SQAVLB0 6.6.87.2-microsoft-standard-
Description:
## Description
This issue concerns a predicate that already applies to one side of an inner
join and is redundantly copied into the join condition. The transformation
is semantics-preserving. PostgreSQL retains both copies and treats their
selectivities as independent, even though they are identical. The
underestimated row count lowers the total plan cost enough to change whether
expensive JIT inlining and optimization are enabled.
### Expected Behaviour
PostgreSQL should recognize identical predicates or account for their
complete correlation. Adding a redundant copy should not change cardinality
estimates, cross a JIT threshold, or produce a large execution-time
difference between equivalent queries.
### Actual Behaviour
In pair 3868, the predicate `t14.c4 NOT BETWEEN 30 AND 46` is present in the
subquery `WHERE` clause. The mutated query also copies it into the preceding
inner join's `ON` condition while retaining the original copy.
The recorded plans show:
| Measurement | Original | Redundant predicate |
|---|---:|---:|
| Estimated `t14` rows | 658 | 432 |
| Top-level estimated rows | 16,367,750 | 10,746,000 |
| Top-level cost | 610,279.14 | 399,518.16 |
| JIT inlining | enabled | disabled |
| JIT optimization | enabled | disabled |
| Median execution time | 742.033 ms | 40.223 ms |
The equivalent query with the redundant predicate is approximately 18.45x
faster. The original cost exceeds PostgreSQL's default
`jit_inline_above_cost` and `jit_optimize_above_cost` value of 500,000,
while the underestimated mutated plan falls below it. Both plans compile 137
JIT functions, but only the original performs costly inlining and
optimization.
## How to repeat
The following standalone case uses lower session-local thresholds so the
same mechanism can be reproduced with small tables. It does not change
global server configuration.
```sql
DROP TABLE IF EXISTS redundant_join_fact;
DROP TABLE IF EXISTS redundant_join_dimension;
CREATE TABLE redundant_join_fact (
x INTEGER NOT NULL
);
CREATE TABLE redundant_join_dimension (
y INTEGER NOT NULL
);
INSERT INTO redundant_join_fact (x)
SELECT g % 100
FROM generate_series(1, 100000) AS g;
INSERT INTO redundant_join_dimension (y)
SELECT g
FROM generate_series(1, 10) AS g;
ANALYZE redundant_join_fact;
ANALYZE redundant_join_dimension;
-- Place the JIT threshold between the two estimated plan costs.
SET jit_above_cost = 11000;
SET jit_inline_above_cost = 0;
SET jit_optimize_above_cost = 0;
-- Original predicate P. Estimated cost is about 13,694, so optimized JIT is
-- enabled. The result is 47,605,000.
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS, TIMING OFF)
SELECT SUM(f.x + d.y)
FROM redundant_join_fact AS f
CROSS JOIN redundant_join_dimension AS d
WHERE f.x < 30 OR f.x > 46;
-- Equivalent P AND P. Estimated cost falls to about 10,333, below
-- jit_above_cost. The result remains 47,605,000.
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS, TIMING OFF)
SELECT SUM(f.x + d.y)
FROM redundant_join_fact AS f
CROSS JOIN redundant_join_dimension AS d
WHERE (f.x < 30 OR f.x > 46)
AND (f.x < 30 OR f.x > 46);
RESET jit_above_cost;
RESET jit_inline_above_cost;
RESET jit_optimize_above_cost;
```
On the tested server, both queries process the same 830,000 joined rows. The
characteristic output is:
```text
P:
estimated fact rows: 67,143
total cost: 13,694.16
JIT Options: Inlining true, Optimization true
Execution Time: 198.767 ms
P AND P:
estimated fact rows: 45,082
total cost: 10,333.49
no JIT section
Execution Time: 41.958 ms
```
The redundant form is approximately 4.74x faster in the minimized case.
Exact times depend on CPU and JIT state, but the estimate reduction and
threshold crossing are deterministic with the tested version.
On Wed Jul 22, 2026 at 4:34 AM -03, PG Bug reporting form wrote:
The following bug has been logged on the website:
Bug reference: 19572
Logged by: cl hl
Email address: 2320415112@qq.com
PostgreSQL version: 17.10
Operating system: Linux LAPTOP-2SQAVLB0 6.6.87.2-microsoft-standard-
Description:## Description
This issue concerns a predicate that already applies to one side of an inner
join and is redundantly copied into the join condition. The transformation
is semantics-preserving. PostgreSQL retains both copies and treats their
selectivities as independent, even though they are identical. The
underestimated row count lowers the total plan cost enough to change whether
expensive JIT inlining and optimization are enabled.### Expected Behaviour
PostgreSQL should recognize identical predicates or account for their
complete correlation. Adding a redundant copy should not change cardinality
estimates, cross a JIT threshold, or produce a large execution-time
difference between equivalent queries.
AFAICT the planner treats each AND clause as an independent condition
and multiplies their selectivities together. So in the duplicated case
it estimates that the Seq Scan on redundant_join_fact returns fewer rows
because there are "more" filters, even though the two are identical.
Note the actual row counts are the same in both plans, only the estimate
changes, so the results are correct. This is a cardinality-estimation
that happens to cross the JIT cost threshold. So I think that this is an
expected behavior rather than a bug, though I may be wrong.
But I'm wondering whether the planner should detect duplicated quals and
drop the redundant qual, or more generally recognize when one qual
implies another (if qual1 is true, qual2 is always true) and remove
qual2. We already have predicate_implied_by() in predtest.c, but IIUC
it's currently only used for partial indexes, partition pruning, and
constraint exclusion, but I'm not sure if it can be used for such case.
I'm not sure it's worth doing for the general case given the possible
added planning time, but exact duplicate detection might be cheap enough
to be worthwhile, but also I'm not sure if it's a common pattern to make
it worh implementing it. Any thoughts?
--
Matheus Alcantara
EDB: https://www.enterprisedb.com
Thanks for the detailed analysis.
You are completely right—the query produces correct results, so it's an estimation/costing issue rather than a correctness bug.
Regarding whether it's worth addressing:
In real-world applications, especially those using ORMs or complex auto-generated SQL, redundant predicates (or transitive quals across joins) are actually quite common. When a redundant qual causes the estimated row count to drop significantly, it can severely distort the plan choice or prematurely cross cost thresholds (like JIT enablement, as seen here).
If exact duplicate qual detection (or a quick check via list membership before adding clauses) can be done at negligible planning cost, it seems like a very beneficial optimization for generator-heavy workloads.
Thanks again!
原始邮件
发件人：Matheus Alcantara <matheusssilv97@gmail.com>
发件时间：2026年7月30日 01:44
收件人：2320415112 <2320415112@qq.com>, pgsql-bugs <pgsql-bugs@lists.postgresql.org>
主题：Re: BUG #19572: Redundant predicate changes JIT decision and causes an 18x performance difference
On Wed Jul 22, 2026 at 4:34 AM -03, PG Bug reporting form wrote:
> The following bug has been logged on the website:
>
> Bug reference: 19572
> Logged by: cl hl
> Email address: 2320415112@qq.com
> PostgreSQL version: 17.10
> Operating system: Linux LAPTOP-2SQAVLB0 6.6.87.2-microsoft-standard-
> Description:
>
> ## Description
>
> This issue concerns a predicate that already applies to one side of an inner
> join and is redundantly copied into the join condition. The transformation
> is semantics-preserving. PostgreSQL retains both copies and treats their
> selectivities as independent, even though they are identical. The
> underestimated row count lowers the total plan cost enough to change whether
> expensive JIT inlining and optimization are enabled.
>
> ### Expected Behaviour
>
> PostgreSQL should recognize identical predicates or account for their
> complete correlation. Adding a redundant copy should not change cardinality
> estimates, cross a JIT threshold, or produce a large execution-time
> difference between equivalent queries.
>
AFAICT the planner treats each AND clause as an independent condition
and multiplies their selectivities together. So in the duplicated case
it estimates that the Seq Scan on redundant_join_fact returns fewer rows
because there are "more" filters, even though the two are identical.
Note the actual row counts are the same in both plans, only the estimate
changes, so the results are correct. This is a cardinality-estimation
that happens to cross the JIT cost threshold. So I think that this is an
expected behavior rather than a bug, though I may be wrong.
But I'm wondering whether the planner should detect duplicated quals and
drop the redundant qual, or more generally recognize when one qual
implies another (if qual1 is true, qual2 is always true) and remove
qual2. We already have predicate_implied_by() in predtest.c, but IIUC
it's currently only used for partial indexes, partition pruning, and
constraint exclusion, but I'm not sure if it can be used for such case.
I'm not sure it's worth doing for the general case given the possible
added planning time, but exact duplicate detection might be cheap enough
to be worthwhile, but also I'm not sure if it's a common pattern to make
it worh implementing it. Any thoughts?
--
Matheus Alcantara
EDB: https://www.enterprisedb.com
On Thu Jul 30, 2026 at 3:44 AM -03, =?utf-8?B?6ZmI5YiX6KGM?= wrote:
Thanks for the detailed analysis.
You are completely right—the query produces correct results, so it's
an estimation/costing issue rather than a correctness bug.Regarding whether it's worth addressing: In real-world applications,
especially those using ORMs or complex auto-generated SQL, redundant
predicates (or transitive quals across joins) are actually quite
common. When a redundant qual causes the estimated row count to drop
significantly, it can severely distort the plan choice or prematurely
cross cost thresholds (like JIT enablement, as seen here).
Yeah, I'm not surprised that ORMs may generate such queries. I've posted
about this on -hackers [1]/messages/by-id/DKBX1KUW8KUW.2DU5RD192DHIQ@gmail.com.
[1]: /messages/by-id/DKBX1KUW8KUW.2DU5RD192DHIQ@gmail.com
--
Matheus Alcantara
EDB: https://www.enterprisedb.com