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. A scan of every row in a table with a few hundred rows is fine, and often the fastest choice. The same scan on a table with forty million rows is usually your answer.
Then try EXPLAIN ANALYZE, which actually runs the query and adds what really happened, including actual row counts and timings. Be careful here. It really runs, so on an UPDATE or DELETE it really changes data. Wrap it in a transaction and roll it back, or use a copy.
Compare the estimate with the actual numbers. When the database expected ten rows and got ten thousand, every decision it made after that step was based on a wrong guess, and that gap is where to start asking questions.
You do not need to understand every line on the first day. Take one slow query a week and read its plan before you change anything. Write down what you expected and what the plan showed. After a month you will start predicting plans before you run them, and you will stop guessing in front of a slow page.
– Asael Shinder
Top comments (0)