Re: JSONB performance enhancement for 9.6

Hi:

The goal is fast retrieval of a a field value with a row when the row is
already
picked, one scenario is download a particular field value (if exists) of
all rows in the table.
It is actually a very common use case of exporting data of several user
selected fields.
The performance is extremely slow.

Thanks

On Sun, Nov 29, 2015 at 12:54 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Bill Moran <wmoran(at)potentialtech(dot)com> writes:
> > Tom Smith <tomsmith1989sk(at)gmail(dot)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
>