Re: pg_plan_advice

On Sun, Apr 5, 2026 at 8:00 AM Alexander Lakhin <exclusion(at)gmail(dot)com> wrote:
> And another error, which might be interesting to you:
> CREATE EXTENSION tsm_system_time;
> CREATE TABLE t(i int);
> SELECT 1 FROM (SELECT i FROM t TABLESAMPLE system_time (1000)), LATERAL (SELECT i LIMIT 1);
>
> ERROR: XX000: plan node has no RTIs: 378
> LOCATION: pgpa_build_scan, pgpa_scan.c:200

Thanks also for this report. The plan looks like this:

Nested Loop (cost=0.00..154.75 rows=2550 width=4)
-> Materialize (cost=0.00..78.25 rows=2550 width=4)
-> Sample Scan on t (cost=0.00..65.50 rows=2550 width=4)
Sampling: system_time ('1000'::double precision)
-> Limit (cost=0.00..0.01 rows=1 width=4)
-> Result (cost=0.00..0.01 rows=1 width=4)

And it's unhappy because it's expecting the Materialize node to be the
RTI-bearing node. In a turn of events that will probably shock nobody
here, I also didn't quite realize that a Materialize node could get
inserted here. It's kind of a problem, too, because what if the sides
of the join were switched? Then we'd have a Nested Loop with an inner
Materialize node and would conclude that the strategy was
PGS_NESTLOOP_MATERIALIZE, when in reality it would be
PGS_NESTLOOP_PLAIN plus a Materialize node inserted at the scan level,
so the generated advice would be incorrect. I guess the fix is
probably to view a Materialize node on top of a Sample Scan for a
!repeatable_across_scans tsmhandler as part of the scan, which is kind
of annoying but probably doable. Not for the first time, I really wish
we stored an RTI set in every plan node, or (maybe more economically)
had some kind of enum in key plan nodes indicating why the node was
inserted. Right now, pg_plan_advice does a lot of reading the tea
leaves, which is great in that it avoids bloating Plan trees with
additional metadata, but a little scary in terms of being able to be
certain that one will get the right answer reliably.

I'll work on a fix.

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