On Sat, 28 Nov 2015 21:27:51 -0500
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;
?
The default storage for a JSONB field is EXTENDED. Switching it to
EXTERNAL will disable compression. You'll have to insert your data over
again, since this change doesn't alter any existing data, but see
if that change improves performance.
--
Bill Moran