From 125f2bd4bec733e79ef8596a88b38497e66037c4 Mon Sep 17 00:00:00 2001 From: Martin Kagamino Lehoux Date: Sun, 29 Mar 2026 15:51:33 +0200 Subject: Consolidate results --- .../posts/postgres-aggregates-correction/index.md | 205 +++++++++++++++++++-- .../merged-ctes-leaf-filter.png | Bin 0 -> 59789 bytes .../merged-ctes-limits.png | Bin 0 -> 26043 bytes .../merged-ctes-root-filter.png | Bin 0 -> 51670 bytes .../postgres-aggregates-correction/merged-ctes.png | Bin 0 -> 61716 bytes .../postgres-aggregates-correction/queries.sql | 183 ++++++++++++++++++ .../subqueries-fixed-limits.png | Bin 0 -> 28684 bytes .../subqueries-fixed.png | Bin 0 -> 37737 bytes .../subqueries-initial-limits.png | Bin 0 -> 34432 bytes .../subqueries-initial.png | Bin 0 -> 35804 bytes .../subqueries-leaf-filter.png | Bin 0 -> 36648 bytes .../subqueries-root-filter.png | Bin 0 -> 30659 bytes 12 files changed, 371 insertions(+), 17 deletions(-) create mode 100644 content/posts/postgres-aggregates-correction/merged-ctes-leaf-filter.png create mode 100644 content/posts/postgres-aggregates-correction/merged-ctes-limits.png create mode 100644 content/posts/postgres-aggregates-correction/merged-ctes-root-filter.png create mode 100644 content/posts/postgres-aggregates-correction/merged-ctes.png create mode 100644 content/posts/postgres-aggregates-correction/queries.sql create mode 100644 content/posts/postgres-aggregates-correction/subqueries-fixed-limits.png create mode 100644 content/posts/postgres-aggregates-correction/subqueries-fixed.png create mode 100644 content/posts/postgres-aggregates-correction/subqueries-initial-limits.png create mode 100644 content/posts/postgres-aggregates-correction/subqueries-initial.png create mode 100644 content/posts/postgres-aggregates-correction/subqueries-leaf-filter.png create mode 100644 content/posts/postgres-aggregates-correction/subqueries-root-filter.png diff --git a/content/posts/postgres-aggregates-correction/index.md b/content/posts/postgres-aggregates-correction/index.md index e6e45af..f93fea8 100644 --- a/content/posts/postgres-aggregates-correction/index.md +++ b/content/posts/postgres-aggregates-correction/index.md @@ -9,16 +9,55 @@ 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 query now took 90ms -when the subquery one was 600ms. +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. + +```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; +``` + +![Initial subqueries query plan](subqueries-initial.png) + +And as a reminder, here's what my "best query" - aka "merge CTEs" - looked like. + +```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; +``` + +![Merged CTEs query plan](merged-ctes.png) As I recently was sharing my postgres experience with a coworker, 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 was with 420ms), it's a common idea +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 to go and try replace these two blocks by a Index Only Scan. -The idea of a Bitmap Index Scan + Bitmap Heap Scan is to use the index to filter (or prefilter) the rows from the index, which is really fast, and then go fetch the actual data from the main Heap +The idea of a Bitmap Index Scan + Bitmap Heap Scan is to use the +index to filter (or prefilter) the rows from the index, which is +really fast, and then go fetch the actual data from the main Heap memory (the table per se). For instance, instead of scanning 10GB of data, and dumping 90% of it, you use the index to find the 10% interesting rows, and then fetch the Heap blocks where the data @@ -42,10 +81,20 @@ need to know how many rows were matched for each product. The initial 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 540ms (yeah, maybe a postgres upgrade -since then) to 85ms. Still a bit behind the previous winner, now at -75ms, but maybe not worth the complexity. Also note that the subquery -version gains more performance as we use root table filters. +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. + +```sql +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; +``` + +![Fixed subqueries query plan](subqueries-fixed.png) ## Going further @@ -63,15 +112,88 @@ 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. -Subquery: 85ms -> 15ms -CTE: 75ms -> 65ms +```sql +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; +``` + +![Subqueries root filter](subqueries-root-filter.png) + +```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 +WHERE products.price > 900; +``` + +![Merged CTEs root filter](merged-ctes-root-filter.png) + +Subquery: 100ms -> 15ms +CTE: 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 goes from 10ms to 100ms, and it doesn't make any sense. +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 +goes from 10ms to 100ms, and it doesn't make any sense. -CTE: 75ms -> 145ms -Subquery: 85ms -> 110ms +```sql +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; +``` + +![Subqueries leaf filter query plan](subqueries-leaf-filter.png) + +```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 + 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; +``` + +![Merged CTEs leaf filter query plan](merged-ctes-leaf-filter.png) + +CTE: 100ms -> 140ms +Subquery: 100ms -> 110ms ### Disk usage @@ -85,7 +207,7 @@ is explained by the very high number of uncorrelated loops. In this setup, it's not much of an issue, because the data is small (57MB to read all orders), I have much RAM available, and I'm the only one using it. But this is not the case in production systems. -Obviously tables are much largers (can easily become several GBs), +Obviously tables are much largers (can easily become tens of GBs), and data read from disk may often be discarded to let other queries cache disk pages. The global setting that controls how much memory is allocated to caching disk data is called `shared_buffers` (128MB @@ -123,9 +245,54 @@ services: rate: 3000 ``` -Subquery Bitmap Heap: 4600ms -Subquery Index Only: 90ms -CTE: 75ms +```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; +``` + +![Initial subqueries with limits](subqueries-initial-limits.png) + +```sql +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; +``` + +![Subqueries fixed with limits](subqueries-fixed-limits.png) + +```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; +``` + +![Merged CTEs with limits](merged-ctes-limits.png) + +Subquery Bitmap Heap: 4200ms +Subquery Index Only: 100ms +CTE: 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. @@ -135,3 +302,7 @@ Subquery Bitmap Heap Scan gets dirty. Data from tables can't fit in cache, so each independant loop has a high chance of having to read a page from disk. The Shared Hit/Read ratio is ~0.5, and total data read from disk goes from 7.7MB in the previous case to 5.5GB. + +## TODO + +- Uniform SQL + results + query plan diff --git a/content/posts/postgres-aggregates-correction/merged-ctes-leaf-filter.png b/content/posts/postgres-aggregates-correction/merged-ctes-leaf-filter.png new file mode 100644 index 0000000..7312317 Binary files /dev/null and b/content/posts/postgres-aggregates-correction/merged-ctes-leaf-filter.png differ diff --git a/content/posts/postgres-aggregates-correction/merged-ctes-limits.png b/content/posts/postgres-aggregates-correction/merged-ctes-limits.png new file mode 100644 index 0000000..09889f3 Binary files /dev/null and b/content/posts/postgres-aggregates-correction/merged-ctes-limits.png differ diff --git a/content/posts/postgres-aggregates-correction/merged-ctes-root-filter.png b/content/posts/postgres-aggregates-correction/merged-ctes-root-filter.png new file mode 100644 index 0000000..f914ac0 Binary files /dev/null and b/content/posts/postgres-aggregates-correction/merged-ctes-root-filter.png differ diff --git a/content/posts/postgres-aggregates-correction/merged-ctes.png b/content/posts/postgres-aggregates-correction/merged-ctes.png new file mode 100644 index 0000000..225b263 Binary files /dev/null and b/content/posts/postgres-aggregates-correction/merged-ctes.png differ diff --git a/content/posts/postgres-aggregates-correction/queries.sql b/content/posts/postgres-aggregates-correction/queries.sql new file mode 100644 index 0000000..ce8bb55 --- /dev/null +++ b/content/posts/postgres-aggregates-correction/queries.sql @@ -0,0 +1,183 @@ +-- ============================================================ +-- 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-fixed-limits.png b/content/posts/postgres-aggregates-correction/subqueries-fixed-limits.png new file mode 100644 index 0000000..9b3df9a Binary files /dev/null and b/content/posts/postgres-aggregates-correction/subqueries-fixed-limits.png differ diff --git a/content/posts/postgres-aggregates-correction/subqueries-fixed.png b/content/posts/postgres-aggregates-correction/subqueries-fixed.png new file mode 100644 index 0000000..cc9ec37 Binary files /dev/null and b/content/posts/postgres-aggregates-correction/subqueries-fixed.png differ diff --git a/content/posts/postgres-aggregates-correction/subqueries-initial-limits.png b/content/posts/postgres-aggregates-correction/subqueries-initial-limits.png new file mode 100644 index 0000000..9e1b761 Binary files /dev/null and b/content/posts/postgres-aggregates-correction/subqueries-initial-limits.png differ diff --git a/content/posts/postgres-aggregates-correction/subqueries-initial.png b/content/posts/postgres-aggregates-correction/subqueries-initial.png new file mode 100644 index 0000000..6b33ff6 Binary files /dev/null and b/content/posts/postgres-aggregates-correction/subqueries-initial.png differ diff --git a/content/posts/postgres-aggregates-correction/subqueries-leaf-filter.png b/content/posts/postgres-aggregates-correction/subqueries-leaf-filter.png new file mode 100644 index 0000000..feb5cba Binary files /dev/null and b/content/posts/postgres-aggregates-correction/subqueries-leaf-filter.png differ diff --git a/content/posts/postgres-aggregates-correction/subqueries-root-filter.png b/content/posts/postgres-aggregates-correction/subqueries-root-filter.png new file mode 100644 index 0000000..8710b47 Binary files /dev/null and b/content/posts/postgres-aggregates-correction/subqueries-root-filter.png differ -- cgit v1.3