Slow database? How to audit queries in PostgreSQL

Why does the database suddenly start slowing down?

As a web application grows, it is common for routine tasks and reports to start taking precious seconds to respond. Most of the time, the culprit is not weak infrastructure, but rather a poorly structured SQL query that forces the database engine to read entire tables row by row. In PostgreSQL, the most powerful tool to uncover the exact cause of slowness is the execution plan generated by the EXPLAIN command. Just like systems dealing with slow queries in MySQL, understanding how the database views your code is the first step toward fixing the issue.

In practice, the execution plan acts like a road map that the database draws before delivering the requested response. When you write a query, PostgreSQL's internal planner analyzes dozens of possible paths, calculating the estimated cost in time and processing for each one. It decides whether it is worth using an index created on a column or opening every physical file on the hard drive to search for the information. Knowing how to interpret this map eliminates guesswork and allows for surgical corrections in code and system architecture.

How to extract your first execution plan with EXPLAIN

To start auditing, you don't need to change a single line of production code; simply open your management tool and add the word EXPLAIN before your usual query. However, there is a fundamental detail many developers forget: by default, the command only simulates and estimates costs without actually running the operation. If you want to see the real time the database spent and how many rows it actually read, you must use the ANALYZE parameter alongside it, obtaining actual performance and resource consumption data.

Below is a practical example of how to apply the command to a typical query searching for customer orders:

EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'pending' AND amount > 500.00;

When you run this command, PostgreSQL returns a tree-structured text. Each line of this report shows an operation node, indicating whether the database performed a sequential scan or used an existing index. Reading this result is done from the inside out; operations nested deeper in the text are executed first, feeding upper steps until the final result reaches your screen.

Understanding the difference between Seq Scan and Index Scan

The biggest performance villain in most relational databases is the dreaded Seq Scan, short for Sequential Scan. In practice, this means PostgreSQL had to physically read every single data block of the entire table, from the first record to the last, just to find the few rows matching your filter. If your table has ten thousand rows, the impact is small, but when it reaches tens of millions of records, this operation consumes all available RAM and spikes disk usage.

To prevent this unwanted behavior, data engineering relies on indexes, which work similarly to the index at the back of a textbook. When the planner chooses an Index Scan, it first checks this ordered auxiliary structure to find the exact disk address of the record, jumping straight to the desired point. However, creating indexes haphazardly brings side effects, as every data insert and update must also modify these extra structures, requiring technical balance in table modeling.

How to interpret costs, times, and estimates

Inside the report generated by EXPLAIN, you will find terms like cost and actual time. Cost is an abstract unit defined by PostgreSQL itself to measure computational effort, where sequential access to a disk page equals a base cost. These numbers help the planner choose the cheapest path, but they can fail if the database's internal statistics are outdated, which frequently happens after large volumes of data deletions or insertions.

Another critical point to watch in the report is the divergence between estimated rows and actual rows. If the planner calculates that a condition will return only five rows, but in practice it returns five hundred thousand records, it might choose an inappropriate join algorithm that will lock up the server. To keep these predictions accurate, the database periodically runs a maintenance routine called VACUUM and ANALYZE, which updates the catalog of statistics regarding data distribution across columns.

Identifying bottlenecks in table joins and sorting operations

Complex queries usually involve joins between multiple tables and heavy sorting using the ORDER BY clause. When EXPLAIN shows operations called Hash Join or Nested Loop, it reveals how PostgreSQL combined the data. The Nested Loop works like two nested loops in programming, excellent for small sets but disastrous for large bases. Meanwhile, the Hash Join builds a temporary table in memory to quickly cross-reference data, though it can exhaust available memory if the result set is too large.

When memory allocated for these operations is insufficient, the database falls back to disk to handle part of the processing, a phenomenon known in engineering as spilling to disk. This constant data exchange between RAM and physical storage drops performance drastically, reminiscent of severe lockups occurring when an operating system exhausts physical memory and triggers the paging file, a scenario similar to investigating memory limits and OOM Killer in containers. Tuning parameters like work_mem in the PostgreSQL configuration file prevents this unwanted behavior. To explore these architectural concepts further in production, see our related technical analysis on How to test the network integrated circuit with a multimeter.

Conclusion

Auditing slow queries using the EXPLAIN command transforms how you write and manage code daily, moving away from guesswork toward decisions based on concrete performance data. By deciphering execution plans, you learn to spot unnecessary scans, create indexes at the right time, and prevent heavy operations from strangling server resources.

Mastering this tool is a game-changer for the career of any developer or system administrator working with relational databases. The habit of analyzing query plans before pushing any code to production ensures scalable, resilient systems capable of delivering a fast and consistent experience to end users.