Re: SQL/JSON json_table plan clause

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 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