Marcelo Altmann: Mastering Query Rewriting for Faster PostgreSQL Performance

When you first spin up your app, the emphasis is on getting started and getting data to your clients. But when you don’t have throughput, you are also not going to have enough concurrency to unveil bad queries. But then you have success. And success means data. More users, more interactions, more everything. Suddenly, queries that performed fine are struggling under the load, hurting performance and scalability. This is all going to mean a far worse user experience when much higher costs because of inefficient resource use. This is where mastering query rewriting techniques comes into play. By analyzing and refactoring your SQL queries, you can significantly improve the performance of your PostgreSQL database. Query optimization and rewriting can also reduce query execution times, minimize resource consumption, and efficiently handle larger datasets.  Let’s go through a few options here, looking at the performance gains they can provide and other options available to database engineers. Faster PostgreSQL Performance Through Indexing Let’s start with our query. Let’s say we have a products table and a sales table, and we want to calculate the total sales amount for each product category for last year.  SELECT p.category_id, SUM(s.quantity * p.price) AS total_sales FROM sales s JOIN products p ON s.product_id = p.product_id WHERE s.sale_date BETWEEN '2023-01-01' AND '2023-12-31' GROUP BY p.category_id This isn’t a crazy complicated query. But it is inefficient. As products grows into thousands of rows and sales into millions, the time for this query to run gets into seconds: Time: 1300.403 ms (00:01.300) Again, it's not bad, but it will only get worse. To get a better understanding of what is happening under the hood, we can run this query with EXPLAIN ANALYZE to output the query plan: PLAN ---------------------------------------------------------------------------------------------------------------------------[...]