A Tale of Query Optimization
I can definitely relate to the struggle of debugging slow SQL queries. I&#x27;ve been optimizing Postgres for the last three years for my day job. There&#x27;s nothing like spending hours trying to determine why a query is slow, only to discover some bizarre incantation makes the problem go away.<p>I had one case where adding OFFSET 0 to a query, which did not change the semantics of the query, made the query ~10x faster. It turned out that due to a reference to a large column being inlined in a dozen different places, Postgres was detoasting[0] that column a dozen times more than it needed to. Adding the OFFSET 0 prevented the subquery from being inlined and made it so Postgres only detoasted the column once.<p>In general, I think optimizing a database is way harder than it needs to be. A lot of it is black magic and it requires a ton of expertise that many teams don&#x27;t have. Even though I&#x27;ve been doing it for years, I still struggle with figuring out what indexes I should create to make a query fast.<p>I recently started a startup in an attempt to make optimizing databases easier. I built a Postgres extension that records information about queries as they run. It&#x27;s basically the same information you would get for EXPLAIN ANALYZE, but you get it for every query at query time. Then, using this information, I&#x27;m able to make specific recommendations about how you should optimize your database. I can give advice on how you should structure your schema and what indexes you should create in order to maximize the performance of your queries. This way, even teams that don&#x27;t have much in-house Postgres experience won&#x27;t have to spend hours or even days trying to figure out how to make their queries faster.<p>If this sounds interesting to you, I would be happy to chat. You can reach out to me at michael@perfalytics.com. It&#x27;s my goal to use the expertise I&#x27;ve built up over the last couple of years to make scaling Postgres a truly painless experience.<p>[0] <a href="https:&#x2F;&#x2F;malisper.me&#x2F;postgres-toast&#x2F;" rel="nofollow">https:&#x2F;&#x2F;malisper.me&#x2F;postgres-toast&#x2F;</a>