fix(postgres-aggregates): Add info on bench perf

2 files changed, 17 insertions, 21 deletions

diff --git a/content/posts/postgres-aggregates/aggregation.txt b/content/posts/postgres-aggregates/aggregation.txt
index 19fe5a9..d8c8f18 100644
--- a/content/posts/postgres-aggregates/aggregation.txt
+++ b/content/posts/postgres-aggregates/aggregation.txt
@@ -1,36 +1,36 @@
                                                                                QUERY PLAN                                                                                
 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- GroupAggregate  (cost=1.00..280600.32 rows=10000 width=32) (actual time=5.206..2498.463 rows=10000 loops=1)
+ GroupAggregate  (cost=1.00..280612.99 rows=10000 width=32) (actual time=5.465..2682.441 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)
+   Buffers: shared hit=1093885
+   ->  Merge Left Join  (cost=1.00..205512.99 rows=10000000 width=24) (actual time=4.829..1538.277 rows=9998159 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)
+         Buffers: shared hit=1093885
+         ->  Merge Left Join  (cost=0.58..7362.56 rows=100000 width=20) (actual time=4.809..53.801 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)
+               Buffers: shared hit=99720
+               ->  Index Scan using products_pkey on public.products  (cost=0.29..387.29 rows=10000 width=16) (actual time=0.013..1.850 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)
+               ->  Index Scan using reviews_product_id_idx on public.reviews  (cost=0.29..5700.28 rows=100000 width=8) (actual time=0.015..35.760 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)
+                     Buffers: shared hit=99577
+         ->  Materialize  (cost=0.42..50400.42 rows=1000000 width=8) (actual time=0.015..638.811 rows=9998150 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)
+               Buffers: shared hit=994165
+               ->  Index Scan using orders_product_id_idx on public.orders  (cost=0.42..47900.42 rows=1000000 width=8) (actual time=0.013..325.213 rows=1000000 loops=1)
                      Output: orders.id, orders.order_date, orders.customer_name, orders.product_id
-                     Buffers: shared hit=994158
+                     Buffers: shared hit=994165
  Planning:
    Buffers: shared hit=181
- Planning Time: 0.467 ms
+ Planning Time: 0.448 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
+   Timing: Generation 0.370 ms, Inlining 0.000 ms, Optimization 0.199 ms, Emission 4.585 ms, Total 5.154 ms
+ Execution Time: 2696.039 ms
 (32 rows)
 
diff --git a/content/posts/postgres-aggregates/index.md b/content/posts/postgres-aggregates/index.md
index d1664ee..9fb215e 100644
--- a/content/posts/postgres-aggregates/index.md
+++ b/content/posts/postgres-aggregates/index.md
@@ -48,7 +48,7 @@ We will see that there are several ways to write a query that outputs the same r
 
 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.
 
-I will also use **hyperfine**[^hyperfine] to benchmark the different queries. You don't necessarily need it to test the queries at home.
+I will also use **hyperfine**[^hyperfine] to benchmark the different queries. You don't necessarily need it to test the queries at home. Note that there will be a difference between the performances of the benchmark and the `EXPLAIN ANALYZE`, that's expected.
 
 ## 1. Aggregation
 
@@ -107,8 +107,6 @@ Benchmark 1: psql postgres://postgres@localhost:5432/postgres -f aggregation.sql
 
 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, we can see which are the most costly parts of the query:
 
 ![Query plan for the aggregation query on several joins](aggregation.png)
@@ -156,8 +154,6 @@ When we analyze the new query plan, we can see some major differences:
 
 ![Query plan for the subqueries](subqueries.png)
 
-<!-- TODO: Display loops in the plan -->
-
 There are now sub-plans: 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**[^wiki-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.