agora inbox for pgsql-bugs@postgresql.org  

help / color / mirror / Atom feed

From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: 2320415112@qq.com
Subject: BUG #19572: Redundant predicate changes JIT decision and causes an 18x performance difference
Date: Wed, 22 Jul 2026 07:34:28 +0000
Message-ID: <19572-f770e89412629023@postgresql.org> (raw)

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.

view thread (3+ messages)  latest in thread

Message-ID: <19572-f770e89412629023@postgresql.org>
Permalink:  ../19572-f770e89412629023@postgresql.org/
Also on:    postgresql.org/message-id/19572-f770e89412629023@postgresql.org

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-bugs@postgresql.org
  Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, 2320415112@qq.com
  Subject: Re: BUG #19572: Redundant predicate changes JIT decision and causes an 18x performance difference
  In-Reply-To: <19572-f770e89412629023@postgresql.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox