MIME-Version: 1.0
References: 
 <CA+TgmoZ-Jh1T6QyWoCODMVQdhTUPYkaZjWztzP1En4=ZHoKPzw@mail.gmail.com>
 <CA+TgmoYjcBA6dw3nwiyfDzPXTCrxTZPXDMrc2TrDJcL1cPK6iA@mail.gmail.com>
 <CA+TgmoYru-vxoTKfwjQby30r2OkTXfb18Km_=VLs6qk8Akr0-g@mail.gmail.com>
 <CA+Tgmoau7yJtvbeH-0kPt1Q=Gt_ezRdgM35Q1=LT665U_86Etg@mail.gmail.com>
 <1299934.1773938807@sss.pgh.pa.us>
 <CA+TgmobOLrMn5jEinWNPL5SrDH1DPpo3a4j+S6-4yhsZwWgzLg@mail.gmail.com>
 <CA+TgmoZUN8FT1Ah=m6Uis5bHa4FUa+_hMDWtcABG17toEfpiUg@mail.gmail.com>
 <CA+TgmoYh2-kM+tscOz=jVYq9Tf4SRPVqzPojs3KLZcW6E9m1BQ@mail.gmail.com>
 <CA+TgmoaK=4w7-qknUo3QhUJ53pXZq=c=KgZmRyD+k7ytqfmgSg@mail.gmail.com>
 <CAP53Pkz3DSFaaowYvbO5LULf3NhydD_UhHkighfWf6_pwxiqUw@mail.gmail.com>
 <CA+TgmoZ45n5jaNKKgbbj4-kYV8WsPvUn=Z8HnoZ7tUb_p9WKXg@mail.gmail.com>
 <CA+TgmoYuWmN-00Ec5pY7zAcpSFQUQLbgAdVWGR9kOR-HM-fHrA@mail.gmail.com>
 <CAP53Pkzn_wZ-R-cPdD9XSQ9+myPUUsPMMqVBPNG3XWXhgfm1-Q@mail.gmail.com>
 <CA+Tgmobxbju8PrY_NULtPr7b7UShp4+Jqibm2Bou8TVS69gObQ@mail.gmail.com>
 <0afba1ce-c946-4131-972d-191d9a1c097c@gmail.com>
In-Reply-To: <0afba1ce-c946-4131-972d-191d9a1c097c@gmail.com>
From: Robert Haas <robertmhaas@gmail.com>
Date: Fri, 27 Mar 2026 08:55:41 -0400
Message-ID: 
 <CA+TgmoZpQDJOz_W34Wkp-JA=MQpzLeV6dsDGt=04U0AD6c65RA@mail.gmail.com>
Subject: Re: pg_plan_advice
To: Alexander Lakhin <exclusion@gmail.com>
Cc: Lukas Fittl <lukas@fittl.com>, Tom Lane <tgl@sss.pgh.pa.us>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: multipart/mixed; boundary="000000000000851d89064e01053f"
Archived-At: 
 <https://www.postgresql.org/message-id/CA%2BTgmoZpQDJOz_W34Wkp-JA%3DMQpzLeV6dsDGt%3D04U0AD6c65RA%40mail.gmail.com>
Precedence: bulk

--000000000000851d89064e01053f
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Fri, Mar 27, 2026 at 2:00=E2=80=AFAM Alexander Lakhin <exclusion@gmail.c=
om> wrote:
> I could not reproduce recent failures from skink and morepork so far, but
> I found that changing some GUCs can trigger similar warnings, e.g.:

Thanks for the reports.

> echo "geqo_threshold =3D 8" >/tmp/extra.config

Failures here are expected. When planning is done via GEQO, not all
join orders are explored, and pg_plan_advice can only constrain the
join order from among the options considered by the planner. So, with
GEQO + test_plan_advice, any given test is going to pass if the second
round of planning considers the join order chosen by the first, and
fail otherwise.

This could be improved at some point in the future. For example,
somebody could add hooks to GEQO so that pg_plan_advice can cause it
to generate only candidates which are consistent with the supplied
advice. In practice, I'm not sure this is going to be a good use of
time. I suspect the energy would be better invested in improving GEQO
or coming up with a more useful replacement. The gap that exists here
doesn't mean that you can't use pg_plan_advice with GEQO; it only
means that you are going to have a bad time using them together if you
provide *complete* (or nearly-complete) plan advice.

> echo "join_collapse_limit =3D 1000" >/tmp/extra.config

The cause here actually seems to be GEQO once again. Raising the
join_collapse_limit causes some join problems to get bigger, which has
the result that they then use GEQO. At least for me, if I also bump up
geqo_threshold, the failures go away.

> and an assertion failure with:
> enable_parallel_append =3D off
> enable_partitionwise_aggregate =3D on
> cpu_tuple_cost =3D 1000
>
> TRAP: failed Assert("relids !=3D NULL"), File: "pgpa_scan.c", Line: 248, =
PID: 1956762

Obviously, this one's a bug. I think the attached should fix it.

--=20
Robert Haas
EDB: http://www.enterprisedb.com

--000000000000851d89064e01053f
Content-Type: application/octet-stream;
	name="0001-pg_plan_advice-Avoid-assertion-failure-with-partitio.ncfbot"
Content-Disposition: attachment;
	filename="0001-pg_plan_advice-Avoid-assertion-failure-with-partitio.ncfbot"
Content-Transfer-Encoding: base64
Content-ID: <f_mn8wlqrx0>
X-Attachment-Id: f_mn8wlqrx0

RnJvbSBjOTQxZDU3ZjdiNzNjNjQxNjQyMjlmMTczZDk3ZmYxZjQwOWZiYjYwIE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBSb2JlcnQgSGFhcyA8cmhhYXNAcG9zdGdyZXNxbC5vcmc+CkRh
dGU6IEZyaSwgMjcgTWFyIDIwMjYgMDg6NDM6MTQgLTA0MDAKU3ViamVjdDogW1BBVENIXSBwZ19w
bGFuX2FkdmljZTogQXZvaWQgYXNzZXJ0aW9uIGZhaWx1cmUgd2l0aCBwYXJ0aXRpb253aXNlCiBh
Z2dyZWdhdGUuCgpBbiBBcHBlbmQgbm9kZSB0aGF0IGlzIHBhcnQgb2YgYSBwYXJ0aXRpb253aXNl
IGFnZ3JlZ2F0ZSBoYXMgbm8KYXBwcmVsaWRzLiBJZiBzdWNoIGEgbm9kZSB3YXMgZWxpZGVkLCB0
aGUgcHJldmlvdXMgY29kaW5nIHdvdWxkCmF0dGVtcHQgdG8gY2FsbCB1bmlxdWVfbm9uam9pbl9y
dGVraW5kKCkgb24gYSBOVUxMIHBvaW50ZXIsIHdoaWNoCmxlYWRzIHRvIGFuIGFzc2VydGlvbiBm
YWlsdXJlLiBJbnNlcnQgYSBOVUxMIGNoZWNrIHRvIHByZXZlbnQgdGhhdC4KClJlcG9ydGVkLWJ5
OiBBbGV4YW5kZXIgTGFraGluIDxleGNsdXNpb25AZ21haWwuY29tPgpEaXNjdXNzaW9uOiBodHRw
Oi8vcG9zdGdyLmVzL20vMGFmYmExY2UtYzk0Ni00MTMxLTk3MmQtMTkxZDlhMWMwOTdjQGdtYWls
LmNvbQotLS0KIGNvbnRyaWIvcGdfcGxhbl9hZHZpY2UvcGdwYV9zY2FuLmMgfCA3ICsrKysrKysK
IDEgZmlsZSBjaGFuZ2VkLCA3IGluc2VydGlvbnMoKykKCmRpZmYgLS1naXQgYS9jb250cmliL3Bn
X3BsYW5fYWR2aWNlL3BncGFfc2Nhbi5jIGIvY29udHJpYi9wZ19wbGFuX2FkdmljZS9wZ3BhX3Nj
YW4uYwppbmRleCA1ZjIxMGYyYjcyNS4uYzc5ZGI2ZmIxYjMgMTAwNjQ0Ci0tLSBhL2NvbnRyaWIv
cGdfcGxhbl9hZHZpY2UvcGdwYV9zY2FuLmMKKysrIGIvY29udHJpYi9wZ19wbGFuX2FkdmljZS9w
Z3BhX3NjYW4uYwpAQCAtNjgsOCArNjgsMTUgQEAgcGdwYV9idWlsZF9zY2FuKHBncGFfcGxhbl93
YWxrZXJfY29udGV4dCAqd2Fsa2VyLCBQbGFuICpwbGFuLAogCQkgKiBOb3RlIHRoYXQgdGhlIFBH
UEFfU0NBTl9QQVJUSVRJT05XSVNFIGNhc2UgYWxzbyBpbmNsdWRlcwogCQkgKiBwYXJ0aXRpb253
aXNlIGpvaW5zOyB0aGlzIG1vZHVsZSBjb25zaWRlcnMgdGhvc2UgdG8gYmUgYSBmb3JtIG9mCiAJ
CSAqIHNjYW4sIHNpbmNlIHRoZXkgbGFjayBpbnRlcm5hbCBzdHJ1Y3R1cmUgdGhhdCB3ZSBjYW4g
ZGVjb21wb3NlLgorCQkgKgorCQkgKiBOb3RlIGFsc28gdGhhdCBpdCdzIHBvc3NpYmxlIGZvciBy
ZWxpZHMgdG8gYmUgTlVMTCBoZXJlLAorCQkgKiBpZiB0aGUgZWxpZGVkIEFwcGVuZCBub2RlIGlz
IHBhcnQgb2YgYSBwYXJ0aXRpb253aXNlIGFnZ3JlZ2F0ZS4KKwkJICogSW4gdGhhdCBjYXNlLCBp
dCBkb2Vzbid0IG1hdHRlciB3aGF0IHN0cmF0ZWd5IHdlIGNob29zZSwgYnV0IHdlCisJCSAqIGRv
IG5lZWQgdG8gYXZvaWQgY2FsbGluZyB1bmlxdWVfbm9uam9pbl9ydGVraW5kKCksIHdoaWNoIHdv
dWxkIGZhaWwKKwkJICogYW4gYXNzZXJ0aW9uLgogCQkgKi8KIAkJaWYgKChub2RldHlwZSA9PSBU
X0FwcGVuZCB8fCBub2RldHlwZSA9PSBUX01lcmdlQXBwZW5kKSAmJgorCQkJcmVsaWRzICE9IE5V
TEwgJiYKIAkJCXVuaXF1ZV9ub25qb2luX3J0ZWtpbmQocmVsaWRzLAogCQkJCQkJCQkgICB3YWxr
ZXItPnBzdG10LT5ydGFibGUpID09IFJURV9SRUxBVElPTikKIAkJCXN0cmF0ZWd5ID0gUEdQQV9T
Q0FOX1BBUlRJVElPTldJU0U7Ci0tIAoyLjUxLjAKCg==
--000000000000851d89064e01053f--