To: pgsql-performance@lists.postgresql.org
Reply-To: jesper.pedersen@redhat.com
From: Jesper Pedersen <jesper.pedersen@redhat.com>
Subject: JSON path
Organization: Red Hat
Message-ID: <71a1d71e-5263-2980-117c-066a37e481bc@redhat.com>
Date: Thu, 14 Nov 2019 08:55:28 -0500
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101
 Thunderbird/68.1.1
MIME-Version: 1.0
Content-Language: en-US
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: quoted-printable
Precedence: bulk

Hi,

We have a table which has a jsonb column in it. Each row contains a lot=20
of data in that column, so TOASTed.

We have to extract data from that column at different levels, so an=20
example query could look like

select
   col1,
   col2,=20
jsonb_path_query_first(data,'$.lvl1.lvl2.lvl3.lvl4.lvl5.val1."text()"')=20
as val1,
jsonb_path_query_first(data,'$.lvl1.lvl2.lvl3.lvl4.lvl5.val2."text()"')=20
as val2,
jsonb_path_query_first(data,'$.lvl1.lvl2.lvl3.lvl4.lvl5.val3."text()"')=20
as val3
from tbl
where
   id =3D 1;

I tried to rewrite it to

WITH foo AS (select
   id,
   col1,
   col2,
jsonb_path_query_first(data,'$.lvl1.lvl2.lvl3.lvl4.lvl5') as jsondata,
   from tbl )
select
   col1,
   col2,
   jsondata->val1->'text()' as val1,
   jsondata->val2->'text()' as val2,
   jsondata->val3->'text()' as val3
from foo
where
   id =3D 1;

However, WITH has the same run-time profile - most of the time is spent=20
in pglz_decompress. Using the -> notation has the same profile.

The more data I extract from the JSON object the slower the query gets.

Of course, if I change the column to EXTERNAL we see a ~3.5 x speedup in=20
the queries but disk space requirements goes up by too much.

(We need to use a jsonb column as the data is unstructured, and may=20
differ in structure between rows. Yes, yes, I know...)

PostgreSQL 12.x on RHEL.

If anybody has some good ideas it would be appreciated.

Thanks in advance !

Best regards,
  Jesper