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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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:
Show quoted text
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