diff options
| -rw-r--r-- | content/posts/postgres-aggregates/index.md | 58 | ||||
| -rw-r--r-- | content/posts/postgres-aggregates/setup.sql | 62 | ||||
| -rw-r--r-- | content/posts/postgres-aggregates/subqueries.png | bin | 37353 -> 67296 bytes |
3 files changed, 70 insertions, 50 deletions
diff --git a/content/posts/postgres-aggregates/index.md b/content/posts/postgres-aggregates/index.md index 5df14fe..d1664ee 100644 --- a/content/posts/postgres-aggregates/index.md +++ b/content/posts/postgres-aggregates/index.md @@ -7,34 +7,44 @@ categories: - PostgreSQL --- -I recently stumbled upon a problem while crafting queries for PostgreSQL at work. What appeared to be be a simple problem to solve at first glance turned out to take many hours of refining. I also found it weird to not easily find accessible knowledge about this on the Internet. I decided to simplify the problem to its core and to write an article about my findings working it through. +I recently stumbled upon a problem while crafting queries for PostgreSQL at work. We had a query with several correlated subqueries that was becoming too slow for the intended use (a search). I thought that using aggregations would solve it, but in fact it slowed it down, and even added bugs from counting duplicate rows. + +I could'nt find easily accessible knowledge about how to improve it on the Internet, so I decided to simplify the problem to its core and to investigate. This article is the walkthrough of my findings. Here is the simpler version of the problem: -- We a have a simple schema - - We have 10k products - - Each product has a mean of 100 orders, which makes for 1M orders - - Each product has a mean of 10 reviews, which make for 100k reviews +- We have a simple schema (you can download the [setup script](./setup.sql)) + - 10k products + - 1M orders (100 orders per product on average) + - 100k reviews (10 reviews per product on average) +- We only have basic indices on the foreign keys for a start - We want to make some efficient computations on multiples tables - We want to build a UI where we display a list of products, along with their number of reviews, and their number of orders -- We only have basic indices on the foreign keys for a start + +I will explain the tools I used to investigate, then we'll build 3 different queries which give the same result, but have different performance. + +<!-- TODO: TLDR? --> By the end of this reading, I hope you will be able to: -- Analyze a slow query with easy to use tools -- Understand in what order are executed the different parts of a query +- Analyze a slow query with easy-to-use tools +- Understand the order in which the different parts of a query are executed ## Basics of PostgreSQL analysis To solve today's problem, we are going to need several tools and concepts. -SQL is a query language: you write in this particular language what you want to be computed. This query is then processed by the **engine** - the actual computer that will do all the work. Since you didn't describe how to process the data, the engine will translate the query into processing steps it can execute. This is called a **query plan**[^wikipedia-query-plan]. +SQL is a query language: you write in this particular language what you want to be computed. This query is then processed by the **engine** - the actual computer that will do all the work. Since you didn't describe how to process the data, the engine will translate the query into processing steps - a **query plan**[^wikipedia-query-plan] - it can execute. + +The same query could be translated into different plans by: -The same query could be translated into different plans by different engines (Postgres, MySQL), different versions of the same engine (performance gains between two Postgres versions is often because of plan optimizations), or even the exact same engine, but with different underlying data. +- different engines: Postgres, MySQL, ... +- different versions of the same engine (performance gains between two Postgres versions are often due to plan optimizations) +- the exact same engine, but with different underlying data <!-- TODO: Add scheme --> -We will see that there are several way to write a query that outputs the same results, but each way will guide the engine into different directions on what plan will be used. To understand how our query is translated by Postgres, we use `EXPLAIN ANALYZE` before our query to ask Postgres to output the plan instead of the results. The `ANALYZE` keyword will even add runtime data (timings, costs) to the plan, after executing the query. +We will see that there are several ways to write a query that outputs the same results, but each way will guide the engine into different directions on what plan will be used. To understand how our query is translated by Postgres, we use `EXPLAIN ANALYZE` before our query to ask Postgres to output the plan instead of the results. The `ANALYZE` keyword will even add runtime data (timings, costs) to the plan, after executing the query. We will then use a **visualization tool** to make it easier to read the plan. I use **Dalibo Explain**[^dalibo-explain], which is a free tool that can be used online. It is also open source and standalone, so you can download the HTML page to use it locally. @@ -42,7 +52,7 @@ I will also use **hyperfine**[^hyperfine] to benchmark the different queries. Yo ## 1. Aggregation -My first approach to this problem was to use tools I was taught when learning SQL at school. If I want to summarize some information, aggregations are the way to go, right? +My first approach to this problem was to use tools I was taught when learning SQL at university. If I want to summarize some information, aggregations are the way to go, right? ```sql SELECT @@ -70,7 +80,7 @@ GROUP BY products.id; ``` -But of course it’s not always that easy. This query give the wrong results, because before grouping and aggregating the rows, you get a single row for every couple of order and review for each product. You should quickly see that `num_reviews` and `num_orders` are the same, and have a mean value of 1000 instead of 100 or 10 as stated in the introduction. +But of course it’s not always that easy. This query give the wrong results, because before grouping and aggregating the rows, you get a single row for every `(order, review)` tuple for each product. You should quickly see that `num_reviews` and `num_orders` are the same, and have a mean value of 1000 instead of 100 or 10 as stated in the introduction. ```sql SELECT @@ -99,7 +109,7 @@ On my computer, with the data provided in the introduction, I measured this quer <!-- Reanalyse plan because of time diff (2.4 -> 1.6) --> -Using Dalibo, we can see what are the most costly parts of the query: +Using Dalibo, we can see which are the most costly parts of the query:  @@ -140,7 +150,7 @@ Benchmark 1: psql postgres://postgres@localhost:5432/postgres -f subqueries.sql Range (min … max): 598.4 ms … 618.9 ms 10 runs ``` -This query **takes now 607ms**. Sounds like a good speed-up +This query **takes now 607ms**. Sounds like a good 3x speed-up. When we analyze the new query plan, we can see some major differences: @@ -152,9 +162,9 @@ There are now sub-plans: because the subqueries reference a column of the outer These queries, even if numerous, are highly efficient because they can use the indexes we created to quickly find to corresponding rows: this is what the Bitmap Index Scan and Bitmap Heap Scan show. -## 3. Merging CTEs +## 3. Merging Common Table Expressions -We have seen two ways to think about this problem, both very different. What would be a better query is a query that avoids this enormous intermediate table, but also avoids having too many loops. +We have seen two ways to think about this problem, both very different. A much better query that would have the best of both approaches is one that would avoid the enormous intermediate table while also avoiding having too many loops. One way to achieve this is to split our computation into several parts. Basically, we can compute separately the reviews count and the orders count, because each aggregation is independent. We will have subqueries, but this time uncorrelated, so they will run only once each. We can use the **Common Table Expression**[^common-table-expressions] pattern to make it easier to read. Then in a final stage, we combine the results of our aggregations. @@ -196,21 +206,21 @@ Benchmark 1: psql postgres://postgres@localhost:5432/postgres -f merging-ctes.sq Range (min … max): 85.8 ms … 94.5 ms 32 runs ``` -This query takes **90ms**, which is an even greater speedup. What happened this time? +There you go, this query takes **90ms**, which is a 15x speedup compared to the first approach. What happened this time? - Get all the reviews, then aggregate by product id in one pass (HashAggregate) -- Same goes for orders, but width the addition of parallelism (cost CPU, but reduce time) +- Same goes for orders, but with the addition of parallelism (costs CPU, but reduces execution time) - The final merging stage is very fast because of the small size of the data at this point in the pipeline  One requirement for this usage is that the group by is on the product id : no need to load the product to group rows together. For instance, if we wanted to group by the product creation date, it would require intermediate join on the product table. -## 4. Adding filtering requirements +## Adding filtering requirements Now that we found a query that executes in 90ms, compared to the initial 1622ms, it seems that we found a good pattern. -But the last query has a drawback that the previous examples do not show: the two first stages have no clues about the products they are aggregating on. +But the last query has a drawback that the previous examples do not show: the two first stages have no clue about the products they are aggregating on. If we slightly change the problem requirements, such as we only want to compute on a subsets of the products, this last query will still compute the aggregations over all reviews and orders, but drop most of the results in the last stage. @@ -224,11 +234,7 @@ We can show this by adding a modulo filter on the product_id to only select 10% ## Wrapping up -This problem is a simplification of real issues I had at work: we were using a query base on correlated subqueries, and we run into some performance issues. - -I though that using aggregations would solve it, but in fact it slowed down, and even added bugs from counting duplicate rows. - -There is not a single way good way to write queries, but here are the takeaways I got from this experience: +There is not a single good way to write queries. As often, it is about trade-offs. But had I to choose, here are the key takeaways I got from this experience: - Avoid queries which use aggregation functions on several "directions": you will likely shoot yourself in the foot and obtain wrong results - When fetching a lot of data, it is efficient to aggregate each "direction" alone, before merging all results together with the main table: it will leverage parallelism and reduce the memory used diff --git a/content/posts/postgres-aggregates/setup.sql b/content/posts/postgres-aggregates/setup.sql index 622f02b..551a979 100644 --- a/content/posts/postgres-aggregates/setup.sql +++ b/content/posts/postgres-aggregates/setup.sql @@ -1,36 +1,50 @@ -CREATE TABLE products ( - id SERIAL PRIMARY KEY, +CREATE TABLE products( + id serial PRIMARY KEY, name varchar(255) NOT NULL, description text, - price numeric(10,2) NOT NULL, + price numeric(10, 2) NOT NULL, created_at timestamp NOT NULL DEFAULT NOW() ); -INSERT INTO products (name, description, price) -SELECT concat('product_', n), 'This is a product description.', trunc(random() * 1000 + 1) -FROM generate_series(1, 10000) as n; +INSERT INTO products(name, description, price) +SELECT + CONCAT('product_', n), + 'This is a product description.', + TRUNC(RANDOM() * 1000 + 1) +FROM + GENERATE_SERIES(1, 10000) AS n; -CREATE TABLE orders ( - id SERIAL PRIMARY KEY, - order_date DATE NOT NULL, - customer_name TEXT NOT NULL, - product_id INTEGER NOT NULL REFERENCES products(id) +CREATE TABLE orders( + id serial PRIMARY KEY, + order_date date NOT NULL, + customer_name text NOT NULL, + product_id integer NOT NULL REFERENCES products(id) ); -INSERT INTO orders (order_date, customer_name, product_id) -SELECT now(), concat('customer_', n), floor(random() * 10000) + 1 -FROM generate_series(1, 1000000) as n; +INSERT INTO orders(order_date, customer_name, product_id) +SELECT + NOW(), + CONCAT('customer_', n), + FLOOR(RANDOM() * 10000) + 1 +FROM + GENERATE_SERIES(1, 1000000) AS n; -CREATE TABLE reviews ( - id SERIAL PRIMARY KEY, - review_date TIMESTAMP NOT NULL, - comment TEXT NOT NULL, - product_id INTEGER NOT NULL REFERENCES products(id) +CREATE TABLE reviews( + id serial PRIMARY KEY, + review_date timestamp NOT NULL, + comment text NOT NULL, + product_id integer NOT NULL REFERENCES products(id) ); -INSERT INTO reviews (review_date, comment, product_id) -SELECT now(), concat('Comment for review #', n), floor(random() * 10000) + 1 -FROM generate_series(1, 100000) as n; +INSERT INTO reviews(review_date, comment, product_id) +SELECT + NOW(), + CONCAT('Comment for review #', n), + FLOOR(RANDOM() * 10000) + 1 +FROM + GENERATE_SERIES(1, 100000) AS n; + +CREATE INDEX reviews_product_id_idx ON reviews(product_id); + +CREATE INDEX orders_product_id_idx ON orders(product_id); -CREATE INDEX reviews_product_id_idx ON reviews (product_id); -CREATE INDEX orders_product_id_idx ON orders (product_id); diff --git a/content/posts/postgres-aggregates/subqueries.png b/content/posts/postgres-aggregates/subqueries.png Binary files differindex 5c2d66f..8d0592a 100644 --- a/content/posts/postgres-aggregates/subqueries.png +++ b/content/posts/postgres-aggregates/subqueries.png |
