Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1aPHD9-00043f-5R for pgsql-performance@arkaria.postgresql.org; Fri, 29 Jan 2016 22:08:11 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1aPHD8-0002Le-OG for pgsql-performance@arkaria.postgresql.org; Fri, 29 Jan 2016 22:08:10 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1aPHBT-0000Wo-Ic for pgsql-performance@postgresql.org; Fri, 29 Jan 2016 22:06:27 +0000 Received: from mailout98.mail01.mtsvc.net ([216.70.64.191] helo=n62.mail01.mtsvc.net) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1aPHBQ-0004io-Ce for pgsql-performance@postgresql.org; Fri, 29 Jan 2016 22:06:26 +0000 Received: from localhost ([127.0.0.1]:38283) by n62.mail01.mtsvc.net with esmtp (Exim 4.72) (envelope-from ) id 1aPHBP-0008Cq-8B for pgsql-performance@postgresql.org; Fri, 29 Jan 2016 17:06:23 -0500 Message-Id: From: jfleming@kispring.com To: pgsql-performance@postgresql.org X-Mailer: Atmail 6.6.1.11564 X-Originating-IP: 69.165.165.40 Subject: jsonb_agg performance Date: Fri, 29 Jan 2016 14:06:23 -0800 Content-Type: multipart/alternative; boundary="=_d2d25063984e9c2fd17464edba32ed22" MIME-Version: 1.0 X-Spam-Level: * X-Spam-Report: score=1.0 tests=HTML_MESSAGE, RDNS_NONE version=3.4.0 cmae=v=2.1 cv=BJCK8jgG c=1 sm=0 tr=0 a=5bZXT6ac67UA:10 a=7aQ_Q-yQQ-AA:10 a=uemh45fB0_TJ_vEII-cA:9 a=QEXdDO2ut3YA:10 a=elwZDb7y-gOjVyNnwKgA:9 a=iHG5bin_MR2wP1H0:21 a=_W_S_7VecoQA:10 X-Spam-Score: 1 X-MT-ID: DA39A3EE5E6B4B0D3255BFEF95601890AFD80709 X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --=_d2d25063984e9c2fd17464edba32ed22 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable The jsonb_agg function seems to have significantly worse performance=0At= han its json_agg counterpart:=0A=0A=3D> explain analyze select pa.produc= t_id, jsonb_agg(attributes) from=0Aproduct_attributes2 pa group by pa.pr= oduct_id;=0A=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=0AQUERY PLAN=0A--------------------------------------= ------------------------------------------------------------------------= ------------------------=0A=C2=A0GroupAggregate=C2=A0 (cost=3D1127.54..1= 231.62 rows=3D3046 width=3D380) (actual=0Atime=3D28.632..241.647 rows=3D= 3046 loops=3D1)=0A=C2=A0=C2=A0 Group Key: product_id=0A=C2=A0=C2=A0 ->= =C2=A0 Sort=C2=A0 (cost=3D1127.54..1149.54 rows=3D8800 width=3D380) (act= ual=0Atime=3D28.526..32.826 rows=3D8800 loops=3D1)=0A=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Sort Key: product_id=0A=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Sort Method: external sort=C2=A0 Disk: 33= 60kB=0A=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ->=C2=A0 Seq Sca= n on product_attributes2 pa=C2=A0=0A(cost=3D0.00..551.00 rows=3D8800 wid= th=3D380) (actual time=3D0.010..7.231=0Arows=3D8800 loops=3D1)=0A=C2=A0P= lanning time: 0.376 ms=0A=C2=A0Execution time: 242.963 ms=0A(8 rows)=0A= =0A=3D> explain analyze select pa.product_id, json_agg(attributes) from= =0Aproduct_attributes3 pa group by pa.product_id;=0A=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=0AQUERY PLA= N=0A--------------------------------------------------------------------= ------------------------------------------------------------------=0A=C2= =A0GroupAggregate=C2=A0 (cost=3D1136.54..1240.62 rows=3D3046 width=3D387= ) (actual=0Atime=3D17.731..30.126 rows=3D3046 loops=3D1)=0A=C2=A0=C2=A0= Group Key: product_id=0A=C2=A0=C2=A0 ->=C2=A0 Sort=C2=A0 (cost=3D1136.5= 4..1158.54 rows=3D8800 width=3D387) (actual=0Atime=3D17.707..20.705 rows= =3D8800 loops=3D1)=0A=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 So= rt Key: product_id=0A=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 So= rt Method: external sort=C2=A0 Disk: 3416kB=0A=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0 ->=C2=A0 Seq Scan on product_attributes3 pa=C2= =A0=0A(cost=3D0.00..560.00 rows=3D8800 width=3D387) (actual time=3D0.006= .5.568=0Arows=3D8800 loops=3D1)=0A=C2=A0Planning time: 0.181 ms=0A=C2= =A0Execution time: 31.276 ms=0A(8 rows)=0A=0AThe only difference between= the two tables is the type of the=0Aattributes column (jsonb vs json).= =C2=A0 Each table contains the same 8800=0Arows.=C2=A0 Even running json= _agg on the jsonb column seems to be faster:=0A=0A=3D> explain analyze s= elect pa.product_id, json_agg(attributes) from=0Aproduct_attributes2 pa= group by pa.product_id;=0A=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=0AQUERY PLAN=0A-----------------------= ------------------------------------------------------------------------= ---------------------------------------=0A=C2=A0GroupAggregate=C2=A0 (co= st=3D1127.54..1231.62 rows=3D3046 width=3D380) (actual=0Atime=3D30.626..= 62.943 rows=3D3046 loops=3D1)=0A=C2=A0=C2=A0 Group Key: product_id=0A=C2= =A0=C2=A0 ->=C2=A0 Sort=C2=A0 (cost=3D1127.54..1149.54 rows=3D8800 width= =3D380) (actual=0Atime=3D30.590..34.157 rows=3D8800 loops=3D1)=0A=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Sort Key: product_id=0A=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Sort Method: external sort=C2= =A0 Disk: 3360kB=0A=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ->= =C2=A0 Seq Scan on product_attributes2 pa=C2=A0=0A(cost=3D0.00..551.00 r= ows=3D8800 width=3D380) (actual time=3D0.014..7.388=0Arows=3D8800 loops= =3D1)=0A=C2=A0Planning time: 0.142 ms=0A=C2=A0Execution time: 64.504 ms= =0A(8 rows)=0A=0AIs it expected that jsonb_agg performance would be that= much worse=0Athan json_agg? --=_d2d25063984e9c2fd17464edba32ed22 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable The jsonb_agg function seems to have significan= tly worse performance than its json_agg counterpart:

=3D>= explain analyze select pa.product_id, jsonb_agg(attributes) from produc= t_attributes2 pa group by pa.product_id;
=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 QUERY PLAN
-= ------------------------------------------------------------------------= -------------------------------------------------------------
=C2= =A0GroupAggregate=C2=A0 (cost=3D1127.54..1231.62 rows=3D3046 width=3D380= ) (actual time=3D28.632..241.647 rows=3D3046 loops=3D1)
=C2=A0=C2= =A0 Group Key: product_id
=C2=A0=C2=A0 ->=C2=A0 Sort=C2=A0 (cost= =3D1127.54..1149.54 rows=3D8800 width=3D380) (actual time=3D28.526..32.8= 26 rows=3D8800 loops=3D1)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0 Sort Key: product_id
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0 Sort Method: external sort=C2=A0 Disk: 3360kB
=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=0A ->=C2=A0 Seq Scan on pr= oduct_attributes2 pa=C2=A0 (cost=3D0.00..551.00 rows=3D8800=0A width=3D3= 80) (actual time=3D0.010..7.231 rows=3D8800 loops=3D1)
=C2=A0Planni= ng time: 0.376 ms
=C2=A0Execution time: 242.963 ms
(8 rows)
=3D> explain analyze select pa.product_id, json_agg(attribut= es) from product_attributes3 pa group by pa.product_id;
=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 QU= ERY PLAN
----------------------------------------------------------= ------------------------------------------------------------------------= ----
=C2=A0GroupAggregate=C2=A0 (cost=3D1136.54..1240.62 rows=3D304= 6 width=3D387) (actual time=3D17.731..30.126 rows=3D3046 loops=3D1)
=C2=A0=C2=A0 Group Key: product_id
=C2=A0=C2=A0 ->=C2=A0 Sort= =C2=A0 (cost=3D1136.54..1158.54 rows=3D8800 width=3D387) (actual time=3D= 17.707..20.705 rows=3D8800 loops=3D1)
=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 Sort Key: product_id
=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0 Sort Method: external sort=C2=A0 Disk: 3416kB=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=0A ->=C2=A0 Seq= Scan on product_attributes3 pa=C2=A0 (cost=3D0.00..560.00 rows=3D8800= =0A width=3D387) (actual time=3D0.006..5.568 rows=3D8800 loops=3D1)
=C2=A0Planning time: 0.181 ms
=C2=A0Execution time: 31.276 ms
(8 rows)

The=0A only difference between the two tables is th= e type of the attributes =0Acolumn (jsonb vs json).=C2=A0 Each table con= tains the same 8800 rows.=C2=A0 Even =0Arunning json_agg on the jsonb co= lumn seems to be faster:

=3D> explain analyze select pa.pr= oduct_id, json_agg(attributes) from product_attributes2 pa group by pa.p= roduct_id;
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0 QUERY PLAN
-------------------------------= ------------------------------------------------------------------------= -------------------------------
=C2=A0GroupAggregate=C2=A0 (cost=3D= 1127.54..1231.62 rows=3D3046 width=3D380) (actual time=3D30.626..62.943= rows=3D3046 loops=3D1)
=C2=A0=C2=A0 Group Key: product_id
=C2= =A0=C2=A0 ->=C2=A0 Sort=C2=A0 (cost=3D1127.54..1149.54 rows=3D8800 wi= dth=3D380) (actual time=3D30.590..34.157 rows=3D8800 loops=3D1)
=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Sort Key: product_id
= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Sort Method: external s= ort=C2=A0 Disk: 3360kB
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=0A ->=C2=A0 Seq Scan on product_attributes2 pa=C2=A0 (cost=3D0= 00..551.00 rows=3D8800=0A width=3D380) (actual time=3D0.014..7.388 rows= =3D8800 loops=3D1)
=C2=A0Planning time: 0.142 ms
=C2=A0Executi= on time: 64.504 ms
(8 rows)

Is it expected that jsonb_ag= g performance would be that much worse than json_agg? --=_d2d25063984e9c2fd17464edba32ed22--