Hi, Thom! On Fri, Jul 10, 2026 at 9:00 PM Thom Brown <[email protected]> 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 difference is only around 10%.
>
> Is this expected?
No, it's no expected. There is problem of multiple evaluation of
nested paths. 0005 fixes that.
------
Regards,
Alexander Korotkov
Supabase
v3-0001-Revert-cascading-of-JSON_TABLE-s-ON-ERROR.patch
Description: Binary data
v3-0003-Make-JSON_TABLE-generated-path-names-avoid-collis.patch
Description: Binary data
v3-0004-Fix-and-polish-JSON_TABLE-documentation.patch
Description: Binary data
v3-0002-Fix-JSON_TABLE-PLAN-deparse-to-keep-parentheses-a.patch
Description: Binary data
v3-0005-Avoid-redundant-re-evaluation-of-JSON_TABLE-neste.patch
Description: Binary data
