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

Question

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

Answers 1
Added at 2016-12-05 13:12
Tags
Question

When including a key from the object stored in my body JSONB field the query is running ~100ms slower than without the field in the select:

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

This takes about 105ms compared to only 5ms when the stats select isn't included. It doesn't seem to matter which key I return from the JSONB body object, they all significantly slow down the query. There are other keys from the body object I would like to include in this query, but each one I add increases the overall query time by ~50ms

The body field has a gin index and I'm seeing the behaviour in both PG v9.5x and v9.6.1

Any suggestions for alternative ways of returning jsonb object data more efficiently?

Answers
nr: #1 dodano: 2016-12-05 14:12

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

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

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

If you simply store and retrieve full JSON documents then there is no issue but as soon as you start manipulating JSON documents in the DBMS and speed is an issue then you should normalize your json data away.

Source Show
◀ Wstecz