From: | Alexander Lakhin <exclusion(at)gmail(dot)com> |
---|---|
To: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Performance of JSON_TABLE vs jsonb_to_recordset |
Date: | 2024-04-20 11:00:00 |
Message-ID: | 5ad26948-7c82-fad2-139a-25bba5361201@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hello hackers,
When playing with JSON_TABLE, I tried to replace tenk1 in regression tests
with a view based on JSON_TABLE, with the same content, and discovered
that for one sub-optimal query it's execution duration increased many-fold.
With the preparation script attached, I see the following durations
(for a build compiled by clang 18.1.3 with -O3):
explain (verbose, analyze)
select
(select max((select i.unique2 from tenk1 i where i.unique1 = o.unique1)))
from tenk1 o;
-- original tenk1
Execution Time: 4769.481 ms
explain (verbose, analyze)
select
(select max((select i.unique2 from jsonb_rs_tenk1 i where i.unique1 = o.unique1)))
from jsonb_rs_tenk1 o;
-- Function Call: jsonb_to_recordset...
Execution Time: 6841.767 ms
explain (verbose, analyze)
select
(select max((select i.unique2 from jsontable_tenk1 i where i.unique1 = o.unique1)))
from jsontable_tenk1 o;
-- Table Function Call: JSON_TABLE...
Execution Time: 288310.131 ms
(with 63% of time spent inside ExecEvalJsonExprPath())
Just for fun I've tested also XMLTABLE with the similar content:
explain (verbose, analyze)
select
(select max((select i.unique2 from xml_tenk1 i where i.unique1 = o.unique1)))
from xml_tenk1 o;
-- Table Function Call: XMLTABLE...
Execution Time: 1235066.636 ms
Maybe it's worth to add a note to the JSON_TABLE() documentation saying that
jsonb_to_recordset is (inherently?) more performant when processing arrays
of flat structures for users not to re-discover this fact...
Best regards,
Alexander
Attachment | Content-Type | Size |
---|---|---|
jsontable-perf-setup.sql | application/sql | 2.6 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Alena Rybakina | 2024-04-20 12:00:53 | Re: Fix parallel vacuum buffer usage reporting |
Previous Message | Xing Guo | 2024-04-20 10:21:38 | Re: Remove deprecated header file resowner_private.h. |