Re: SQL/JSON json_table plan clause

On Sun, 12 Jul 2026 at 18:17, Alexander Korotkov <aekorotkov(at)gmail(dot)com> wrote:
>
> On Sat, Jul 11, 2026 at 10:30 PM Thom Brown <thom(at)linux(dot)com> wrote:
> > On Sat, 11 Jul 2026 at 13:19, Alexander Korotkov <aekorotkov(at)gmail(dot)com> wrote:
> > > On Fri, Jul 10, 2026 at 9:00 PM Thom Brown <thom(at)linux(dot)com> wrote:
> > > > I've tested patches 0001-0003.
> > >
> > > Thank you for testing the patchset.
> > >
> > > > 0001 - the ERROR ON ERROR query returns a NULL row again, so it now
> > > > matches the docs. ERROR ON EMPTY is now output in pg_dump
> > > > too.paragraph is accurate again.
> > > >
> > > > 0002 - PLAN (p0 OUTER (p1 INNER p11)) is now preserving parentheses,
> > > > including a three-level (p1 INNER (p11 INNER p111)) chain and the
> > > > mixed version (p0 OUTER ((p1 INNER p11) UNION p2)). Schema-only
> > > > dump/restore of all test views work.
> > >
> > > Good.
> > >
> > > > 0003 - the silent column-drop now correctly errors with "PLAN clause
> > > > for nested path json_table_path_1 was not found".
> > > >
> > > > However, I am getting a new error:
> > > >
> > > > postgres=# SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS
> > > > (json_table_path_0 int PATH '$')) jt;
> > > > ERROR: duplicate JSON_TABLE column or path name: json_table_path_0
> > > > LINE 1: SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (json_table_...
> > > >
> > > > So I think patch 0003 needs a new version because it claims "a
> > > > generated name can no longer coincide with a user-supplied one"
> > >
> > > Thank you for catching this. 0003 is revised.
> > >
> > > > I also did a few performance tests. I've noticed that JSON_TABLE
> > > > queries using NESTED PATH have become noticeably slower since this
> > > > commit, even when no PLAN clause is used.
> > > >
> > > > Here's what I get prior to this commit:
> > > >
> > > > postgres=# \timing
> > > > Timing is on.
> > > > postgres=# CREATE TEMP TABLE doc AS
> > > > SELECT (SELECT jsonb_agg(jsonb_build_object('a', i,
> > > > 'b', (SELECT jsonb_agg(j) FROM generate_series(1,1000) j)))
> > > > FROM generate_series(1,1000) i) AS j;
> > > > SELECT 1
> > > > Time: 195.441 ms
> > > > postgres=# SELECT count(*) FROM doc, JSON_TABLE(doc.j, '$[*]'
> > > > COLUMNS (a int PATH '$.a',
> > > > NESTED PATH '$.b[*] ? (@ < 5)' COLUMNS (v int PATH '$'))) jt;
> > > > count
> > > > -------
> > > > 4000
> > > > (1 row)
> > > >
> > > > Time: 150.463 ms
> > > > postgres=# SELECT count(*) FROM doc, JSON_TABLE(doc.j, '$[*]'
> > > > COLUMNS (a int PATH '$.a',
> > > > NESTED PATH '$.b[*] ? (@ < 5)' COLUMNS (v int PATH '$'))) jt;
> > > > count
> > > > -------
> > > > 4000
> > > > (1 row)
> > > >
> > > > Time: 146.457 ms
> > > > postgres=# SELECT count(*) FROM doc, JSON_TABLE(doc.j, '$[*]'
> > > > COLUMNS (a int PATH '$.a',
> > > > NESTED PATH '$.b[*] ? (@ < 5)' COLUMNS (v int PATH '$'))) jt;
> > > > count
> > > > -------
> > > > 4000
> > > > (1 row)
> > > >
> > > > Time: 147.257 ms
> > > >
> > > > And post-commit:
> > > >
> > > > postgres=# CREATE TEMP TABLE doc AS
> > > > SELECT (SELECT jsonb_agg(jsonb_build_object('a', i,
> > > > 'b', (SELECT jsonb_agg(j) FROM generate_series(1,1000) j)))
> > > > FROM generate_series(1,1000) i) AS j;
> > > > SELECT 1
> > > > postgres=# \timing
> > > > Timing is on.
> > > > postgres=# SELECT count(*) FROM doc, JSON_TABLE(doc.j, '$[*]'
> > > > COLUMNS (a int PATH '$.a',
> > > > NESTED PATH '$.b[*] ? (@ < 5)' COLUMNS (v int PATH '$'))) jt;
> > > > count
> > > > -------
> > > > 4000
> > > > (1 row)
> > > >
> > > > Time: 536.018 ms
> > > > postgres=# SELECT count(*) FROM doc, JSON_TABLE(doc.j, '$[*]'
> > > > COLUMNS (a int PATH '$.a',
> > > > NESTED PATH '$.b[*] ? (@ < 5)' COLUMNS (v int PATH '$'))) jt;
> > > > count
> > > > -------
> > > > 4000
> > > > (1 row)
> > > >
> > > > Time: 526.044 ms
> > > > postgres=# SELECT count(*) FROM doc, JSON_TABLE(doc.j, '$[*]'
> > > > COLUMNS (a int PATH '$.a',
> > > > NESTED PATH '$.b[*] ? (@ < 5)' COLUMNS (v int PATH '$'))) jt;
> > > > count
> > > > -------
> > > > 4000
> > > > (1 row)
> > > >
> > > > Time: 582.042 ms
> > > >
> > > > This gives an average of 148.059 pre-commit vs.548.035ms post-commit,
> > > > a 3.7 x slowdown.
> > > >
> > > > This seems to grow with the size of the nested arrays., With small
> > > > ones the is only around 10%.
> > > >
> > > > Is this expected?
> > >
> > > No, it's no expected. There is problem of multiple evaluation of
> > > nested paths. 0005 fixes that.
> >
> > I've tested all patches, and all reported issues now resolved. The
> > regression is also completely eliminated. Thanks!
> >
> > Is there any use for errdetail("PATH name was %s not found in nested
> > columns list.") anymore? I don't think we need it, but if for some
> > reason we do, the wording needs fixing.
>
> Yes, no need for this ERROR. This is checked before, replaced with an
> assert in 0006.
>
> > Also, in 0001, the dumping half of the fix doesn't seem to have a
> > test. There's a new test for the runtime side, and json_table_view9
> > covers the redundant NULL ON EMPTY, but nothing would fail if a dumped
> > view started losing an explicit ERROR ON EMPTY again. Maybe something
> > like this next to the other view tests:
> >
> > CREATE VIEW jsonb_table_view_onempty AS
> > SELECT * FROM JSON_TABLE(jsonb '{}', '$' AS p0
> > COLUMNS (a int PATH '$.nosuch' ERROR ON EMPTY)
> > ERROR ON ERROR);
> > \sv jsonb_table_view_onempty
> > DROP VIEW jsonb_table_view_onempty;
> >
> > The \sv output should keep the ERROR ON EMPTY clause.
>
> New test is added to 0001.

All issues I raised are resolved by these patches, so all good from my side.

Thom