diff options
Diffstat (limited to 'content/posts/postgres-aggregates-correction')
12 files changed, 371 insertions, 17 deletions
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; +``` + + + +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; +``` + + 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; +``` + + ## 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; +``` + + + +```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; +``` + + + +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; +``` + + + +```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; +``` + + + +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; +``` + + + +```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; +``` + + + +```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; +``` + + + +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 Binary files differnew file mode 100644 index 0000000..7312317 --- /dev/null +++ b/content/posts/postgres-aggregates-correction/merged-ctes-leaf-filter.png diff --git a/content/posts/postgres-aggregates-correction/merged-ctes-limits.png b/content/posts/postgres-aggregates-correction/merged-ctes-limits.png Binary files differnew file mode 100644 index 0000000..09889f3 --- /dev/null +++ b/content/posts/postgres-aggregates-correction/merged-ctes-limits.png diff --git a/content/posts/postgres-aggregates-correction/merged-ctes-root-filter.png b/content/posts/postgres-aggregates-correction/merged-ctes-root-filter.png Binary files differnew file mode 100644 index 0000000..f914ac0 --- /dev/null +++ b/content/posts/postgres-aggregates-correction/merged-ctes-root-filter.png diff --git a/content/posts/postgres-aggregates-correction/merged-ctes.png b/content/posts/postgres-aggregates-correction/merged-ctes.png Binary files differnew file mode 100644 index 0000000..225b263 --- /dev/null +++ b/content/posts/postgres-aggregates-correction/merged-ctes.png 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 Binary files differnew file mode 100644 index 0000000..9b3df9a --- /dev/null +++ b/content/posts/postgres-aggregates-correction/subqueries-fixed-limits.png diff --git a/content/posts/postgres-aggregates-correction/subqueries-fixed.png b/content/posts/postgres-aggregates-correction/subqueries-fixed.png Binary files differnew file mode 100644 index 0000000..cc9ec37 --- /dev/null +++ b/content/posts/postgres-aggregates-correction/subqueries-fixed.png diff --git a/content/posts/postgres-aggregates-correction/subqueries-initial-limits.png b/content/posts/postgres-aggregates-correction/subqueries-initial-limits.png Binary files differnew file mode 100644 index 0000000..9e1b761 --- /dev/null +++ b/content/posts/postgres-aggregates-correction/subqueries-initial-limits.png diff --git a/content/posts/postgres-aggregates-correction/subqueries-initial.png b/content/posts/postgres-aggregates-correction/subqueries-initial.png Binary files differnew file mode 100644 index 0000000..6b33ff6 --- /dev/null +++ b/content/posts/postgres-aggregates-correction/subqueries-initial.png diff --git a/content/posts/postgres-aggregates-correction/subqueries-leaf-filter.png b/content/posts/postgres-aggregates-correction/subqueries-leaf-filter.png Binary files differnew file mode 100644 index 0000000..feb5cba --- /dev/null +++ b/content/posts/postgres-aggregates-correction/subqueries-leaf-filter.png diff --git a/content/posts/postgres-aggregates-correction/subqueries-root-filter.png b/content/posts/postgres-aggregates-correction/subqueries-root-filter.png Binary files differnew file mode 100644 index 0000000..8710b47 --- /dev/null +++ b/content/posts/postgres-aggregates-correction/subqueries-root-filter.png |
