Bill Moran <wmoran@potentialtech.com> writes:
> Tom Smith <tomsmith1989sk@gmail.com> wrote:
>> Is there a plan for 9.6 to resolve the issue of very slow
>> query/retrieval of jsonb fields when there are large number (maybe
>> several thousands) of top level keys. Currently, if I save a large
>> json document with top level keys of thousands and query/retrieve field
>> values, the whole document has to be first decompressed and load to
>> memory before searching for the specific field key/value.
> I could be off-base here, but have you tried:
> ATLER TABLE $table ALTER COLUMN $json_column SET STORAGE EXTERNAL;
There is just about zero chance we'll ever worry about this for compressed
columns. However, even the uncompressed case does currently involve
loading the whole column value, as Tom says. We did consider the
possibility of such an optimization when designing the JSONB storage
format, but I don't know of anyone actively working on it.
In any case, it's unlikely that it'd ever be super fast, since it's
certainly going to involve at least a couple of TOAST fetches.
Personally I'd be looking for a different representation. If there
are specific fields that are known to be needed a lot, maybe functional
indexes would help?
regards, tom lane