What is the process for tuning a slow query?
Six steps, and the order matters, because the first two decide what you are even going to try.
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
amount DECIMAL(10, 2) NOT NULL
);
Step one is to find the query. Look at the slow query log, or at the list of the queries that take the most time in total. The worst query for your database is not always the slowest one, because a query that takes a tenth of a second and runs ten thousand times an hour is costing you more than one that takes ten seconds once a night.
Step two is to look at the plan. Run the query with the word EXPLAIN ANALYZE in front of it. That runs the query and shows you the plan it actually used, not the plan it thinks it will use. Look for the step with the biggest cost, and for the difference between the rows it expected and the rows it really got.
Step three is to work out what the step is doing. A sequential scan on a big table means that there is no index that the filter can use. A sort of a lot of rows means that the index does not match the order of the query. A join with a large number of rows on one side means that the other side has no index.
Step four is to fix it. Usually that means an index. Sometimes it means changing the query, for example by removing the function that is wrapped around a column.
Step five is to measure again. Run the query with the plan again and check that the step you fixed is now cheap. And check that the query really is faster, because sometimes the plan looks better and the query is not.
Step six is to check the cost. An index makes the write slower, so look at what the table now costs to write, and at the usage of the new index after a while.
The next two questions in this topic show the fix and the common causes.