Understanding B-Tree Indexes in PostgreSQL: Part 1
This is a good practical explainer because it connects the usual "add an index" advice to the physical model underneath: pages, tuples, ctid, B-tree roots and leaves, and the way PostgreSQL can avoid dragging a query through the whole table.
The Hacker News thread gets the teaching problem right. This is exactly the kind of PostgreSQL-specific reference you want when someone knows queries are slow but does not yet have a mental model for why missing indexes hurt. The deeper lesson is that performance work gets easier when the database stops being a black box and starts looking like a set of page-level tradeoffs.