4 ms·

Not related to the project but do you know of useful content that explains how to approach query optimization for Postgres? All I was able to find was classic s

• phamilton 3y ago
  https://www.pgmustard.com/ https://www.pgmustard.com/ is a great tool. There are some free alternatives for the actual tool, but their documentation is pretty great. For example, https://www.pgmustard.com/docs/explain/heap-fetches https://www.pgmustard.com/docs/explain/heap-fetches provides clarity that isn't obvious from the official pg docs. Beyond those resources, here are a few useful things I've learned: 1. `explain (analyze, buffers)` is useful. It will tell you about hot vs cold data. One caveat: it doesn't deduplicate the buffer hits, so 1M buffer hits could be only a few thousand unique pages. But I still find it useful especially when comparing query plans. 2. pg_buffercache. Knowing what's in the buffer allows you to optimize the long tail of queries that perform buffer reads. Sometimes rebuilding an index on an unrelated table can create space in the buffer for the data the query needs. 3. Try using dedicated covering partial indexes for high traffic queries. An index-only scan is super cheap and with the right include and where condition you can make it small and efficient. The tips above are especially useful in Aurora, where the shared buffers are huge (there's no page cache so it's the only caching layer).
• iurisilvio 3y ago
  The PEV2 is open source and give you a good visualization. I never used this pgmustard to compare. https://explain.dalibo.com/ https://explain.dalibo.com/