public inbox for [email protected]
help / color / mirror / Atom feedFrom: Robert Haas <[email protected]>
To: Alexander Lakhin <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: Lukas Fittl <[email protected]>
Cc: Andrei Lepikhov <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: pg_plan_advice
Date: Mon, 6 Apr 2026 16:15:21 -0400
Message-ID: <CA+Tgmoa2n_zOZgp3a7dqST6Fvv856-CsFtHh9QOvpZe6hKYEzQ@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CA+TgmoZ-Jh1T6QyWoCODMVQdhTUPYkaZjWztzP1En4=ZHoKPzw@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>
<[email protected]>
<CA+TgmoadkuOMJjvYe2h6aznHFeePprGEQ8CgUpRK=47sB6DMAg@mail.gmail.com>
<[email protected]>
<CA+TgmoY+g1u-fN=3igXG-8u0Ho3V4u-ooWXCj-FQ9DA=uGek9g@mail.gmail.com>
<[email protected]>
<[email protected]>
<CA+Tgmoben3_8rZbQ2X2+gOBOFpOgkc9hx3-z9e_Q_kHCfuW25g@mail.gmail.com>
<[email protected]>
<CA+TgmoaPgXYYEivQWxyVV=eYhN+T9JAgS9Xe4m7g9wVitVPF8g@mail.gmail.com>
<[email protected]>
<[email protected]>
On Sun, Apr 5, 2026 at 8:00 AM Alexander Lakhin <[email protected]> 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
view thread (184+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: pg_plan_advice
In-Reply-To: <CA+Tgmoa2n_zOZgp3a7dqST6Fvv856-CsFtHh9QOvpZe6hKYEzQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox