Re: The logic behind comparing generic vs. custom plan costs

From: "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>
To: Justin Blank <justin(dot)blank(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: The logic behind comparing generic vs. custom plan costs
Date: 2025-03-15 18:06:11
Message-ID: CAKFQuwb4LouA8dsi=gBba8MT5-U5nuhbQKi0PcRh0MkNBAzByg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Sat, Mar 15, 2025 at 10:42 AM Justin Blank <justin(dot)blank(at)gmail(dot)com>
wrote:

> My idea had been that even if the custom plans average higher cost
> than the generic plan, it is still worth considering custom plans. If
> 1 time in 5, the custom plan is 10% of the cost of the generic plan,
> it doesn't matter if the average custom plan is worse, you should at
> least determine the cost of the custom plan before deciding whether to
> use it or the generic plan. Even if 80% of the time, you don't use the
> custom plan, the payoff in the good case may be worth it.
>
>
In theory, while ignoring planning costs, a generic plan will never
outperform a custom plan (the custom plan establishes a floor any custom
plan could fall back on). Thus any algorithm that requires computing the
custom plan unconditionally amounts to simply setting the GUC to infinity
(instead of 5).

The assumption is that at moderate to high reuse volumes it is quite
probable that a generic plan will win or at least be acceptable in 999 in
1000 or more executions, not 4 in 5.

David J.

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2025-03-15 18:09:37 Re: The logic behind comparing generic vs. custom plan costs
Previous Message Justin Blank 2025-03-15 17:41:53 The logic behind comparing generic vs. custom plan costs