SQL Performance & Query Optimization

Welcome everyone! Today we're diving into SQL performance and query optimization, where we'll transform painfully slow queries into lightning-fast sub-millisecond responses. If you've ever watched a query take minutes to complete, or seen your database server grind to a halt under load, this session is for you. We'll explore the mechanics of how databases execute queries, learn practical optimization techniques, and see real-world examples of achieving forty to sixty times performance improvements. By the end, you'll have a toolkit for diagnosing and fixing the most common performance bottlenecks in production systems.

Let's start by understanding what happens when you execute a SQL query. Looking at this diagram, your query flows through six distinct stages. First, the Parser validates syntax and builds a parse tree. Then the Rewriter applies rules and view expansions. Next comes the Query Planner, which generates possible execution strategies. Here's where it gets interesting: the Cost Optimizer, shown in purple, evaluates potentially thousands of different execution plans and picks the cheapest one based on statistics. Finally, the Executor runs the chosen plan and returns your result set. As the callout emphasizes, understanding this pipeline is crucial because the optimizer is your friend when you write queries it can work with, but your enemy when you don't. Learning to think like the optimizer is the key to writing fast queries.

Now let's see optimization in action with EXPLAIN ANALYZE, your most powerful diagnostic tool. Looking at this terminal output, the first query does a sequential scan on the orders table, highlighted in red. Notice the cost of twenty-eight thousand five hundred forty-seven and execution time of one hundred forty-two milliseconds. The filter removed nearly a million rows! This is terrible. Now watch what happens after we create an index on customer ID. The second query uses an index scan, shown in green. Same data, but now execution time drops to just zero point four one milliseconds. That's a three hundred fifty times improvement from a single index! This demonstrates why EXPLAIN ANALYZE should be your first step when optimizing any slow query.

Not all indexes are created equal, so let's examine the four main types and when to use each one. First, B-Tree is your default workhorse, handling equality, ranges, and sorting. Use it for columns in WHERE clauses, JOINs, and ORDER BY. Second, Hash indexes are optimized purely for exact equality lookups, they're smaller but can't handle ranges or sorting. Third, GIN indexes are your go-to for full-text search, JSONB queries, and array operations, they excel at multi-valued columns. Finally, GiST indexes handle geometric and geospatial queries, you'll see these with PostGIS for location-based searches. Choosing the right index type for your access patterns can make the difference between milliseconds and minutes.

Here's a classic optimization pattern: replacing correlated subqueries with joins. Looking at the slow query on the left, we have two subqueries in the SELECT clause. The problem? Each subquery executes once for every row in the orders table. If you have a thousand pending orders, you're running two thousand extra queries! Now look at the optimized version on the right. By using JOIN and LEFT JOIN, we fetch all the data in a single pass. The database can use indexes efficiently and process everything in one go. The customer name comes from a JOIN, and we use COUNT with GROUP BY for item counts. This pattern alone can turn a thirty-second query into a sub-second one.

Let me show you one of the most devastating performance anti-patterns: the N plus one query problem. At the top, we fetch five hundred active orders with one query, simple enough. But then, as shown in the middle section, we execute a separate query for each order's items. That's five hundred and one total queries! Look at the stats box: this naive approach takes one thousand two hundred forty milliseconds. The solution, shown in the bottom code block, is a single query with a LEFT JOIN. One round-trip to the database instead of five hundred and one. Execution time drops to just eight milliseconds, a sixty-two times improvement! This pattern is especially common in ORMs when lazy loading isn't properly managed. Always prefer eager loading with joins when you know you'll need related data.

This table gives you a comprehensive comparison of index strategies for different scenarios. Reading across the columns, you can see each index type's strengths and trade-offs. B-Tree offers the best balance for most use cases with medium size and low write cost. Hash is smallest but only supports equality. Notice GIN has large size overhead and high write cost, that's the price for its powerful full-text and JSONB capabilities. BRIN is fascinating, it's tiny and has very low write cost, perfect for large time-series tables. The partial and composite strategies shown at the bottom let you fine-tune indexes for specific query patterns. As the callout notes, the rule of thumb is simple: index what you filter on, join on, and sort on. Just avoid indexing low-cardinality columns unless you're using a partial index to filter to a specific subset.

Let's look at the real-world impact of proper optimization. The stats at the top tell an incredible story: query time dropped from one hundred forty-two milliseconds to zero point four milliseconds, that's a ninety-nine point seven percent reduction! We went from scanning nearly a million rows to just three hundred twelve. I/O operations dropped by the same amount. Looking at the detailed metrics table below, notice the shared buffers hit rate jumped from twelve percent to ninety-eight percent, meaning almost everything now comes from cache. P99 latency, that critical tail latency, dropped from eight hundred ninety milliseconds to just four milliseconds. And check out max connections used: we freed up eighty-two database connections because queries complete so much faster. These aren't theoretical numbers, this is what proper indexing and query optimization deliver in production.

Now let's talk about the four horsemen of query performance apocalypse. The danger callout warns us that these anti-patterns can turn sub-millisecond queries into multi-second disasters. First, SELECT star forces the database to fetch every column, including large BLOBs you don't need, and defeats covering indexes. Second, missing indexes on WHERE and JOIN columns is the number one cause of slow queries, period. Third, implicit type casts are insidious. If you compare a varchar column to an integer, the planner adds a CAST function that prevents index usage, and you won't even see it in your query! Finally, OR chains often break index usage. If you're ORing conditions on different columns, the optimizer struggles. Rewrite as UNION ALL or use IN for the same column. Audit your codebase for these patterns before you start adding indexes.

You can't optimize what you don't measure, and pg_stat_statements is your window into real production query performance. Looking at this terminal output, we're querying for the top five slowest queries by total time. Notice the first row in red: that SELECT star from orders has been called forty-eight thousand times, averaging one hundred forty-two milliseconds each. That's nearly seven million milliseconds of total execution time! This is exactly the query we optimized earlier. The green row at the bottom shows a well-optimized query, zero point three eight milliseconds average over ninety-five thousand calls. After you've made your optimizations, run pg_stat_statements_reset to clear the statistics, then monitor again to verify your improvements are working in production. This extension is invaluable for finding the queries that actually matter in your application.

Let's wrap up with the four key principles you should take away from this session. First, always use EXPLAIN ANALYZE. Measure before and after every optimization, never guess about performance. Second, index strategically based on your access patterns. Use B-Tree for ranges and sorting, GIN for JSONB and full-text search, and partial indexes for filtered subsets. Third, eliminate N plus one queries ruthlessly. Use joins or batch loading to cut down round-trips to the database. And finally, monitor continuously in production with pg_stat_statements to reveal your real slow queries. Remember, optimization is an ongoing process, not a one-time fix. Thanks for joining me today, and happy optimizing!