Re: SQL/JSON json_table plan clause

On Tue, 14 Jul 2026 at 23:32, Alexander Korotkov <aekorotkov(at)gmail(dot)com> wrote:
>
> On Tue, Jul 14, 2026 at 6:44 PM Thom Brown <thom(at)linux(dot)com> wrote:
> >
> > On Mon, 13 Jul 2026 at 14:30, Thom Brown <thom(at)linux(dot)com> wrote:
> > >
> > > 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.
> >
> > Also, shouldn't this have been marked as committed in the current CF?
>
> Pushed, thank you.
> And the patch is marked as committed.

Thanks.

I've been trying to break it further in the meantime. I haven't
managed to get it to crash or return incorrect results.

But I did manage to get it to blow up in memory consumption.

postgres=# SET track_io_timing = on;

CREATE TEMP TABLE onedoc AS
SELECT jsonb_build_array(jsonb_build_object('arr', arr)) AS js

FROM (
SELECT jsonb_agg('str_' || lpad(x::text, 20, '0')) AS arr
FROM generate_series(1, 2500) x) a;
SET
SELECT 1
postgres=# SELECT pg_backend_pid();
pg_backend_pid
----------------
281748
(1 row)

postgres=# EXPLAIN (ANALYSE, IO, MEMORY, SERIALIZE BINARY, SETTINGS,
SUMMARY, VERBOSE, WAL)
SELECT count(*)
FROM JSON_TABLE((SELECT js FROM onedoc), '$[*]' AS p0
COLUMNS (NESTED PATH '$.arr[*]' AS pa COLUMNS (a text PATH '$'),
NESTED PATH '$.arr[*]' AS pb COLUMNS (b text PATH '$'))
PLAN (p0 INNER (pa CROSS pb))
) jt;

QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=54.06..54.09 rows=1 width=8) (actual
time=7843.279..7843.281 rows=1.00 loops=1)
Output: count(*)
Buffers: shared hit=23, local hit=5 written=1, temp read=45777 written=45777
I/O Timings: local write=0.060, temp read=91.437 write=237.507
InitPlan expr_1
-> Seq Scan on pg_temp.onedoc (cost=0.00..50.80 rows=1360
width=32) (actual time=0.019..0.020 rows=1.00 loops=1)
Output: onedoc.js
Prefetch: avg=1.00 max=1 capacity=256
Buffers: local hit=1
-> Table Function Scan on "json_table" jt (cost=0.01..3.01
rows=100 width=0) (actual time=7164.812..7614.681 rows=6250000.00
loops=1)
Output: jt.a, jt.b
Table Function Call: JSON_TABLE((InitPlan expr_1).col1,
'$[*]' AS p0 COLUMNS ( NESTED PATH '$."arr"[*]' AS pa COLUMNS (a text
PATH '$'), NESTED PATH '$."arr"[*]' AS pb COLUMNS (b text PATH '$'))
PLAN (p0 INNER (pa CROSS pb)))
Storage: Disk Maximum Storage: 366211kB
Buffers: shared hit=23, local hit=5 written=1, temp
read=45777 written=45777
I/O Timings: local write=0.060, temp read=91.437 write=237.507
Settings: work_mem = '100MB', random_page_cost = '1.1',
cpu_tuple_cost = '0.03', effective_cache_size = '6GB'
Planning:
Buffers: shared hit=8
Memory: used=19kB allocated=32kB
Planning Time: 0.273 ms
Serialization: time=0.009 ms output=1kB format=binary
Execution Time: 7931.529 ms
(22 rows)

$ grep VmHWM /proc/281748/status
VmHWM: 7031316 kB

Thom