Query Optimisation and Performance Tuning in DBMS

Alex: Today we're looking at query optimisation and performance tuning in databases. Sam, why does query performance matter so much?

Sam: Because the difference between a well-optimised and a poorly optimised database can be the difference between a responsive application and an unusable one. A query that takes 10 seconds to run might be acceptable for an occasional report, but if it's called thousands of times per minute by a busy application, it will bring the system to a halt. Query performance is a critical quality attribute of any database-backed system.

Alex: How does a database decide how to execute a query?

Sam: The database engine has a component called the query optimiser that analyses every SQL statement and decides on an execution plan: the sequence of operations it will use to retrieve the required data. The optimiser considers factors like which indexes are available, the estimated number of rows in each table, the join order, and the selectivity of filter conditions, and it chooses the plan it estimates will be fastest. The EXPLAIN command, or EXPLAIN ANALYZE in PostgreSQL, shows you the execution plan the optimiser has chosen for any query.

Alex: How do indexes improve query performance?

Sam: An index is a data structure that allows the database to find rows matching specific criteria without scanning the entire table. Think of a book index: rather than reading every page to find references to a topic, you look it up in the index and go directly to the relevant pages. Without an index, a query filtering by a column requires a full table scan, which is slow for large tables. With an appropriate index, the same query can retrieve results almost instantly.

Alex: What are the trade-offs of indexing?

Sam: Indexes consume storage space. More importantly, every time data is inserted, updated, or deleted, all the indexes on that table must also be updated, which adds overhead to write operations. This means you should index columns that are frequently used in WHERE clauses, JOIN conditions, and ORDER BY clauses, but avoid over-indexing tables that are written to frequently. The right indexing strategy depends on the balance of read and write operations for each table.

Alex: What are the most common causes of slow queries?

Sam: Missing indexes on columns used in filtering or joining. Poorly written SQL that prevents the optimiser from using available indexes, for example using a function on an indexed column in a WHERE clause. Returning far more data than needed, selecting all columns when you only need a few, or failing to paginate results. Inefficient join logic, particularly nested loops on large tables. And database statistics that are out of date, causing the optimiser to make poor plan choices based on inaccurate estimates of data distribution.

Alex: What does a DBA do when they identify a slow query?

Sam: Use EXPLAIN to examine the execution plan and identify the bottleneck. Check whether appropriate indexes exist and are being used. Rewrite the query if the current formulation is preventing optimal plan selection. Create or modify indexes if they're missing or suboptimal. And in some cases, denormalise the schema or create a materialised view if the query is fundamentally constrained by the data model. It's a diagnostic and problem-solving process that combines deep technical knowledge with careful analysis.

Alex: Brilliant. Thanks Sam. We move into Unit 14: Internet of Things next.