Mastering PostgreSQL 9.6

Inspecting individual queries

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 in the plan, 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 on)
• Look ...