Indexes

Part of the PostgreSQL course

The query worked fine on your laptop with two hundred test rows. In production, against four million, the same query takes eleven seconds and the page times out. Nothing changed except the size of the table, and that is the whole story: without help, the database answers WHERE user_id = 42 by reading every single row and checking each one.

That analogy also tells you the cost, which is the half people forget. The index at the back of a book has to be rebuilt every time you edit a page. Indexes are not free, and an index nobody uses is pure overhead. ...