The surprising impact of medium-size texts on PostgreSQL performance
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:<p><pre><code>  SELECT x ->> 'field1', x ->> 'field2', x ->> 'field3'
  FROM table
</code></pre>
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.<p>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.<p>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.