Mastering PostgreSQL 12 - Third Edition

Inspecting individual queries

Sometimes, slow queries are identified, but we still don't have a clue about 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 and use the following checklist:

• Try and 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 ...