Re: JSONB index not in use, but is TOAST the real cause of slow query?

On Sat, May 28, 2022 at 1:54 PM Shaheed Haque <shaheedhaque(at)gmail(dot)com>
wrote:

>
And how can I understand the dreadful amount of
> time (of course, this is just on my dev machine, but still...)? Is
> there a way to see/tweak what TOAST costs or indeed to confirm if it
> is even in use?
>

Turn on track_io_timing, and then do explain (analyse, buffers). This
won't distinguish TOAST from main table IO, but will at least confirm if a
large amount of IO is happening and how long it is taking.

You can look at pg_statio_user_tables before the after the query to
distinguish TOAST, but this won't give you the timing, just the block
counts. And the counts might also get incremented by concurrent users, so
on a busy system it is hard to interpret.

Are these queries fast upon repeat execution? A few meg time less than
1000 it just not that much with modern hardware, you should be able to just
cache it in RAM.

Cheers,

Jeff