Re: SQL/JSON json_table plan clause

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.

------
Regards,
Alexander Korotkov
Supabase