Sometimes, slow queries are identified but you still don't have a clue of what is really going on. The next step is, of course, to inspect the execution plan of the query and see what happens. Identifying those key operations in the plan that are responsible for bad runtime is fairly simple. Try to use the following checklist:
- Try to see where it is in the plan that time starts to skyrocket
- Check for missing indexes (one of the main reasons for bad performance)
- Use the EXPLAIN clause (buffers true, analyze true, and so on) to see if your query uses too many buffers
- Turn on the track_io_timing parameter to figure out whether there is an I/O problem or a CPU problem (explicitly check if there is random I/O going ...
