This post covers standard Postgres B-tree indexes. To make it easier to read, I’ve just referred to “indexes” throughout, but it’s worth bearing in mind that the other Postgres index types behave very differently. At pgMustard, we’ve been looking into multi-column indexes recently: particularly what problems they can cause and…

Indexes are really important for Postgres performance, but they’re often misunderstood and misapplied. This post aims to give you a good grounding in indexes to avoid a lot of beginner mistakes. Step one: understand what you want to achieve Because indexes are such a powerful tool, a new index is often viewed as “the answer” to whatever performance…

Index-only scans can be a really effective way to speed up table reads that hit an index. Of course, they’re not a silver bullet to all your performance problems, but they are a very welcome and useful part of the toolbox. In order to understand index-only scans, why (and when)…

Why the query planner makes estimates When Postgres plans how it’s going to execute your query, it has a bunch of implementation choices to make. Some of these depend on just how much data it’s going to be dealing with — for example, there are several different sort algorithms available, and one of the major factors…

Like me, you’re a Postgres noob. You’re a software dev (and a good one!), but you have to write database queries every now and then, and often they’re pretty slow. There’s Alex, the DBA — you can ask them for help. But even though they’re really polite, you know that…