Slow access to JSONB column [closed]

Question

I have the following query that accesses a JSONB column in the table datasets:

SELECT count(*), my_jsonb_column->0->'PARAM1'
FROM datasets
GROUP BY my_jsonb_column->0->'PARAM1' 

The explain analyze output is

HashAggregate  (cost=4778.92..5577.57 rows=53243 width=40) (actual time=1189.083..1189.135 rows=10 loops=1)
  Group Key: ((my_jsonb_column -> 0) -> 'PARAM1'::text)
  ->  Seq Scan on datasets  (cost=0.00..4510.44 rows=53696 width=32) (actual time=0.052..1165.469 rows=53696 loops=1)
Planning time: 0.081 ms
Execution time: 1189.242 ms

A similar query that uses a plain column is roughly 50x faster, with a pretty much identical query plan also using a sequential scan.

The JSONB column contains an array with typically 1-3 elements, each one an object containing between 10 to 300 keys.

I expected the JSONB version to be significantly slower than the plain column version, but I didn't expect it to be that slow. Am I doing something particularly inefficient here?

Is there anything I can do to speed up this kind of access to a JSONB column? I'll need to access different keys, not just one specific one.

Does the size of the JSONB column play a role here? I would have assumed that this kind of access to a specific key should be mostly independent of the column size, but I'm not entirely sure.

Answer 1

The JSONB object is probably large enough that it is compressed and stored out of line, in a TOAST table. Having to retrieve and decompress the TOASTed value for every row is going to slow things down by a lot.

I just wouldn't use JSON or JSONB for this purpose if I wanted speed. If you need to access a small number of keys, pulling them out into the table proper could help.

Answer 2

Just for fun, sample data..

CREATE TABLE datasets
AS 

    SELECT
      gs.rowid,
      jsonb_agg(
        jsonb_build_object('PARAM'||keys.id, md5(gs.rowid::text))
      ) AS jsondata,
      md5(gs.rowid::text) AS myparam
    FROM generate_series(1,53696) AS gs(rowid)
    CROSS JOIN LATERAL (
      SELECT gs.rowid, trunc(random()*3)::int+1 AS elemcnt
    ) AS r1
    CROSS JOIN LATERAL (
      SELECT gs.rowid, elemcnt, trunc(random()*290)::int+10 AS keycnt
    ) AS r2
    CROSS JOIN LATERAL generate_series(1,r1.elemcnt) AS elems(id)
    CROSS JOIN LATERAL generate_series(1,r2.keycnt)  AS keys(id)
    GROUP BY gs.rowid;

Then we do...

SELECT rowid FROM datasets WHERE myparam = '5ecc613150de01b7e6824594426f24f4';
                                              QUERY PLAN                                               
-------------------------------------------------------------------------------------------------------
 Seq Scan on datasets  (cost=0.00..5975.20 rows=1 width=4) (actual time=18.760..19.527 rows=1 loops=1)
   Filter: (myparam = '5ecc613150de01b7e6824594426f24f4'::text)
   Rows Removed by Filter: 53695
 Planning time: 0.089 ms
 Execution time: 19.557 ms
(5 rows)

SELECT rowid, jsondata->0->'PARAM1' FROM datasets WHERE jsondata->0->>'PARAM1' = '5ecc613150de01b7e6824594426f24f4';
                                                 QUERY PLAN                                                  
-------------------------------------------------------------------------------------------------------------
 Seq Scan on datasets  (cost=0.00..6245.02 rows=268 width=684) (actual time=761.555..809.514 rows=1 loops=1)
   Filter: (((jsondata -> 0) ->> 'PARAM1'::text) = '5ecc613150de01b7e6824594426f24f4'::text)
   Rows Removed by Filter: 53695
 Planning time: 0.078 ms
 Execution time: 809.545 ms
(5 rows)

So the overhead on the whole operation is 41.3x Now just add a gist index.

Adding an index

CREATE INDEX ON datasets USING gin(jsondata jsonb_path_ops);

SELECT rowid, jsondata->0->'PARAM1' FROM datasets WHERE jsondata @> '[{"PARAM1":"5ecc613150de01b7e6824594426f24f4"}]'::jsonb;

                                                           QUERY PLAN                                                           
--------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on datasets  (cost=24.42..225.02 rows=54 width=684) (actual time=0.405..0.408 rows=1 loops=1)
   Recheck Cond: (jsondata @> '[{"PARAM1": "5ecc613150de01b7e6824594426f24f4"}]'::jsonb)
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on datasets_jsondata_idx  (cost=0.00..24.40 rows=54 width=0) (actual time=0.082..0.082 rows=1 loops=1)
         Index Cond: (jsondata @> '[{"PARAM1": "5ecc613150de01b7e6824594426f24f4"}]'::jsonb)
 Planning time: 0.184 ms
 Execution time: 0.460 ms
(7 rows)

And, now we're 42x faster than with the seq scan on the "plain column".

I'm not sure where your question is though. TOAST adds over head. So does indexing into binary JSON and converting from the JSONB type to text.