diff options
| -rw-r--r-- | assets/css/main.css | 8 | ||||
| -rw-r--r-- | content/posts/postgres-aggregates-correction/index.md | 73 | ||||
| -rw-r--r-- | content/posts/postgres-aggregates-correction/queries.sql | 183 | ||||
| -rw-r--r-- | content/posts/postgres-aggregates-correction/subqueries-original-limits.png (renamed from content/posts/postgres-aggregates-correction/subqueries-initial-limits.png) | bin | 34432 -> 34432 bytes | |||
| -rw-r--r-- | content/posts/postgres-aggregates-correction/subqueries-original.png (renamed from content/posts/postgres-aggregates-correction/subqueries-initial.png) | bin | 35804 -> 35804 bytes |
5 files changed, 63 insertions, 201 deletions
diff --git a/assets/css/main.css b/assets/css/main.css index 8c8b0b8..4248d9f 100644 --- a/assets/css/main.css +++ b/assets/css/main.css @@ -1,4 +1,10 @@ /* ======================================== + Base element overrides (post-reset) + ======================================== */ + +strong, b { font-weight: bold; } + +/* ======================================== CSS Custom Properties (Theme Colors) ======================================== */ @@ -96,7 +102,7 @@ hr { border-color: var(--color-border); } -p, hr, pre, dl { +p, hr, pre, dl, details { margin-bottom: 1.25rem; } diff --git a/content/posts/postgres-aggregates-correction/index.md b/content/posts/postgres-aggregates-correction/index.md index f93fea8..38ec504 100644 --- a/content/posts/postgres-aggregates-correction/index.md +++ b/content/posts/postgres-aggregates-correction/index.md @@ -9,9 +9,16 @@ categories: In my [first post](/posts/postgres-aggregates/), I expected to show that there was a better alternative to subqueries for large, -multi-join queries. The numbers were good, the best query takes 100ms -when the subquery one is 500ms. Here's what the subquery looked like. +multi-join queries. Here's a summary of the results, updated for +PostgreSQL 18: +| Query | Time | +|-------|-----:| +| Subquery | 500ms | +| Merged CTEs | 100ms | + + +{{< details summary="**Subquery — original**" >}} ```sql SELECT products.id, @@ -21,10 +28,10 @@ SELECT FROM products; ``` - - -And as a reminder, here's what my "best query" - aka "merge CTEs" - looked like. + +{{< /details >}} +{{< details summary="**Merged CTEs**" >}} ```sql WITH product_reviews AS ( SELECT product_id, COUNT(id) AS num_reviews @@ -47,9 +54,10 @@ LEFT JOIN product_orders ON products.id = product_orders.product_id; ```  +{{< /details >}} As I recently was sharing my postgres experience with a coworker, -I noted something weird in the subquery plan: each branch had a +I noted something weird in the subquery plan: each branch had a Bitmap Index Scan followed by a Bitmap Heap Scan. This is a common pattern, that we could say as "good enough". But when this is the bottleneck of a query (as it is with 360ms), it's a common idea @@ -79,12 +87,13 @@ need to know how many rows were matched for each product. ## A simple fix -The initial query contained `count(id)`, and replacing it with +The original query contained `count(id)`, and replacing it with `count(*)` indeed improved the plan. We now have an Index Only Scan, and the query is down from 500ms to 100ms, equal to the previous best. Also note that the subquery version gains more performance as we use root table filters. +{{< details summary="**Subquery — fixed**" >}} ```sql SELECT products.id, @@ -95,6 +104,13 @@ FROM products; ```  +{{< /details >}} + +| Query | Scan type | Time | +|-------|-----------|-----:| +| Subquery `count(id)` | Bitmap Index + Heap Scan | 500ms | +| Subquery `count(*)` | Index Only Scan | 100ms | +| Merged CTEs | Parallel Seq Scan | 100ms | ## Going further @@ -112,6 +128,7 @@ a price over 900 (the price is random between 0 and 1000), then the Subquery approach will filter before running the subqueries, and in this case reduce the number of subquery loops by 10x. +{{< details summary="**Subquery — root filter**" >}} ```sql SELECT products.id, @@ -123,7 +140,9 @@ WHERE products.price > 900; ```  +{{< /details >}} +{{< details summary="**Merged CTEs — root filter**" >}} ```sql WITH product_reviews AS ( SELECT product_id, COUNT(id) AS num_reviews @@ -147,16 +166,20 @@ WHERE products.price > 900; ```  +{{< /details >}} -Subquery: 100ms -> 15ms -CTE: 100ms -> 75ms +| Query | No filter | Root filter (`price > 900`) | +|-------|----------:|----------------------------:| +| Subquery `count(*)` | 100ms | 15ms | +| Merged CTEs | 100ms | 75ms | However, when the filtering is in a leaf table, the Index Only Scan can no longer be used (`where customer_name ilike '%0'`). As expected, the Subquery gets back its Bitmap Heap Scan, and duration goes up. -However, I can't explain why the CTE goes up too. The Parallel Sec Scan +However, I can't explain why the CTE goes up too. The Parallel Seq Scan goes from 10ms to 100ms, and it doesn't make any sense. +{{< details summary="**Subquery — leaf filter**" >}} ```sql SELECT products.id, @@ -167,7 +190,9 @@ FROM products; ```  +{{< /details >}} +{{< details summary="**Merged CTEs — leaf filter**" >}} ```sql WITH product_reviews AS ( SELECT product_id, COUNT(id) AS num_reviews @@ -191,9 +216,12 @@ LEFT JOIN product_orders ON products.id = product_orders.product_id; ```  +{{< /details >}} -CTE: 100ms -> 140ms -Subquery: 100ms -> 110ms +| Query | No filter | Leaf filter (`customer_name ilike '%0'`) | +|-------|----------:|-----------------------------------------:| +| Subquery `count(*)` | 100ms | 110ms | +| Merged CTEs | 100ms | 140ms | ### Disk usage @@ -220,6 +248,7 @@ I can use docker-compose and blkio_config settings to limit the disk. I will also use a much lower `shared_buffers` that can't cache all my data, like 16MB. +{{< details summary="**docker-compose.yml**" >}} ```yaml services: postgres: @@ -244,7 +273,9 @@ services: - path: /dev/nvme0n1 rate: 3000 ``` +{{< /details >}} +{{< details summary="**Subquery — count(id), Bitmap Heap Scan**" >}} ```sql SELECT products.id, @@ -254,8 +285,10 @@ SELECT FROM products; ``` - + +{{< /details >}} +{{< details summary="**Subquery — count(*), Index Only Scan**" >}} ```sql SELECT products.id, @@ -266,7 +299,9 @@ FROM products; ```  +{{< /details >}} +{{< details summary="**Merged CTEs**" >}} ```sql WITH product_reviews AS ( SELECT product_id, COUNT(id) AS num_reviews @@ -289,10 +324,13 @@ LEFT JOIN product_orders ON products.id = product_orders.product_id; ```  +{{< /details >}} -Subquery Bitmap Heap: 4200ms -Subquery Index Only: 100ms -CTE: 90ms +| Query | Scan type | Time (throttled disk) | +|-------|-----------|----------------------:| +| Subquery `count(id)` | Bitmap Heap Scan | 4200ms | +| Subquery `count(*)` | Index Only Scan | 100ms | +| Merged CTEs | Parallel Seq Scan | 90ms | I think I can explain the numbers. CTE, as before, only need to read once the data. So it's reading just the right amount from disk. @@ -305,4 +343,5 @@ read from disk goes from 7.7MB in the previous case to 5.5GB. ## TODO -- Uniform SQL + results + query plan +- hori centered images (mobile?) +- disk usage data diff --git a/content/posts/postgres-aggregates-correction/queries.sql b/content/posts/postgres-aggregates-correction/queries.sql deleted file mode 100644 index ce8bb55..0000000 --- a/content/posts/postgres-aggregates-correction/queries.sql +++ /dev/null @@ -1,183 +0,0 @@ --- ============================================================ --- postgres-aggregates-correction: benchmark queries --- Run each block separately and save the EXPLAIN output. --- ============================================================ - --- ------------------------------------------------------- --- 1. Subquery — count(id) — no filter [ORIGINAL, baseline] --- ------------------------------------------------------- -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; - - --- ------------------------------------------------------- --- 2. Subquery — count(*) — no filter [THE FIX] --- ------------------------------------------------------- -EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) -SELECT - products.id, - products.name, - (SELECT COUNT(*) FROM reviews WHERE product_id = products.id) AS num_reviews, - (SELECT COUNT(*) FROM orders WHERE product_id = products.id) AS num_orders -FROM products; - - --- ------------------------------------------------------- --- 3. CTE (merging) — count(id) — no filter --- ------------------------------------------------------- -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; - - --- ------------------------------------------------------- --- 4. Subquery — count(*) — root filter (price > 900) --- ------------------------------------------------------- -EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) -SELECT - products.id, - products.name, - (SELECT COUNT(*) FROM reviews WHERE product_id = products.id) AS num_reviews, - (SELECT COUNT(*) FROM orders WHERE product_id = products.id) AS num_orders -FROM products -WHERE products.price > 900; - - --- ------------------------------------------------------- --- 5. CTE (merging) — count(id) — root filter (price > 900) --- ------------------------------------------------------- -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.price > 900; - - --- ------------------------------------------------------- --- 6. Subquery — count(*) — leaf filter (customer_name ilike '%0') --- ------------------------------------------------------- -EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) -SELECT - products.id, - products.name, - (SELECT COUNT(*) FROM reviews WHERE product_id = products.id) AS num_reviews, - (SELECT COUNT(*) FROM orders WHERE product_id = products.id AND customer_name ILIKE '%0') AS num_orders -FROM products; - - --- ------------------------------------------------------- --- 7. CTE (merging) — count(id) — leaf filter (customer_name ilike '%0') --- ------------------------------------------------------- -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 - WHERE customer_name ILIKE '%0' - 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; - - --- ============================================================ --- DISK-THROTTLED PASS (run with Docker: shared_buffers=16MB, --- blkio capped at 125MB/s read — see docker-compose snippet) --- Run DISCARD ALL between queries to flush caches. --- ============================================================ - --- ------------------------------------------------------- --- D1. Subquery — count(id) — no filter [Bitmap Heap Scan expected] --- ------------------------------------------------------- -DISCARD ALL; -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; - - --- ------------------------------------------------------- --- D2. Subquery — count(*) — no filter [Index Only Scan expected] --- ------------------------------------------------------- -DISCARD ALL; -EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) -SELECT - products.id, - products.name, - (SELECT COUNT(*) FROM reviews WHERE product_id = products.id) AS num_reviews, - (SELECT COUNT(*) FROM orders WHERE product_id = products.id) AS num_orders -FROM products; - - --- ------------------------------------------------------- --- D3. CTE (merging) — count(id) — no filter --- ------------------------------------------------------- -DISCARD ALL; -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; diff --git a/content/posts/postgres-aggregates-correction/subqueries-initial-limits.png b/content/posts/postgres-aggregates-correction/subqueries-original-limits.png Binary files differindex 9e1b761..9e1b761 100644 --- a/content/posts/postgres-aggregates-correction/subqueries-initial-limits.png +++ b/content/posts/postgres-aggregates-correction/subqueries-original-limits.png diff --git a/content/posts/postgres-aggregates-correction/subqueries-initial.png b/content/posts/postgres-aggregates-correction/subqueries-original.png Binary files differindex 6b33ff6..6b33ff6 100644 --- a/content/posts/postgres-aggregates-correction/subqueries-initial.png +++ b/content/posts/postgres-aggregates-correction/subqueries-original.png |
