Re: pg_*_advice: tsv load failure, etc.

On Thu, Sep 24, 2026 at 5:02 PM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> This is just a quick note to say I have seen these reports and am
> working on them. I hope to have a patch set by tomorrow.

Well, that certainly didn't happen. This has turned out to be
complicated. Here's a new patch set, which doesn't fix everything, but
fixes some things. The first three are fixes for brown-paper-bag bugs
that I discovered while investigating Nik's reports. The last is a fix
for the problem Ayush Tiwari reported back on September 10th. Briefly,
0001 fixes a goof in the introduction of child_append_relid_sets,
which was added to support pg_plan_advice, so the goof could result in
advice being wrong, but here I test it more directly via
pg_overexplain. 0002 fixes a problem where a variable was used for two
things but was the right value in only one of those places, and can
result in PARTITIONWISE advice going missing. 0003 fixes a problem
where NO_GATHER advice should be emitted for partitioned tables, and
isn't. And 0004 fixes Ayush's complaint that advice feedback for stuff
like JOIN_ORDER((a b)) shows up as only partially matched when in fact
it is fully matched. I plan to commit these patches and back-patch
them to v19 relatively quickly, barring objections.

There are more problems that aren't fixed. What Nik's report on
September 21st shows is that we sometimes generate plan advice for set
operations even though we're not able to enforce such advice for lack
of proper planner hooks. I spent a very long time trying to get that
case exactly right, but I'm not confident that I have managed to do
so, which is why there's no patch for that included here. Nik's report
on September 23rd is arguably not a bug at all. Generally, we don't
promise that supplying stupid advice won't mess up your plan, and
PARTITIONWISE(plain_table) is arguably stupid advice. On the other
hand, INDEX_SCAN(some_table no_such_index) won't mess up your plan
because we consider it *inapplicable* advice rather than *stupid*
advice, and that categorization causes it to not do anything. There's
a reasonable argument that PARTITIONWISE(plain_table) ought to be
treated similarly, although whether that rises to the level of being a
bug rather than an unimplemented feature seems pretty debatable.

There's also a residual problem with NO_GATHER(): after 0003, we no
longer omit it for partitioned tables, but we still include it for
their children, and that doesn't actually accomplish anything useful,
because we don't generate gather paths for individual children of a
partitionwise table, only for the partitioning root. In some ways,
this is similar to the set operation problem that Nik pointed out:
advice gets generated which is conceptually reasonable, but doesn't
make sense in practice. The reasons are different: for set operations,
we lack a hook to enforce it; for NO_GATHER on partition children,
enforcement is unnecessary because nothing else can happen.

On top of all that, I'm still looking into one more problem discovered
along the way as well, but I'm not ready to write about that, yet.

--
Robert Haas
Databricks