Indexes Lied to Me: and other lessons from the world of databases

Before diving into indexes and their types, it's important to have a mental model of postgreSQL is actually doing. In this article we are going to cover some basic indexes you can use when working with postgreSQL, how they work and when to use them. We will also cover some common pitfalls and how to avoid them.

What is a B-Tree Index?

A B-Tree(Balanced Tree) index is a separate on-disk structure that stores a sorted copy of your column values alondside pointers (called ctid) back to actual heap rows.

When you run WHERE status = 'active', PosgreSQL has two choices: walk over every page of your table or traverse through B-Tree index to find matching leaves and follow pointers back. The second option wins, But only when the cost model says it will.

Key Insight: The planner doesn't care about your wants,It computes an estimated cost for each execution plan and picks the cheapeet one. An index scan will only be observed if the planner believes it produces a low cost plan than a sequential scan.

Let's setup an example schema which we will be using throughout the article: I am using datagrip to demonstrate the queries, but you can use any client you like.