[malisper]

I've mentioned this story here before, but one of the most surprising performance gains I saw was by eliminating TOAST look ups. If I recall correctly, each time you use the `->>` operator on a TOASTed JSONb column, the column will be deTOASTed. That means if you write a query like:

  SELECT x ->> 'field1', x ->> 'field2', x ->> 'field3'
  FROM table

and x is TOASTed, Postgres will deTOAST x three different times. This multiplies the amount of data that needs to be processed and dramatically slows things down.

My first attempt to fix this was to read the field in one query and use a subselect to pull out the individual fields. This attempt was thwarted by the Postgres optimizer which inlined the subquery and still resulted in deTOASTing the field multiple times.

After a discussion with the Postgres IRC, RhodiumToad pointed out that if I add OFFSET 0 to the end of the subquery, that will prevent Postgres from inlining it. After retrying that, I saw an order of magnitude improvement due to eliminating the redundant work.

[throwayws]

This sounds like an interesting optimization possibility. Do you know will this be patched?

[jerrysievert]

the funny thing is that pg shouldn't need to do a detoasting to be able to query as long as it's jsonb, it's a pretty straightforward iteration - we do the same thing when converting from jsonb to v8 in plv8.

[Tomis02]

How come? As far as I know if the jsonb data was toasted (too big to fit in 2kb), then in order to query anything back from inside the jsonb you have to de-toast it, by definition.

[rolls-reus]

How do you find out that the optimiser inlined the subquery? Is this visible in the explain output?

[malisper]

IIRC, EXPLAIN VERBOSE will show you the columns being selected by each step of the plan. The inlining showed up there.

[jerrysievert]

or better yet: EXPLAIN ANALYZE VERBOSE - that will give you a little bit better picture.