Thread: 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.
On Wed, Aug 20, 2014 at 1:53 PM, Larry White <ljw1001@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
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.geoghegan86@gmail.com> wrote:
On Wed, Aug 20, 2014 at 1:53 PM, Larry White <ljw1001@gmail.com> wrote:Yes. See the expression index example in the jsonb documentation.
> 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?
--
Regards,
Peter Geoghegan