...
- Read the next datablock from the disk for the main table (blocks read during table scans are not pinned to the shared cache for large tables)
- Perform an index scan for a TOASTed counterpart to find rows that relate to the row in the main table (index blocks can be either in the shared cache or not)
- Retrieve all those rows that contain a JSONB value (in most cases db blocks must be read from disk)
- Decompress the value of needed and apply a function or make a comparison to the value
...
Here is a quite interesting post regarding hidden costs when working with JSONB
https://heap.io/blog/engineering/when-to-avoid-jsonb-in-a-postgresql-schema
| Warning | ||
|---|---|---|
| ||
... Using the above measurements table again, the initial non-JSONB version of our table takes up 79 mb of disk space, whereas the JSONB variant takes 164 mb — more than twice as much. That is, the majority of our table contents are the the strings value_1, value_2, value_3, and scientist_id, repeated over and over again. ... |
...