Re: JSONB index not in use, but is TOAST the real cause of slow query? - Mailing list pgsql-general

From Tom Lane
Subject Re: JSONB index not in use, but is TOAST the real cause of slow query?
Date
Msg-id 3127818.1653767952@sss.pgh.pa.us
Whole thread Raw
In response to Re: JSONB index not in use, but is TOAST the real cause of slow query?  (Shaheed Haque <shaheedhaque@gmail.com>)
Responses Re: JSONB index not in use, but is TOAST the real cause of slow query?
List pgsql-general
Shaheed Haque <shaheedhaque@gmail.com> writes:
> One last thought about TOAST. If the cost of the -> retrieving the
> data cannot be obviated, is there any way to tweak how that works?

The only thing that's readily tweakable is to disable data compression
for the out-of-line values (see ALTER TABLE ... SET STORAGE, and note
that you have to rebuild the table for already-stored values to be
changed).  It seems unlikely that that will help you much though,
since doing that would save CPU at the cost of more disk I/O, and
it seems that the I/O side is your bottleneck already.  It *would*
help if jsonb had logic for partial fetches, because that'd require
uncompressed storage to work.  But AFAICS that's not there yet.
I distinctly recall that that was planned for when we were designing
jsonb's on-disk layout, but I see no code actually doing anything
of that sort ... the -> operator certainly doesn't.

            regards, tom lane



pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: JSONB index not in use, but is TOAST the real cause of slow query?
Next
From: Thomas Munro
Date:
Subject: Re: Improve configurability for IO related behavoir