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.96) (envelope-from ) id 1vxVQR-00GgIE-2K for pgsql-hackers@arkaria.postgresql.org; Tue, 03 Mar 2026 19:29:00 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vxVQO-008Rnf-35 for pgsql-hackers@arkaria.postgresql.org; Tue, 03 Mar 2026 19:28:57 +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.96) (envelope-from ) id 1vxVQO-008RnW-1p for pgsql-hackers@lists.postgresql.org; Tue, 03 Mar 2026 19:28:57 +0000 Received: from mail-ot1-x336.google.com ([2607:f8b0:4864:20::336]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1vxVQM-00000000Iq0-2Pp3 for pgsql-hackers@lists.postgresql.org; Tue, 03 Mar 2026 19:28:56 +0000 Received: by mail-ot1-x336.google.com with SMTP id 46e09a7af769-7d598f4c618so3188769a34.0 for ; Tue, 03 Mar 2026 11:28:54 -0800 (PST) ARC-Seal: i=1; a=rsa-sha256; t=1772566132; cv=none; d=google.com; s=arc-20240605; b=cjiQ8LTsnEW5A6nkwDQJPxJgwlFA3o99c5R9gPqUr1/0SHTYv1laIx6bL34w+L9d7z mmO0dYMbK7TIAfXuCBeKu+MyTr7MyUimkCap4cEbUgxdpTKPl3TkJ15XM400edslcPPD NyWnOvvsPzTr5tKdXBOHvpAfGvRn561fmT1oXiO981AUw8IxJMJU810wWTq78GjJqLzI IfeegPEzX6V4I4KZcKEWIQstvcMoFu+bmXUcsk6IuJSSRy44ngnyYjiPoMMHfD6epeSK oqdunN0Z54IVSr+3dMNcRqQE53A8bd77twGuPC27ccHnY/La/jq/VOp/7xxk0H5KHBmQ KjRA== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20240605; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:dkim-signature; bh=BHL1ZLTMNWO6GMYIe3a4Va+ze3helYSkGkfOctlRSYg=; fh=jcq9eWu0b5Fl4l+neqopKrTWRFzSLH7djLxvt3G/9mA=; b=KXqJeNTq4rZAaSzF64Q2qb5KQa1YtQaHWhBvQvKIIHlSUNUMnaWXHw1qLodi8S1fZO Vvb82ljhNE+xAdvOOyWAjf4Ntn4sWjMoyXaPKDMYkzb8Ps+/0Zl8q3gZ6A0ojg4xgils 5uOg/5l7c6GOzbC21sd6E/9olmqR3ncef8Psy3TMaPll5SCl4IWdWnM5CaB/mqPIdTO4 VD4AdiXbJn58VH3ulO3GjqqZx0SZGdGRe9Z8a/xyvdcdmdiKQlRZTFnQDpJXFUGnwDWp zs/V8VPrA8wz7Wu3XOSXsg4NtPh5ZH2EOacPB327vc+/ySEv5UQ6oaHepoMxN7/IGTns X2tQ==; darn=lists.postgresql.org ARC-Authentication-Results: i=1; mx.google.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1772566132; x=1773170932; darn=lists.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=BHL1ZLTMNWO6GMYIe3a4Va+ze3helYSkGkfOctlRSYg=; b=BwlQJNaq8pX+tI0vsZFUtiJbNw+pOz8DQO9uWKq4AwWo2HSSsdR7Qoyn+zGoJHZ94s xMb7KDG2zba8tDIrZKQgQwcLa+oIGp7Le4ivUi8rMQ2CKb8QQMMcIUL9YzgPZz6GDrBF 6USGcvPq+V4W1egfTRPypQ6LEjkeqGvcDMOqjE5pzXfOR8y+efxDXmVC6CQRTdrO8E3g gD71h70whCiABpI2rtLg9kHjopTSKlTCQZzKOyHbRiIrElvNlOWRXulIhMkl/mFNE/TS GZGKztMe49wxyVW69LcLIG7Dqye0Eb/qN5dvCiMXqTLg5OvRRMPMnk9wMHUDD5D8H5oO IJpg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1772566132; x=1773170932; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-gg:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=BHL1ZLTMNWO6GMYIe3a4Va+ze3helYSkGkfOctlRSYg=; b=mg7nEZ5a1jernJDAXDxQPwfixyJ0adNZUC3ztox1XydQeU4CB6f/skhEFO0QD2qRKu Os9ihzPP1MaAxmNVn/qLa2YIaVqYgjGndE9Zlmt4UhYbS/JsEw45PJrFPf+BvmUj1Z6C OVM8HC+pmm0ueX+g+tQZrBcBdloy2+buQPEh1SEJ4R0SlSKJsQqHLx4L/1TxK3E52uF6 fJXapBKDc2H+PYWXQJrJb/KeQsnZAmI54wxJ1qlKLRAmSTG9mSvQ95yLtVhF6R//JSK1 dO6Kszxy1UHSDfkT6lma6c9myr2x3aqHXvMq7nX0kXU3eO1hJBLCsgYitwLTU7VXez/v 0XlA== X-Forwarded-Encrypted: i=1; AJvYcCVbl/hRVMPheK4JzEZMd0jimZhyJ9hU7/mzYdO3ib200XoodQ0dHaM0YxAVTyzgoZGbRlQe9TwUPXsYZWUv@lists.postgresql.org X-Gm-Message-State: AOJu0YxYd33V+MdZeg/0l9Yy72mlPNcXXYlATFIrTTMclXEec5TjUa7M OljKpat+TeCoYvn7K3v3NZ11N3lwtmNo/9fcOUW4JrcRlmfKQrdkvR2Yf4AhBbs5Ta4WFnIxGFS h565v8PQC8id0C05gql+2nI5y9jnvBw8= X-Gm-Gg: ATEYQzzXPlkLuD/uzwbGnvT8JoDLXQtytmzfDhrEZACczZRyt7ukIYrIDtNJy2m0h1u GkKkvGl3W34bKW3ErhhxciZ/kldRMpHlqJMcJRX48u+qlnDXEYjcncluRNrHOibsrDhxh85Rosn 2VT29TE4daNSDcNrYVYN7JB9c0qAN7nYUkgqxom8Xz1yxE2vVd/Md20zPaRO16WVPPzF0601q5t WAVuWXvKSiDqfNJvxvR8ljAyp9GgMSaHRyH9M1Y9xU1G9tj4V+KvT2PGlWCRnj0lVk/u78mzCby 4osUCWA= X-Received: by 2002:a05:6830:6306:b0:7cf:d4b2:d679 with SMTP id 46e09a7af769-7d591b34cb3mr12977426a34.12.1772566132404; Tue, 03 Mar 2026 11:28:52 -0800 (PST) MIME-Version: 1.0 References: <1136161.1769654478@sss.pgh.pa.us> In-Reply-To: From: "David G. Johnston" Date: Tue, 3 Mar 2026 12:28:16 -0700 X-Gm-Features: AaiRm53cdjG91GpSvru9M-MovzVyNbkkj2yXFHno29neKgkmwXBtIEIkU-r6DtI Message-ID: Subject: Re: pg_plan_advice (now with transparent SQL plan performance overrides - pg_stash_advice) To: Robert Haas Cc: Jakub Wartak , Alexandra Wang , Richard Guo , Lukas Fittl , Tom Lane , Jacob Champion , Dian Fay , Matheus Alcantara , PostgreSQL Hackers Content-Type: multipart/alternative; boundary="000000000000a9b5eb064c23b660" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000a9b5eb064c23b660 Content-Type: text/plain; charset="UTF-8" On Tuesday, March 3, 2026, Robert Haas wrote: > > > > However with below SEQ_SCAN is applied/matched, but marked as failed > > (so bug?): > > > > postgres=# set pg_plan_advice.advice to 'SEQ_SCAN(t1@minmax_1)'; > > SET > > postgres=# explain (plan_advice, costs off) select max(id) from t1; > > QUERY PLAN > > ----------------------------------------------- > > Aggregate > > -> Seq Scan on t1 > > Supplied Plan Advice: > > SEQ_SCAN(t1@minmax_1) /* matched, failed */ > > Generated Plan Advice: > > SEQ_SCAN(t1) > > NO_GATHER(t1) > > I think this is just out of scope for now. It's documented that we > don't have a way of controlling aggregation behavior at present. Here > again, we can do more things in future releases, but it's too late to > add more to the scope for this release. There's still plenty of time > to fix bugs, but this isn't a bug. We'd need a whole lot of new > machinery to prevent this sort of thing, including new core hooks and > new syntax. Here again, we have the freedom to decide that I chose the > scope wrongly and that this is therefore not shippable as is, but I do > not think there is time to significantly expand the scope at this > point. > > I mostly get why specifying an index that doesn't exist as part of advice, alongside a target relation, produces "matched" along with "inapplicable" and "failed". INDEX_SCAN(f no_such_index) /* matched, inapplicable, failed */ But less understandable is why a failure to match a subplan-qualified target produces "matched" when the subplan doesn't appear. SEQ_SCAN(t1@minmax_1) /* matched, failed */ Maybe we need to do something like: relname - matches anywhere in the plan tree relname@somewhere - only looks at "somewhere" for matches; absence of somewhere results in "not matched" (the expected feedback for the advice/query combination above). This would necessitate needing some way to specify "top-level" after the "@" symbol - leaving it blank would suffice. I haven't worked through "directly at the named subplan" versus "the named subplan and any descendants"... If keeping the status quo the existing behavior should be documented. The existing wording for not matched; "or it may occur if the relevant portion of the query was not planned," seems to be the one that covers this case. David J. --000000000000a9b5eb064c23b660 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Tuesday, March 3, 2026, Robert Haas <robertmhaas@gmail.com> wrote:
>
> However with below SEQ_SCAN is applied/matched, but marked as failed > (so bug?):
>
> postgres=3D# set pg_plan_advice.advice to 'SEQ_SCAN(t1@minmax_1)&#= 39;;
> SET
> postgres=3D# explain (plan_advice, costs off) select max(id) from t1;<= br> >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0QU= ERY PLAN
> -----------------------------------------------
>=C2=A0 Aggregate
>=C2=A0 =C2=A0 ->=C2=A0 Seq Scan on t1
>=C2=A0 Supplied Plan Advice:
>=C2=A0 =C2=A0 SEQ_SCAN(t1@minmax_1) /* matched, failed */
>=C2=A0 Generated Plan Advice:
>=C2=A0 =C2=A0 SEQ_SCAN(t1)
>=C2=A0 =C2=A0 NO_GATHER(t1)

I think this is just out of scope for now. It's documented that we
don't have a way of controlling aggregation behavior at present. Here again, we can do more things in future releases, but it's too late to add more to the scope for this release. There's still plenty of time to fix bugs, but this isn't a bug. We'd need a whole lot of new
machinery to prevent this sort of thing, including new core hooks and
new syntax. Here again, we have the freedom to decide that I chose the
scope wrongly and that this is therefore not shippable as is, but I do
not think there is time to significantly expand the scope at this
point.


I mostly get why specifying a= n index that doesn't exist as part of advice, alongside a target relati= on, produces "matched" along with "inapplicable" and &q= uot;failed".

INDEX_SCAN(f no_such_index)= /* matched, inapplicable, failed */

=
But less understandable is why a failure to match a subplan-qualif= ied target produces "matched" when the subplan doesn't appear= .

SEQ_SCAN(t1@minmax_1) /* matched, failed */<= /span>

Maybe we need to do somethin= g like:

relname - matches an= ywhere in the plan tree
relname@somewhere - only looks= at "somewhere" for matches; absence of somewhere results in &quo= t;not matched" (the expected feedback for the advice/query combination= above).

This would necessit= ate needing some way to specify "top-level" after the "@&quo= t; symbol - leaving it blank would suffice.=C2=A0 I haven't worked thro= ugh "directly at the named subplan" versus "the named subpla= n and any descendants"...

If keeping = the status quo the existing behavior should be documented. The existing wor= ding for not matched; "or it may=C2=A0occur if the relevant portion of the query was not planned,"= seems to be the one that covers this case.

David J.

--000000000000a9b5eb064c23b660--