[GENERAL] JsonB Gin Index is very large; is there a work around?
Hi, I'm using 9.4 beta 2. I ran a test using 4 of the largest Json docs from our production data set. The four files total to 59.4 MB of raw json, which is compressed by TOAST to 21 MB, which is great. The index, though, is 47 MB, bringing the total size of the data in PG to 68 MB. The index was created as: CREATE INDEX document_payload_idx ON document USING gin (payload jsonb_path_ops); I recognize that these may be reasonably considered pathological cases. My questions are: Is the work that was done to reduce GIN index size in this release? Is there anyway to index a subset of the data in a JSONB column? I'm thinking of something like declaring certain paths to be indexed? Any suggestions would be greatly appreciated.
Re: [GENERAL] JsonB Gin Index is very large; is there a work around?
On Wed, Aug 20, 2014 at 1:53 PM, Larry White ljw1...@gmail.com wrote: Is there anyway to index a subset of the data in a JSONB column? I'm thinking of something like declaring certain paths to be indexed? Yes. See the expression index example in the jsonb documentation. -- Regards, Peter Geoghegan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] JsonB Gin Index is very large; is there a work around?
Ok Thank you. FWIW, the documents (which I can't share) consist mainly of a long list of integers in the form {n:41515920318427252715}, so they really are outliers. On Wed, Aug 20, 2014 at 5:09 PM, Peter Geoghegan peter.geoghega...@gmail.com wrote: On Wed, Aug 20, 2014 at 1:53 PM, Larry White ljw1...@gmail.com wrote: Is there anyway to index a subset of the data in a JSONB column? I'm thinking of something like declaring certain paths to be indexed? Yes. See the expression index example in the jsonb documentation. -- Regards, Peter Geoghegan