Redundant qualifier elimination
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t253254psql -h localhost -U postgresBuilt from patchset v1 (message #1), September 01, 2026 at 12:22 PM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t253254_1 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t253254_1 && git checkout t253254_1Patchset v1 (message #1) is on t253254_1
Hi,
Bug report #19572 [1]/messages/by-id/19572-f770e89412629023@postgresql.org describes a case where the same qual appears in
both a JOIN's ON clause and the WHERE clause, causing the planner to
misestimate the row count and pick a worse plan (in the report, it
flipped JIT on). IIUC this is working as expected: the planner treats
each AND'ed clause as independent and multiplies their selectivities, so
an identical duplicate gets its selectivity squared.
I hadn't come across this myself, but the reporter mentioned it can show
up with ORM-generated SQL, which seems plausible. So I'm wondering
whether it's worth handling it in the planner.
I'm attaching a PoC that it drops a base-relation restriction clause
when an equal() clause is already present. Note the duplicate is only
visible after qual pushdown, the ON-clause copy only becomes a
baserestrictinfo once distributed, so the check lives in
add_base_clause_to_rel(), alongside the existing "skip always-true qual"
logic.
It only handles exactly identical quals. It could perhaps be generalized
to drop implied quals (if qual1 is true, qual2 is always true). I think
that predicate_implied_by() in predtest.c already does this kind of
proof, though today IIUC it's only used for partial indexes, partition
pruning and constraint exclusion, not the base qual list. I'm not sure
the general case is worth the added planning time.
Thoughts?
[1]: /messages/by-id/19572-f770e89412629023@postgresql.org
--
Matheus Alcantara
EDB: https://www.enterprisedb.com
Hi,
I tested the patch on current master and found two separate issues.
First, equal clauses cannot always be removed. The patch changes results when
a clause contains a VOLATILE function:
CREATE TEMP TABLE t(x int);
INSERT INTO t VALUES (1);
CREATE TEMP SEQUENCE s;
SELECT setval('s', 1, false);
SELECT count(*)
FROM t
WHERE nextval('s') = 1
AND nextval('s') = 1;
Master returns 0 because nextval() is called twice. With the patch, the second
clause is removed and the query returns 1. Volatile clauses therefore must not
be deduplicated.
Complex clauses may also increase planning time. Each new restriction scans
all previous restrictions and equal() may walk the whole expression tree.
The worst-case cost is quadratic in the number of clauses.
For the original non-volatile predicate, the patch does correct the estimate.
Without it, selectivity P is counted as P * P. Removing the duplicate increases
the estimated row count and plan cost.
The query becomes slower only because the corrected cost enables optimized
JIT. Thus the patch fixes the estimate, but moves both equivalent queries to
the slower side of the JIT threshold.
The original 137-function example compares basic JIT with O3 plus inlining.
The smaller published example compares optimized JIT with no JIT at all.
I profiled the smaller query on current master with LLVM 22.1.6. The first
optimized execution spent:
inlining: 10.95 ms
LLVM IR optimization: 4.02 ms
machine-code generation: 5.38 ms
total JIT time: 20.47 ms
The large jump in machine-code generation happens between O0 and O1.
On AArch64, O0 uses a short instruction-selection path and a simple register
allocator. Starting with O1, LLVM enables the full backend: more expensive
instruction selection, instruction scheduling, live-range analysis and global
register allocation.
For the same final IR, machine-code generation took 1.5 ms at O0 and 6.2 ms
at O1.
Experimental PostgreSQL providers gave these total JIT times:
O1: 7.1 ms
O2: 8.0 ms
O3: 8.1 ms
Therefore most of the cost appears when moving from O0 to O1. The difference
between O1 and O3 is small. Replacing O3 with O1 would not remove this
performance cliff. O3 still seems reasonable once PostgreSQL has decided that
optimized JIT is worth using.
So I think:
1. The patch needs a VOLATILE check and planning-time tests.
2. It fixes the selectivity estimate but does not fix the JIT regression.
3. The main problem is the JIT cost decision: it uses total plan cost but does
not account for the number or size of generated JIT functions.
Best regards,
Denis Smirnov
Denis Smirnov <darthunix@gmail.com> writes:
So I think:
1. The patch needs a VOLATILE check and planning-time tests.
2. It fixes the selectivity estimate but does not fix the JIT regression.
3. The main problem is the JIT cost decision: it uses total plan cost but does
not account for the number or size of generated JIT functions.
For the record, I do not think we should accept this patch, even
if the problems you mention get fixed. It would impose nontrivial
costs on every query --- roughly O(N^2) in the number of restriction
clauses --- in order to fix badly-written queries, and that is not
a tradeoff I like. We could doubtless improve the cost by expending
lots more engineering effort, but it's still catering to badly
written queries, and we have better places to put the effort.
(There have been plenty of previous discussions along this same line,
btw.)
regards, tom lane
On 14/08/26 13:27, Tom Lane wrote:
Denis Smirnov <darthunix@gmail.com> writes:
So I think:
1. The patch needs a VOLATILE check and planning-time tests.
2. It fixes the selectivity estimate but does not fix the JIT regression.
3. The main problem is the JIT cost decision: it uses total plan cost but does
not account for the number or size of generated JIT functions.For the record, I do not think we should accept this patch, even
if the problems you mention get fixed. It would impose nontrivial
costs on every query --- roughly O(N^2) in the number of restriction
clauses --- in order to fix badly-written queries, and that is not
a tradeoff I like. We could doubtless improve the cost by expending
lots more engineering effort, but it's still catering to badly
written queries, and we have better places to put the effort.(There have been plenty of previous discussions along this same line,
btw.)regards, tom lane
Thank you for the feedback! I was not aware of such discussions,
thanks for pointing this out.
--
Matheus Alcantara
EDB: https://www.enterprisedb.com