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 <t...@sss.pgh.pa.us> wrote: > Bill Moran <wmo...@potentialtech.com> writes: > > Tom Smith <tomsmith198...@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 >
