Re: BUG #19357: PostgreSQL generates a custom plan that performs worse than the generic plan for a certain query.

Hi Tom Lane,

I strongly agree with your point. My objective is not to make PostgreSQL capable of optimizing all queries. My intention is simply to note that, for this test case, PostgreSQL is fully capable of optimizing the query, as demonstrated by the generic plan. However, I am unsure why the performance of the custom plan actually degrades. I believe that understanding the reasons behind this performance degradation (or suboptimality) could be important. This facilitates PostgreSQL’s selection of the optimal query plan.

Best,
Chi

Original


From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us&gt;
Date: 2025-12-17 23:39
To: Greg Sabino Mullane <htamfids(at)gmail(dot)com&gt;
Cc: 798604270 <798604270(at)qq(dot)com&gt;, pgsql-bugs <pgsql-bugs(at)lists(dot)postgresql(dot)org&gt;
Subject: Re: BUG #19357: PostgreSQL generates a custom plan that performs worse than the generic plan for a certain query.

Greg&nbsp;Sabino&nbsp;Mullane&nbsp;<htamfids(at)gmail(dot)com&gt;&nbsp;writes:
&gt;&nbsp;Please&nbsp;simplify&nbsp;your&nbsp;test&nbsp;query&nbsp;as&nbsp;much&nbsp;as&nbsp;possible&nbsp;and&nbsp;use&nbsp;"text"&nbsp;format
&gt;&nbsp;in&nbsp;your&nbsp;explain&nbsp;results;&nbsp;those&nbsp;will&nbsp;improve&nbsp;your&nbsp;chances&nbsp;of&nbsp;getting&nbsp;a
&gt;&nbsp;useful&nbsp;reply.&nbsp;:)

More&nbsp;to&nbsp;the&nbsp;point:&nbsp;it's&nbsp;hard&nbsp;to&nbsp;get&nbsp;excited&nbsp;about&nbsp;optimizing&nbsp;a&nbsp;query
that&nbsp;was&nbsp;so&nbsp;obviously&nbsp;built&nbsp;by&nbsp;a&nbsp;fuzzer&nbsp;rather&nbsp;than&nbsp;corresponding&nbsp;to
any&nbsp;real-world&nbsp;use-case.&nbsp;&nbsp;We&nbsp;have&nbsp;finite&nbsp;manpower&nbsp;and&nbsp;we'd&nbsp;much&nbsp;rather
put&nbsp;it&nbsp;into&nbsp;solving&nbsp;users'&nbsp;real&nbsp;problems.

regards,&nbsp;tom&nbsp;lane