A page in your application takes eight seconds to load, and you have traced it to one query. Someone on the team says add an index. Someone else says rewrite it as two queries. You could try both and time them, but there is a better first step that most developers skip for years. Ask the database what it intends to do.
Every major relational database will show you its plan. In PostgreSQL and MySQL you put EXPLAIN in front of the query. In SQL Server you ask for the execution plan in the management tools. What comes back looks dense the first time, but it is a description of the steps the database will take: which tables it reads, in what order, whether it scans every row or jumps straight to the ones it needs, and how it joins them together.
Learn to read it in small pieces. In PostgreSQL the plan is a tree, and the most indented steps run first. For each step, notice two things. What kind of operation it is, and how many rows the database expects to pass through it.…