PostgreSQL: speed up a query with EXPLAIN and indexes

The shop has joined the Nkoldongo traders' cooperative: forty shops, a single database, a million sales. The chairman calls Mama Ngo: "When a loyal customer comes in, the screen takes ages to bring up their history. And on Saturdays, it's worse." No query is wrong: they are slow. And in front of a slow query, the temptation is to try things at random. This workshop does the opposite: before speeding up a query, you read how PostgreSQL runs it. You measure, then you read the plan with EXPLAIN and EXPLAIN ANALYZE: a million rows read to return fourteen, by three processes in parallel. You create the index that changes the plan, and you prove it with the plan itself, learning to look for a single word in it. Then the three cases where an index exists and is useless: a function on the column, a condition that is not selective enough, a search in the middle of a text. You also measure what an index costs on every write, and you drop the one that is not used. Finally, full-text search: searching for words rather than letters, plural or singular, upper case or not, with the index that takes it from more than a second to a few dozen milliseconds. The challenge: the Saturday dashboard, and the two-column index that makes it instant.