What a PostgreSQL Query Is Really Doing

The starting point

SQL lets us describe what data we want without manually describing every step the database should take. PostgreSQL then parses the query, checks types and relations, considers available indexes and statistics, and chooses an execution plan. Understanding this separation between declarative intent and execution strategy is one of the most useful database concepts to learn because it explains why two logically equivalent queries can behave differently.

The planner chooses a strategy

For a simple query, PostgreSQL may scan a table, inspect rows, and return matches. For a larger query it may choose an index scan, combine several indexes, join tables using different algorithms, or sort intermediate results. The planner estimates the cost of these strategies using information about the data.

That means reading SQL alone does not always tell you how expensive a query will be. When performance matters, the execution plan is evidence. It shows what PostgreSQL actually decided to do rather than what we imagine it is doing.

Joins are not automatically bad

Joins sometimes get blamed for slow database applications, but a relational system is designed around relationships. A join between articles and categories is normal. The important questions are how many rows participate, which columns are indexed, how selective the filters are, and what the planner chooses.

Trying to eliminate every join by duplicating data can create a worse system. Duplication introduces synchronization problems. A better approach is to understand the query, inspect its plan when necessary, and change the model only when there is evidence that the current shape is unsuitable.