My personal favorite is I was generating a query that pulled out a dozen different fields from a large JSONb column. Naturally, you would think Postgres would read the JSONb field once, then pull out the individual fields from it. Instead, Postgres was reading the JSONb column once per each field. I figured out this was the case because the number of blocks read from EXPLAIN (ANALYZE, BUFFERS) went up proportionally to the number of fields I extracted from the JSONb column. Reading the JSONb column was especially expensive because Postgres needed to deTOAST[0] the JSONb column.
The obvious fix is to write a subquery to read the entire JSONb column and then have the outer query extract the individual fields, but that doesn't work! Postgres will inline the subquery, basically undoing your attempt to prevent the unnecessary accesses. In the end, the solution wound up being to add OFFSET 0 to the end of the subquery. That doesn't change the semantics of the query, but it does prevent Postgres from inlining the JSONb column access.