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
Date: Thu, 29 Sep 2022 15:58:02 -0400
Message-ID: <347704.1664481482@sss.pgh.pa.us> (raw)
In-Reply-To: <CAJzqzvo+zATPZTtmFK0gjRQUftOuyMr6mB_bT_Pk+T2QNiSdrw@mail.gmail.com>
References: <CAJzqzvo+zATPZTtmFK0gjRQUftOuyMr6mB_bT_Pk+T2QNiSdrw@mail.gmail.com>
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
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: tgl@sss.pgh.pa.us, malay.keshav@databricks.com, malay.keshav@gmail.com
Subject: Re: [Bug][Ver 11]: Generic query plan selected is worse than custom query plan
In-Reply-To: <347704.1664481482@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox