diff options
| -rw-r--r-- | content/posts/postgres-aggregates/aggregation.png | bin | 0 -> 51255 bytes | |||
| -rw-r--r-- | content/posts/postgres-aggregates/aggregation.sql | 20 | ||||
| -rw-r--r-- | content/posts/postgres-aggregates/aggregation.txt | 36 | ||||
| -rw-r--r-- | content/posts/postgres-aggregates/docker-compose.yml | 9 | ||||
| -rw-r--r-- | content/posts/postgres-aggregates/draft.md | 68 | ||||
| -rw-r--r-- | content/posts/postgres-aggregates/index.md | 223 | ||||
| -rw-r--r-- | content/posts/postgres-aggregates/merging-ctes.png | bin | 0 -> 57343 bytes | |||
| -rw-r--r-- | content/posts/postgres-aggregates/merging-ctes.sql | 34 | ||||
| -rw-r--r-- | content/posts/postgres-aggregates/merging-ctes.txt | 71 | ||||
| -rw-r--r-- | content/posts/postgres-aggregates/sequential-ctes.sql | 28 | ||||
| -rw-r--r-- | content/posts/postgres-aggregates/setup.sql | 36 | ||||
| -rw-r--r-- | content/posts/postgres-aggregates/subqueries.png | bin | 0 -> 37353 bytes | |||
| -rw-r--r-- | content/posts/postgres-aggregates/subqueries.sql | 28 | ||||
| -rw-r--r-- | content/posts/postgres-aggregates/subqueries.txt | 39 |
14 files changed, 592 insertions, 0 deletions
diff --git a/content/posts/postgres-aggregates/aggregation.png b/content/posts/postgres-aggregates/aggregation.png Binary files differnew file mode 100644 index 0000000..92fe883 --- /dev/null +++ b/content/posts/postgres-aggregates/aggregation.png diff --git a/content/posts/postgres-aggregates/aggregation.sql b/content/posts/postgres-aggregates/aggregation.sql new file mode 100644 index 0000000..ebb9ed5 --- /dev/null +++ b/content/posts/postgres-aggregates/aggregation.sql @@ -0,0 +1,20 @@ +EXPLAIN ( + ANALYZE, + COSTS, + VERBOSE, + BUFFERS +) +SELECT + products.id AS product_id, + products.name AS product_name, + COUNT(DISTINCT reviews.id) AS num_reviews, + COUNT(DISTINCT orders.id) AS num_orders +FROM + products + LEFT JOIN reviews ON reviews.product_id = products.id + LEFT JOIN orders ON orders.product_id = products.id + -- WHERE + -- products.id % 10 = 0 +GROUP BY + products.id; + diff --git a/content/posts/postgres-aggregates/aggregation.txt b/content/posts/postgres-aggregates/aggregation.txt new file mode 100644 index 0000000..19fe5a9 --- /dev/null +++ b/content/posts/postgres-aggregates/aggregation.txt @@ -0,0 +1,36 @@ + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + GroupAggregate (cost=1.00..280600.32 rows=10000 width=32) (actual time=5.206..2498.463 rows=10000 loops=1) + Output: products.id, products.name, count(DISTINCT reviews.id), count(DISTINCT orders.id) + Group Key: products.id + Buffers: shared hit=1093923 + -> Merge Left Join (cost=1.00..205500.32 rows=10000000 width=24) (actual time=4.720..1482.634 rows=9995979 loops=1) + Output: products.id, products.name, reviews.id, orders.id + Merge Cond: (products.id = orders.product_id) + Buffers: shared hit=1093923 + -> Merge Left Join (cost=0.58..7362.19 rows=100000 width=20) (actual time=4.706..51.596 rows=100000 loops=1) + Output: products.id, products.name, reviews.id + Merge Cond: (products.id = reviews.product_id) + Buffers: shared hit=99765 + -> Index Scan using products_pkey on public.products (cost=0.29..387.29 rows=10000 width=16) (actual time=0.010..1.926 rows=10000 loops=1) + Output: products.id, products.name, products.description, products.price, products.created_at + Buffers: shared hit=143 + -> Index Scan using reviews_product_id_idx on public.reviews (cost=0.29..5699.91 rows=100000 width=8) (actual time=0.008..34.368 rows=100000 loops=1) + Output: reviews.id, reviews.review_date, reviews.comment, reviews.product_id + Buffers: shared hit=99622 + -> Materialize (cost=0.42..50388.13 rows=1000000 width=8) (actual time=0.010..626.788 rows=9995969 loops=1) + Output: orders.id, orders.order_date, orders.customer_name, orders.product_id + Buffers: shared hit=994158 + -> Index Scan using orders_product_id_idx on public.orders (cost=0.42..47888.13 rows=1000000 width=8) (actual time=0.009..324.399 rows=1000000 loops=1) + Output: orders.id, orders.order_date, orders.customer_name, orders.product_id + Buffers: shared hit=994158 + Planning: + Buffers: shared hit=181 + Planning Time: 0.467 ms + JIT: + Functions: 15 + Options: Inlining false, Optimization false, Expressions true, Deforming true + Timing: Generation 0.365 ms, Inlining 0.000 ms, Optimization 0.194 ms, Emission 4.497 ms, Total 5.055 ms + Execution Time: 2511.587 ms +(32 rows) + diff --git a/content/posts/postgres-aggregates/docker-compose.yml b/content/posts/postgres-aggregates/docker-compose.yml new file mode 100644 index 0000000..e0e804c --- /dev/null +++ b/content/posts/postgres-aggregates/docker-compose.yml @@ -0,0 +1,9 @@ +version: "3" +services: + postgres: + image: postgres + restart: always + environment: + POSTGRES_HOST_AUTH_METHOD: trust + ports: + - "5432:5432" diff --git a/content/posts/postgres-aggregates/draft.md b/content/posts/postgres-aggregates/draft.md new file mode 100644 index 0000000..ab9cfd1 --- /dev/null +++ b/content/posts/postgres-aggregates/draft.md @@ -0,0 +1,68 @@ +## 4. Sequential CTEs + +```sql +WITH product_with_reviews AS ( + SELECT + products.id as product_id, + products.name as product_name, + count(reviews.id) AS num_reviews + FROM + products + LEFT JOIN reviews ON reviews.product_id = products.id + GROUP BY + products.id, + products.name +) +SELECT + product_with_reviews.product_id, + product_name, + num_reviews, + count(id) AS num_orders +FROM + product_with_reviews + LEFT JOIN orders ON orders.product_id = product_with_reviews.product_id +GROUP BY + product_with_reviews.product_id, + product_name, + num_reviews; +``` + +``` +Benchmark 1: psql postgres://postgres@localhost:5432/postgres -f sequential-ctes.sql + Time (mean ± σ): 360.9 ms ± 4.5 ms [User: 15.5 ms, System: 4.2 ms] + Range (min … max): 355.0 ms … 369.2 ms 10 runs +``` + +**This query takes 361ms.** + +It’s longer than the merged CTEs, basically because the hash join happens before aggregation. So we have much more data to process at the end of the pipeline. + +<!-- TODO: Replace by screenshot --> + + + +## 5. Going further + +| Query | Time 100% | Time 10% | +| -------------- | --------- | -------- | +| Aggregation | 2450 | 270 | +| Merged CTE | 90 | 80 | +| Subquery | 650 | 60 | +| Sequential CTE | 361 | 85 | + +- What happens if we filter out 10% ? 90% of products base of products table? + - Sequential CTE would be faster, adding the filter in the beginning + - Aggregation & Subquery too would be faster + - Merged CTE would still do all the work, or almost (less data to process at some point) +- We can see plans that scale almost linearly : Aggregation & Subquery +- Sequential subquery does not scale as well, I don’t know why +- But Merged CTE only scales down for a small fraction, because most of the work is done without knowing what products will be selected. +- Maybe a conclusion is that there is no silver bullet: we can make queries have a small cost when done a a large queryset, compared to others, but when the queryset is much smaller, the gain will be less important than others. +- The same way as finding 1 largest element is a seq scan O(n), but finding n largest elements out of n should use a sort O(nlogn). Finding the sweet spot where to swap your algorithm is finding k such as kn ~ nlogn ⇒ k = log n. Ex: for 10k elements, if you need to find more than 10, use sort. + +## TODOs and open questions + +- Are there differences using different postgres versions? +- Add charts to visualize +- Traffic analytics? +- Short intro to postgres engine and query plans diff --git a/content/posts/postgres-aggregates/index.md b/content/posts/postgres-aggregates/index.md new file mode 100644 index 0000000..bd99993 --- /dev/null +++ b/content/posts/postgres-aggregates/index.md @@ -0,0 +1,223 @@ +--- +title: "Efficiente PostgreSQL Aggregations" +draft: true +date: +description: "Diving into PostgreSQL query plans to improve our tooling" +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 calmly an article of my findings working it through. + +Here is the simpler version of the problem: + +- We a have a simple schema +- We want to make some efficient computations on multiples tables + - 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 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 + +By the end of this reading, I hoope you will be able to: + +- Analyze a slow query with easy to use tools +- Understand in what order will the different parts of a query be executed + +## 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? + +<!-- {{< highlight sql >}} --> + +```sql +SELECT + products.id AS product_id, + COUNT(reviews.id) AS num_reviews +FROM + products +LEFT JOIN reviews ON reviews.product_id = products.id +GROUP BY + products.id; +``` + +<!-- {{</ highlight >}} --> + +With the above query, I get a single row for each product, with the correct count of reviews for each. Easy, isn’t it? I just had to use this pattern on several joins + +```sql +SELECT + products.id AS product_id, + COUNT(reviews.id) AS num_reviews, + COUNT(orders.id) AS num_orders +FROM + products + LEFT JOIN reviews ON reviews.product_id = products.id + LEFT JOIN orders ON orders.product_id = products.id +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. + +```sql +SELECT + products.id AS product_id, + COUNT(DISTINCT reviews.id) AS num_reviews, + COUNT(DISTINCT orders.id) AS num_orders +FROM + products + LEFT JOIN reviews ON reviews.product_id = products.id + LEFT JOIN orders ON orders.product_id = products.id +GROUP BY + products.id; +``` + +Adding the `DISTINCT` keyword here fixes this behaviour, and we finally get what we were looking for. Note that this fix only works here because the aggregation function is `COUNT`: it would not have worked if we were computing the average review rating. + +No that we found an obvious way to compute the correct result, let’s take a look at performances. + +> 💡 Quick disclaimer: I am in no way making benchmarks for different query patterns in PostgreSQL. The differences I show in this article are wide enough, alongside the query plans, to understand how the PostgreSQL query engine works and in what way it affects performance. + +``` +Benchmark 1: psql postgres://postgres@localhost:5432/postgres -f aggregation.sql + Time (mean ± σ): 1.622 s ± 0.028 s [User: 0.008 s, System: 0.005 s] + Range (min … max): 1.590 s … 1.666 s 10 runs +``` + +On my computer, with the data provided in the introduction, I measured this query **takes 1622ms** to execute. This seems too slow for a “web app” usage where you would need to query this data on demand for a user. + +<!-- Reanalyse plan because of time diff (2.4 -> 1.6) --> + +Using Dalibo to understand the query plans produced by analysing this query, we can see what are the most costly parts of the query: + + + +- On the right an index scan taking 300ms, and the intermediate materialization taking 300ms +- The merge join of 10M rows (size of reviews x products) + +We can guess from this plan that all the data is merged into an enormous intermediate table, which is then processed for computing our aggregations. Let’s take these insights into account and try building a more efficient version. + +## 2. Subqueries + +We know that we want to avoid merging all the data together before making computations, because it adds a lot of memory pressure that slows the whole pipeline. + +Going all the way to the opposite way of thinking, we can simply make a subquery to compute the two counts for each one of the products. + +```sql +SELECT + products.id, + products.name, + ( + SELECT count(id) + FROM reviews + WHERE product_id = products.id + ) as num_reviews, + ( + SELECT count(id) + FROM orders + WHERE product_id = products.id + ) as num_orders +FROM + products; +``` + +As a side effect, we can see that rewriting the query this way adds independence, and we don’t need to use `DISTINCT` to avoid counting some rows several times. + +``` +Benchmark 1: psql postgres://postgres@localhost:5432/postgres -f subqueries.sql + Time (mean ± σ): 607.0 ms ± 5.7 ms [User: 16.5 ms, System: 4.1 ms] + Range (min … max): 598.4 ms … 618.9 ms 10 runs +``` + +This query **takes now 607ms**. Sounds like a good speed-up + +When we analyse the new query plan, we can see some major differences: + + + +There are now subplans: because the subqueries reference a column of the outer query (`products.id`), these must be run for each row that is found in `products`. This is shown by the keywords `loops: 10000`. This is called a **correlated subquery**. + +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 + +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. + +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** pattern to make it easier to read. Then in a final stage, we combine the results of our aggregations. + +```sql +WITH product_reviews AS ( + SELECT + product_id, + count(id) AS num_reviews + FROM + reviews + GROUP BY + product_id +), +product_orders AS ( + SELECT + product_id, + count(id) AS num_orders + FROM + orders + GROUP BY + product_id +) +SELECT + products.id, + products.name, + COALESCE(product_reviews.num_reviews, 0) AS num_reviews, + COALESCE(product_orders.num_orders, 0) AS num_orders +FROM + products + LEFT JOIN product_reviews ON products.id = product_reviews.product_id + LEFT JOIN product_orders ON products.id = product_orders.product_id; +``` + +``` +Benchmark 1: psql postgres://postgres@localhost:5432/postgres -f merging-ctes.sql + Time (mean ± σ): 90.3 ms ± 2.4 ms [User: 14.1 ms, System: 3.7 ms] + Range (min … max): 85.8 ms … 94.5 ms 32 runs +``` + +**This query takes 90ms.** + +> In a previous version, I forgot to remove the distinct keywords. It added useless complexity to the query by using all those index scans, whereas we needed all data. + +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. + +What happens? + +- 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) + + + +## 4. There is no silver bullet + +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. + +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. + +We can show this by adding a modulo filter on the product_id to only select 10% of the products, randomly. We can see that the two first queries perform almost linearly better, when the last one has a negligible improvement + +| Query | Time 100% (ms) | Time 10% (ms) | +| ----------- | -------------- | ------------- | +| Aggregation | 1622 | 184 | +| Subquery | 607 | 69 | +| Merged CTE | 90 | 75 | + +## Wrapping up + +- 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 mergin all results together with the main table: it will leverage parallelism and reduce the memory used +- When selecting only a subset of the main table, using subqueries will see the most impressive speed gains (if the filter depends on the main table data: filtering products based on the number of reviews they received will append after having computed all the data) + +## Resources + +- [Hyperfine](https://github.com/sharkdp/hyperfine) for benchmarking +- [Dalibo Explain](https://explain.dalibo.com/) for query plan analysis diff --git a/content/posts/postgres-aggregates/merging-ctes.png b/content/posts/postgres-aggregates/merging-ctes.png Binary files differnew file mode 100644 index 0000000..4ce435e --- /dev/null +++ b/content/posts/postgres-aggregates/merging-ctes.png diff --git a/content/posts/postgres-aggregates/merging-ctes.sql b/content/posts/postgres-aggregates/merging-ctes.sql new file mode 100644 index 0000000..2061efd --- /dev/null +++ b/content/posts/postgres-aggregates/merging-ctes.sql @@ -0,0 +1,34 @@ +EXPLAIN ( + ANALYZE, + COSTS, + VERBOSE, + BUFFERS +) WITH product_reviews AS ( + SELECT + product_id, + COUNT(id) AS num_reviews + FROM + reviews + GROUP BY + product_id), + product_orders AS ( + SELECT + product_id, + COUNT(id) AS num_orders + FROM + orders + GROUP BY + product_id +) + SELECT + products.id, + products.name, + COALESCE(product_reviews.num_reviews, 0) AS num_reviews, + COALESCE(product_orders.num_orders, 0) AS num_orders +FROM + products + LEFT JOIN product_reviews ON products.id = product_reviews.product_id + LEFT JOIN product_orders ON products.id = product_orders.product_id; + +-- WHERE +-- products.id % 10 = 0 diff --git a/content/posts/postgres-aggregates/merging-ctes.txt b/content/posts/postgres-aggregates/merging-ctes.txt new file mode 100644 index 0000000..5c958dd --- /dev/null +++ b/content/posts/postgres-aggregates/merging-ctes.txt @@ -0,0 +1,71 @@ + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------------------------------------- + Hash Left Join (cost=19871.86..20138.39 rows=10000 width=32) (actual time=84.427..88.463 rows=10000 loops=1) + Output: products.id, products.name, COALESCE(product_reviews.num_reviews, '0'::bigint), COALESCE(product_orders.num_orders, '0'::bigint) + Inner Unique: true + Hash Cond: (products.id = product_orders.product_id) + Buffers: shared hit=8402 + -> Hash Left Join (cost=2754.21..2994.48 rows=10000 width=24) (actual time=19.962..22.583 rows=10000 loops=1) + Output: products.id, products.name, product_reviews.num_reviews + Inner Unique: true + Hash Cond: (products.id = product_reviews.product_id) + Buffers: shared hit=1049 + -> Seq Scan on public.products (cost=0.00..214.00 rows=10000 width=16) (actual time=0.002..0.719 rows=10000 loops=1) + Output: products.id, products.name, products.description, products.price, products.created_at + Buffers: shared hit=114 + -> Hash (cost=2631.44..2631.44 rows=9822 width=12) (actual time=19.909..19.911 rows=10000 loops=1) + Output: product_reviews.num_reviews, product_reviews.product_id + Buckets: 16384 Batches: 1 Memory Usage: 558kB + Buffers: shared hit=935 + -> Subquery Scan on product_reviews (cost=2435.00..2631.44 rows=9822 width=12) (actual time=17.667..18.894 rows=10000 loops=1) + Output: product_reviews.num_reviews, product_reviews.product_id + Buffers: shared hit=935 + -> HashAggregate (cost=2435.00..2533.22 rows=9822 width=12) (actual time=17.667..18.342 rows=10000 loops=1) + Output: reviews.product_id, count(reviews.id) + Group Key: reviews.product_id + Batches: 1 Memory Usage: 1425kB + Buffers: shared hit=935 + -> Seq Scan on public.reviews (cost=0.00..1935.00 rows=100000 width=8) (actual time=0.011..4.796 rows=100000 loops=1) + Output: reviews.id, reviews.review_date, reviews.comment, reviews.product_id + Buffers: shared hit=935 + -> Hash (cost=16993.16..16993.16 rows=9959 width=12) (actual time=64.403..64.453 rows=10000 loops=1) + Output: product_orders.num_orders, product_orders.product_id + Buckets: 16384 Batches: 1 Memory Usage: 558kB + Buffers: shared hit=7353 + -> Subquery Scan on product_orders (cost=16793.98..16993.16 rows=9959 width=12) (actual time=62.164..63.412 rows=10000 loops=1) + Output: product_orders.num_orders, product_orders.product_id + Buffers: shared hit=7353 + -> Finalize HashAggregate (cost=16793.98..16893.57 rows=9959 width=12) (actual time=62.164..62.867 rows=10000 loops=1) + Output: orders.product_id, count(orders.id) + Group Key: orders.product_id + Batches: 1 Memory Usage: 1425kB + Buffers: shared hit=7353 + -> Gather (cost=14603.00..16694.39 rows=19918 width=12) (actual time=56.542..58.444 rows=30000 loops=1) + Output: orders.product_id, (PARTIAL count(orders.id)) + Workers Planned: 2 + Workers Launched: 2 + Buffers: shared hit=7353 + -> Partial HashAggregate (cost=13603.00..13702.59 rows=9959 width=12) (actual time=54.293..54.991 rows=10000 loops=3) + Output: orders.product_id, PARTIAL count(orders.id) + Group Key: orders.product_id + Batches: 1 Memory Usage: 1425kB + Buffers: shared hit=7353 + Worker 0: actual time=53.312..53.970 rows=10000 loops=1 + Batches: 1 Memory Usage: 1425kB + Buffers: shared hit=2406 + Worker 1: actual time=53.322..54.071 rows=10000 loops=1 + Batches: 1 Memory Usage: 1425kB + Buffers: shared hit=2395 + -> Parallel Seq Scan on public.orders (cost=0.00..11519.67 rows=416667 width=8) (actual time=0.006..13.835 rows=333333 loops=3) + Output: orders.id, orders.order_date, orders.customer_name, orders.product_id + Buffers: shared hit=7353 + Worker 0: actual time=0.008..13.768 rows=327216 loops=1 + Buffers: shared hit=2406 + Worker 1: actual time=0.007..13.584 rows=325720 loops=1 + Buffers: shared hit=2395 + Planning: + Buffers: shared hit=171 + Planning Time: 0.410 ms + Execution Time: 89.503 ms +(67 rows) + diff --git a/content/posts/postgres-aggregates/sequential-ctes.sql b/content/posts/postgres-aggregates/sequential-ctes.sql new file mode 100644 index 0000000..fa18acd --- /dev/null +++ b/content/posts/postgres-aggregates/sequential-ctes.sql @@ -0,0 +1,28 @@ +-- EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) +WITH product_with_reviews AS ( + SELECT + products.id AS product_id, + products.name AS product_name, + COUNT(reviews.id) AS num_reviews + FROM + products + LEFT JOIN reviews ON reviews.product_id = products.id + -- WHERE + -- products.id % 10 = 0 + GROUP BY + products.id, + products.name +) +SELECT + product_with_reviews.product_id, + product_name, + num_reviews, + COUNT(id) AS num_orders +FROM + product_with_reviews + LEFT JOIN orders ON orders.product_id = product_with_reviews.product_id +GROUP BY + product_with_reviews.product_id, + product_name, + num_reviews; + diff --git a/content/posts/postgres-aggregates/setup.sql b/content/posts/postgres-aggregates/setup.sql new file mode 100644 index 0000000..622f02b --- /dev/null +++ b/content/posts/postgres-aggregates/setup.sql @@ -0,0 +1,36 @@ +CREATE TABLE products ( + id SERIAL PRIMARY KEY, + name varchar(255) NOT NULL, + description text, + 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; + +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; + +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; + +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 differnew file mode 100644 index 0000000..5c2d66f --- /dev/null +++ b/content/posts/postgres-aggregates/subqueries.png diff --git a/content/posts/postgres-aggregates/subqueries.sql b/content/posts/postgres-aggregates/subqueries.sql new file mode 100644 index 0000000..38efccd --- /dev/null +++ b/content/posts/postgres-aggregates/subqueries.sql @@ -0,0 +1,28 @@ +EXPLAIN ( + ANALYZE, + COSTS, + VERBOSE, + BUFFERS +) +SELECT + products.id, + products.name, +( + SELECT + COUNT(id) + FROM + reviews + WHERE + product_id = products.id) AS num_reviews, +( + SELECT + COUNT(id) + FROM + orders + WHERE + product_id = products.id) AS num_orders +FROM + products; + +-- WHERE +-- products.id % 10 = 0; diff --git a/content/posts/postgres-aggregates/subqueries.txt b/content/posts/postgres-aggregates/subqueries.txt new file mode 100644 index 0000000..6c19212 --- /dev/null +++ b/content/posts/postgres-aggregates/subqueries.txt @@ -0,0 +1,39 @@ + QUERY PLAN +---------------------------------------------------------------------------------------------------------------------------------------------------- + Seq Scan on public.products (cost=0.00..4131733.02 rows=10000 width=32) (actual time=71.607..619.401 rows=10000 loops=1) + Output: products.id, products.name, (SubPlan 1), (SubPlan 2) + Buffers: shared hit=1142915 + SubPlan 1 + -> Aggregate (cost=41.42..41.43 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=10000) + Output: count(reviews.id) + Buffers: shared hit=119508 + -> Bitmap Heap Scan on public.reviews (cost=4.37..41.39 rows=10 width=4) (actual time=0.002..0.005 rows=10 loops=10000) + Output: reviews.id, reviews.review_date, reviews.comment, reviews.product_id + Recheck Cond: (reviews.product_id = products.id) + Heap Blocks: exact=99508 + Buffers: shared hit=119508 + -> Bitmap Index Scan on reviews_product_id_idx (cost=0.00..4.37 rows=10 width=0) (actual time=0.001..0.001 rows=10 loops=10000) + Index Cond: (reviews.product_id = products.id) + Buffers: shared hit=20000 + SubPlan 2 + -> Aggregate (cost=371.71..371.72 rows=1 width=8) (actual time=0.048..0.048 rows=1 loops=10000) + Output: count(orders.id) + Buffers: shared hit=1023293 + -> Bitmap Heap Scan on public.orders (cost=5.20..371.46 rows=100 width=4) (actual time=0.008..0.044 rows=100 loops=10000) + Output: orders.id, orders.order_date, orders.customer_name, orders.product_id + Recheck Cond: (orders.product_id = products.id) + Heap Blocks: exact=993293 + Buffers: shared hit=1023293 + -> Bitmap Index Scan on orders_product_id_idx (cost=0.00..5.17 rows=100 width=0) (actual time=0.002..0.002 rows=100 loops=10000) + Index Cond: (orders.product_id = products.id) + Buffers: shared hit=30000 + Planning: + Buffers: shared hit=137 + Planning Time: 0.307 ms + JIT: + Functions: 18 + Options: Inlining true, Optimization true, Expressions true, Deforming true + Timing: Generation 0.404 ms, Inlining 36.770 ms, Optimization 20.753 ms, Emission 13.916 ms, Total 71.843 ms + Execution Time: 632.764 ms +(35 rows) + |
