Re: SQL/JSON json_table plan clause

On Sat, 11 Jul 2026 at 13:19, Alexander Korotkov <aekorotkov(at)gmail(dot)com> wrote:
>
> Hi, Thom!
>
> 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.

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.

Regards

Thom