Selecting from JSONB field slow

Question

I have a relatively small table (~50k rows). When I select all records, it takes a ~40s. The table has 3 JSONB columns. When I select every column except for the JSONBs, the query takes ~700ms.

If I add in just one of the JSONB fields, the query time jumps to nearly 10s.

I'm never using a where clause referencing something inside the JSONB, just selecting *. Even so, I tried adding GIN indexes because I saw them frequently mentioned as a performance booster for JSONB.

I've ran a full vacuum.

Postgres version 9.6

 explain (analyze, buffers) select * from message;
   Seq Scan on message  (cost=0.00..5541.69 rows=52969 width=834) (actual 
   time=1.736..116.183 rows=52969 loops=1)
     Buffers: shared hit=64 read=4948
   Planning time: 0.151 ms
   Execution time: 133.555 ms

Pavel Stehule Pavel Stehule · Accepted Answer · 2018-09-07T04:17:06

Jsonb is PostgreSQL varlena data type - that means so when the value is longer than 2KB, then it is stored in auxiliary table (named TOAST table). A pointer to TOAST table is stored in main table. So when you don't touch Jsonb column, then this value is not read.

GIN index doesn't help in this case. It helps just for searching.

10sec on 50K values is long time - maybe your Jsonb values are pretty long, or your IO system doesn't perform well. Please, check size of your table, and check the performance of your IO. The cheap cloud machines usually has terrible IO.

Another possible reason of slowdown is a complexity of Jsonb data type. Jsonb is serialized tree of json sub objects. If you don't need some special features of Jsonb data type, then use JSON data type. This is just test (JSON format is checked on input only). The output of JSONB is faster than Jsonb, because JSON is internally text, and there are not any operations necessary. Output of Jsonb should be serialized, what is more expensive.

Selecting from JSONB field slow

1 Answers