Performance of JSON_TABLE vs jsonb_to_recordset

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

Responses

Browse pgsql-hackers by date

  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.