Query

a written question a database answers

What it is

A query is a request for data, written in a language the database understands, that comes back with a result: usually a set of rows, sometimes a single number. PostgreSQL’s manual defines it in one line: retrieving data from a database, or the command that does the retrieving, is called a query, and in SQL that command is SELECT. Everyday usage stretches the word further, and engineers call almost any statement they send to a database a query, including the ones that change data and hand something back. Both senses appear below, and the plural, queries, usually means all the traffic an application sends.

Asking which invoices have waited longest for payment reads close to how the question would be put aloud:

SELECT i.number, c.name, i.amount, i.due_on
FROM invoices AS i
JOIN customers AS c ON c.id = i.customer_id
WHERE i.paid_on IS NULL
  AND i.due_on < current_date
ORDER BY i.due_on
LIMIT 20;

The text names what it wants and leaves the method open. Between receiving it and returning the first row, PostgreSQL’s planner lists the ways it could get the answer, such as reading the whole invoices table or walking an index on due_on, estimates a cost for each, and runs the cheapest. Those costs are in arbitrary units, conventionally anchored to reading one page from disk. Two queries that look alike on screen can therefore differ by a factor of a thousand once the data under them grows, and the plan is the only place that difference shows.

The clauses do not run in the order they are written. The manual describes the processing of SELECT as WITH first, then FROM, WHERE, grouping and HAVING, then the output list, and only after that ORDER BY and LIMIT. That order explains a rule that surprises most people once: a name given to a column in the select list works in ORDER BY and GROUP BY, while WHERE runs before the name exists, so there the expression has to be written out again. The result of every query is itself a table, which is why queries nest, with a subquery or a WITH clause feeding its rows into a larger one.

Why it matters

In an application backed by a relational database, the queries decide much of how fast a page feels and how much hardware the system needs. Which queries matter is arithmetic rather than instinct: a query’s cost to the system is its duration multiplied by how often it runs. A 2 ms lookup run a million times an hour adds up to more than half an hour of database time every hour, while a report that takes two seconds and runs twice a day barely registers.

PostgreSQL keeps that tally in the pg_stat_statements extension. It folds statements that differ only in their literal values into one entry and records how many times each ran and the total time it took. Sorting by the total rather than the average is the habit that pays off first in query work, because it points at the statement worth fixing instead of the one that only looks slow.

Every query also costs a round trip. The application sends text, the server parses, plans and runs it, and rows travel back. For a quick lookup on an index, the trip can outweigh the work. So the shape of the conversation matters as much as any single statement: one UPDATE that touches a hundred rows makes one trip, and a loop that reads a hundred rows and updates each in turn makes a hundred and one.

Then there is the question of who writes the queries. An ORM or a query builder generates them from application code, which is quick for simple reads and writes and keeps the code in one language. It also hides how many statements a request sends and what they look like, until a page turns slow. Hand-written SQL shows both, at the price of someone owning it like any other code. A workable split lets the ORM handle routine records, writes the hot paths by hand, and reads the generated SQL of anything that climbs to the top of the totals.

What it looks like in practice

The method for a slow database in Optimise the database before you shard it starts with queries, before anyone prices more machines. The first step asks PostgreSQL which statements cost the most in total, using pg_stat_statements sorted by total execution time. The second runs EXPLAIN (ANALYZE, BUFFERS) on the statement at the top, to see the plan the database chose, the rows each step really returned against the rows the planner expected, and how many pages it read. The note’s rule of thumb is that a scan of a million rows to return ten, or an estimate a hundred times off, usually explains the slowness on its own. It counts round trips too, and draws the line this article draws above: whatever has to see the data moves into the database, and business rules stay in the application.

A query can also do the work of a lock. In Selling the last ticket exactly once a flash sale goes wrong in one of two ways. Reading the stock level, deciding in the application and writing the new number back lets two buyers both take the last unit; holding a row lock across that application logic makes every buyer wait behind it. The fix folds the check and the decrement into one conditional UPDATE that subtracts the quantity only where enough stock remains and returns the new level. One row back means the unit is reserved, and zero rows means sold out. At READ COMMITTED, PostgreSQL’s default isolation level, a second buyer who reaches the row while the first holds it waits, then evaluates the WHERE clause again against the committed row, so the last unit goes to exactly one of them. A CHECK constraint that keeps stock at zero or above stands behind the query.

Where it goes wrong

A cheap query that dominates the load. The database runs hot, yet the slow-query log stays quiet, because each call finishes in a few milliseconds. The cause is frequency: one fast query called on every request, often from a loop or from a component that appears on every page. Rank statements by total execution time in pg_stat_statements, then cache the result, batch the calls, or make the query cheaper with an index that matches its filter.

A page that sends hundreds of queries. Each query in the trace takes a millisecond, and the page still takes seconds. An ORM loading related records lazily, one query per parent row, is the usual cause, known as the N+1 pattern. Load the related rows in one query, with a join or with WHERE id = ANY($1) and an array of keys, and watch the count of statements per request as closely as the duration of any one.

The last unit sold twice. Two requests read the same stock level, both decide there is enough, and both write. Reading and writing in separate statements leaves a gap between the check and the action, and concurrent traffic finds it. Close the gap inside the query, with a conditional UPDATE and RETURNING as above, or with SELECT … FOR UPDATE held as briefly as possible and with no network call between the lock and the commit.

A diagnosis that changed the data. Someone runs EXPLAIN ANALYZE on a slow DELETE to look at its plan, and the rows are gone. ANALYZE executes the statement for real and discards only its output, so every side effect happens. Wrap any data-modifying statement in BEGIN and ROLLBACK before analysing it, or use plain EXPLAIN, which plans without executing, when estimates are enough.

Further reading

Glossary