Postgresql query is very slow when selecting a key from a JSONB field object

Question

When you include a key from an object stored in my bodyJSONB field , the request runs ~ 100 ms slower than without a field in the selection element:

SELECT id, 
       title,
       body->'_stats' AS stats
  FROM items

This takes about 105 ms compared to 5 ms when the selection is statsnot enabled. It doesn't seem to matter which key I return from the JSONB object body, they all slow down the request significantly. There are other keys from the body object that I would like to include in this request, but each of them that I add increases the total request time by ~ 50 ms

The field bodyhas a gin index, and I see the behavior in both PG v9.5x and v9.6.1

Any suggestions on alternative ways to return jsonb object data more efficiently?

Answer 1

The first line in the introduction of the JSON standard reads (my emphasis):

JSON is a text format that facilitates structured data exchange between all programming languages.

In short, JSON is good for sending and receiving information from other components of your application, but this is not a format related to databases in general and completely alien to the relational data model, and therefore not particularly fast, but you want to approach it.

If you just store and load full JSON documents, then there is no problem, but as soon as you start manipulating JSON documents in the DBMS, and speed is a problem, you should normalize the data json.