From: Tom Lane <tgl@sss.pgh.pa.us>
To: Malay Keshav <malay.keshav@databricks.com>
cc: pgsql-bugs@lists.postgresql.org,
        "malay.keshav@gmail.com" <malay.keshav@gmail.com>
Subject: Re: [Bug][Ver 11]: Generic query plan selected is worse than custom
 query plan
In-reply-to: 
 <CAJzqzvo+zATPZTtmFK0gjRQUftOuyMr6mB_bT_Pk+T2QNiSdrw@mail.gmail.com>
References: 
 <CAJzqzvo+zATPZTtmFK0gjRQUftOuyMr6mB_bT_Pk+T2QNiSdrw@mail.gmail.com>
Comments: In-reply-to Malay Keshav <malay.keshav@databricks.com>
	message dated "Thu, 29 Sep 2022 11:33:54 -0700"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <347703.1664481482.1@sss.pgh.pa.us>
Date: Thu, 29 Sep 2022 15:58:02 -0400
Message-ID: <347704.1664481482@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/347704.1664481482%40sss.pgh.pa.us>
Precedence: bulk

Malay Keshav <malay.keshav@databricks.com> writes:
> In our particular case, the Postgres engine decided to pick the generic
> query plan and cache it for all further calls with that query. My
> understanding was that the generic query plan would only be selected if it
> had a better execution time than the custom query plan. Which in our case
> is not true.

> We were able to reproduce this deterministically using the same query
> parameters to trigger the engine to pick the bad generic query plan on the
> 6th run (first 5 runs shows the engine used the efficient query plan). Why
> does the engine pick the generic query plan when its execution time is
> worse than the custom query plan? Is this a bug?

Since you've provided precisely zero detail, it's impossible to answer
in any detail, but this is unlikely to be anything we'd call a bug.
What's presumably happening is that the planner's estimate of the cost
of the generic plan comes out less than its estimate of the cost of
a custom plan, but that estimate is faulty.  Sure, we'd love to improve
all such situations, but it's not an easy task --- and any changes in that
area would not be something we'd consider back-patching into long-stable
branches.

			regards, tom lane