Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1ttVuQ-00DOF1-Q8 for pgsql-general@arkaria.postgresql.org; Sat, 15 Mar 2025 18:06:54 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1ttVuO-000YC6-F9 for pgsql-general@arkaria.postgresql.org; Sat, 15 Mar 2025 18:06:52 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1ttVuO-000YB2-3w for pgsql-general@lists.postgresql.org; Sat, 15 Mar 2025 18:06:52 +0000 Received: from mail-oa1-x33.google.com ([2001:4860:4864:20::33]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1ttVuK-0033Ax-2E for pgsql-general@postgresql.org; Sat, 15 Mar 2025 18:06:51 +0000 Received: by mail-oa1-x33.google.com with SMTP id 586e51a60fabf-2c11ddc865eso1075848fac.3 for ; Sat, 15 Mar 2025 11:06:49 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1742062008; x=1742666808; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=KGv5yYRZ2wpY+6aqG22MKMg6Nej6up5t3edH5ueQNjs=; b=dIa+08ICwOwpOD2jfCZQ8NpZ6S+daSTCChq+NewURhjnY05ScGOsa2M0Q4vT4l5uEO T1edH7UD6jASF2g4bBDQV+uLZV6IpmYtzndxcziF5qxnDuBqQG75ZiofekbrxZ5CubSk t0PvV666rZyfrcwtZNRKQsihaN/AAKUniSn7brU6C9bAJDJi10uaxGMnsQuN9SW6krUT JJxeLzPps3o33L5/hQMYxEtYkPJ5eak5kWc0fRAtURn1aiVbDpguigh5dNgMWyJLiKKx pwtQHwMRVb48C5Udg0x84Qa9UsRWidxcO/9RQXNArZMLDq6YfvhEI9yTIwmbEj7R3Ubv 2+RQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1742062008; x=1742666808; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=KGv5yYRZ2wpY+6aqG22MKMg6Nej6up5t3edH5ueQNjs=; b=HLYvqWHATVX33imNumSHe/hJ20YxK0uAoFcwcI49eEgLBotPyBz2qa5G/T3C5bIx/v n03AKcNOB+3dZAF0SKHhBrvJ+enoEhrjE9wnJVNwu4oU+/duZp2lPEHtAHsuSOMFUHWf sLCyCVxHSuQbLlDZIzkCfJ+WHeHEscAMya0IYI4vBy0d9pJfIQWW7rVyf7QSPfh3+Ilf jvauvAc7rxbGpD0JMea/w8Fx/0lsrlK0jZqlxCk2lBlfFxwZpQeL5cW5/HdyLXg593zM qN8BF7KPtbgXv8FJvQMqHpfi3eTpgaD4tWNYNFCkqgGE/4Ti/GS/fhEf2UXeYpDgLq5b UFyQ== X-Gm-Message-State: AOJu0Yz7aiW3M2Y7RbnoQTQYe1YlqKzW7I/Te3e2olMYHy9f+RfplmkU CJrNPwh4LuPTOXD+Mm2TST3a4ez68kFzLec/T9uuodtygcHAJhkbiSeeXSMmJOzGzJxiAzY2T79 QyMmYb75VhGLiYq6PNLXMMbjJGmA= X-Gm-Gg: ASbGncuikrn82aCxwGonlv0cmazIye/8Mo4G+i6xv/lHjIvFD27bY9blwQWb1scWvpt tAql43x+iop31MipNo8jkkII8Ph543KluD30tV6I2Ro5GsRHkl/cQrnt1Y2Da24W/CDJikHksXv kwEhpZ9MajY7mL6OiQhWycnUDfpg87u/QJLuk= X-Google-Smtp-Source: AGHT+IEdJu0CYw3B3uWIDAPs+4j1dJoqbPoA/uLn1uBrEjFvJUzbjb7LlaA2zndSJmE6zgK/L+Tvx0FIrq2KXyQ3FC4= X-Received: by 2002:a05:6870:4f16:b0:29e:255e:9551 with SMTP id 586e51a60fabf-2c690ee0971mr3794613fac.2.1742062007781; Sat, 15 Mar 2025 11:06:47 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: "David G. Johnston" Date: Sat, 15 Mar 2025 11:06:11 -0700 X-Gm-Features: AQ5f1Jq6pUKXAsYo8QkS1KHHy4saQkXfjXP9udKzXLxPbgnn1NXj8_ocWYdJsr8 Message-ID: Subject: Re: The logic behind comparing generic vs. custom plan costs To: Justin Blank Cc: pgsql-general@postgresql.org Content-Type: multipart/alternative; boundary="00000000000026925d0630656bc6" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000026925d0630656bc6 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Sat, Mar 15, 2025 at 10:42=E2=80=AFAM Justin Blank 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. --00000000000026925d0630656bc6 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Sat, Mar 15, 2025 at 10:42=E2=80=AFAM Justin Blank <= justin.blank@gmail.com> wr= ote:
My idea had been that even i= f 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<= br> 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=C2=A0back=C2=A0on).=C2=A0 Th= us any algorithm that requires computing the custom plan unconditionally am= ounts 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 1= 000 or more executions, not 4 in 5.

David J.

--00000000000026925d0630656bc6--