Re: BUG #19572: Redundant predicate changes JIT decision and causes an 18x performance difference

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(at)gmail(dot)com&gt;
发件时间：2026年7月30日 01:44
收件人：2320415112 <2320415112(at)qq(dot)com&gt;, pgsql-bugs <pgsql-bugs(at)lists(dot)postgresql(dot)org&gt;
主题：Re: BUG #19572: Redundant predicate changes JIT decision and causes an 18x performance difference

On&nbsp;Wed&nbsp;Jul&nbsp;22,&nbsp;2026&nbsp;at&nbsp;4:34&nbsp;AM&nbsp;-03,&nbsp;PG&nbsp;Bug&nbsp;reporting&nbsp;form&nbsp;wrote:
&gt;&nbsp;The&nbsp;following&nbsp;bug&nbsp;has&nbsp;been&nbsp;logged&nbsp;on&nbsp;the&nbsp;website:
&gt;
&gt;&nbsp;Bug&nbsp;reference:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;19572
&gt;&nbsp;Logged&nbsp;by:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;cl&nbsp;hl
&gt;&nbsp;Email&nbsp;address:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2320415112(at)qq(dot)com
&gt;&nbsp;PostgreSQL&nbsp;version:&nbsp;17.10
&gt;&nbsp;Operating&nbsp;system:&nbsp;&nbsp;&nbsp;Linux&nbsp;LAPTOP-2SQAVLB0&nbsp;6.6.87.2-microsoft-standard-
&gt;&nbsp;Description:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&gt;
&gt;&nbsp;##&nbsp;Description
&gt;
&gt;&nbsp;This&nbsp;issue&nbsp;concerns&nbsp;a&nbsp;predicate&nbsp;that&nbsp;already&nbsp;applies&nbsp;to&nbsp;one&nbsp;side&nbsp;of&nbsp;an&nbsp;inner
&gt;&nbsp;join&nbsp;and&nbsp;is&nbsp;redundantly&nbsp;copied&nbsp;into&nbsp;the&nbsp;join&nbsp;condition.&nbsp;The&nbsp;transformation
&gt;&nbsp;is&nbsp;semantics-preserving.&nbsp;PostgreSQL&nbsp;retains&nbsp;both&nbsp;copies&nbsp;and&nbsp;treats&nbsp;their
&gt;&nbsp;selectivities&nbsp;as&nbsp;independent,&nbsp;even&nbsp;though&nbsp;they&nbsp;are&nbsp;identical.&nbsp;The
&gt;&nbsp;underestimated&nbsp;row&nbsp;count&nbsp;lowers&nbsp;the&nbsp;total&nbsp;plan&nbsp;cost&nbsp;enough&nbsp;to&nbsp;change&nbsp;whether
&gt;&nbsp;expensive&nbsp;JIT&nbsp;inlining&nbsp;and&nbsp;optimization&nbsp;are&nbsp;enabled.
&gt;
&gt;&nbsp;###&nbsp;Expected&nbsp;Behaviour
&gt;
&gt;&nbsp;PostgreSQL&nbsp;should&nbsp;recognize&nbsp;identical&nbsp;predicates&nbsp;or&nbsp;account&nbsp;for&nbsp;their
&gt;&nbsp;complete&nbsp;correlation.&nbsp;Adding&nbsp;a&nbsp;redundant&nbsp;copy&nbsp;should&nbsp;not&nbsp;change&nbsp;cardinality
&gt;&nbsp;estimates,&nbsp;cross&nbsp;a&nbsp;JIT&nbsp;threshold,&nbsp;or&nbsp;produce&nbsp;a&nbsp;large&nbsp;execution-time
&gt;&nbsp;difference&nbsp;between&nbsp;equivalent&nbsp;queries.
&gt;

AFAICT&nbsp;the&nbsp;planner&nbsp;treats&nbsp;each&nbsp;AND&nbsp;clause&nbsp;as&nbsp;an&nbsp;independent&nbsp;condition
and&nbsp;multiplies&nbsp;their&nbsp;selectivities&nbsp;together.&nbsp;So&nbsp;in&nbsp;the&nbsp;duplicated&nbsp;case
it&nbsp;estimates&nbsp;that&nbsp;the&nbsp;Seq&nbsp;Scan&nbsp;on&nbsp;redundant_join_fact&nbsp;returns&nbsp;fewer&nbsp;rows
because&nbsp;there&nbsp;are&nbsp;"more"&nbsp;filters,&nbsp;even&nbsp;though&nbsp;the&nbsp;two&nbsp;are&nbsp;identical.
Note&nbsp;the&nbsp;actual&nbsp;row&nbsp;counts&nbsp;are&nbsp;the&nbsp;same&nbsp;in&nbsp;both&nbsp;plans,&nbsp;only&nbsp;the&nbsp;estimate
changes,&nbsp;so&nbsp;the&nbsp;results&nbsp;are&nbsp;correct.&nbsp;This&nbsp;is&nbsp;a&nbsp;cardinality-estimation
that&nbsp;happens&nbsp;to&nbsp;cross&nbsp;the&nbsp;JIT&nbsp;cost&nbsp;threshold.&nbsp;So&nbsp;I&nbsp;think&nbsp;that&nbsp;this&nbsp;is&nbsp;an
expected&nbsp;behavior&nbsp;rather&nbsp;than&nbsp;a&nbsp;bug,&nbsp;though&nbsp;I&nbsp;may&nbsp;be&nbsp;wrong.

But&nbsp;I'm&nbsp;wondering&nbsp;whether&nbsp;the&nbsp;planner&nbsp;should&nbsp;detect&nbsp;duplicated&nbsp;quals&nbsp;and
drop&nbsp;the&nbsp;redundant&nbsp;qual,&nbsp;or&nbsp;more&nbsp;generally&nbsp;recognize&nbsp;when&nbsp;one&nbsp;qual
implies&nbsp;another&nbsp;(if&nbsp;qual1&nbsp;is&nbsp;true,&nbsp;qual2&nbsp;is&nbsp;always&nbsp;true)&nbsp;and&nbsp;remove
qual2.&nbsp;We&nbsp;already&nbsp;have&nbsp;predicate_implied_by()&nbsp;in&nbsp;predtest.c,&nbsp;but&nbsp;IIUC
it's&nbsp;currently&nbsp;only&nbsp;used&nbsp;for&nbsp;partial&nbsp;indexes,&nbsp;partition&nbsp;pruning,&nbsp;and
constraint&nbsp;exclusion,&nbsp;but&nbsp;I'm&nbsp;not&nbsp;sure&nbsp;if&nbsp;it&nbsp;can&nbsp;be&nbsp;used&nbsp;for&nbsp;such&nbsp;case.

I'm&nbsp;not&nbsp;sure&nbsp;it's&nbsp;worth&nbsp;doing&nbsp;for&nbsp;the&nbsp;general&nbsp;case&nbsp;given&nbsp;the&nbsp;possible
added&nbsp;planning&nbsp;time,&nbsp;but&nbsp;exact&nbsp;duplicate&nbsp;detection&nbsp;might&nbsp;be&nbsp;cheap&nbsp;enough
to&nbsp;be&nbsp;worthwhile,&nbsp;but&nbsp;also&nbsp;I'm&nbsp;not&nbsp;sure&nbsp;if&nbsp;it's&nbsp;a&nbsp;common&nbsp;pattern&nbsp;to&nbsp;make
it&nbsp;worh&nbsp;implementing&nbsp;it.&nbsp;Any&nbsp;thoughts?

--
Matheus&nbsp;Alcantara
EDB:&nbsp;https://www.enterprisedb.com