Add comparison of sql pipelines

4 files changed, 110 insertions, 35 deletions

diff --git a/content/posts/nested-postgres-results/composite-array-agg.txt b/content/posts/nested-postgres-results/composite-array-agg.txt
new file mode 100644
index 0000000..eb5c507
--- /dev/null
+++ b/content/posts/nested-postgres-results/composite-array-agg.txt
@@ -0,0 +1,33 @@
+GroupAggregate  (cost=14206.02..16920.03 rows=8849 width=36) (actual time=17.442..24.779 rows=10000 loops=1)
+  Output: accommodation_id, array_agg(ROW(date, price))
+  Group Key: overrides.accommodation_id
+  Buffers: shared hit=6442
+  ->  Gather Merge  (cost=14206.02..16702.25 rows=21433 width=13) (actual time=17.433..20.521 rows=20000 loops=1)
+        Output: accommodation_id, date, price
+        Workers Planned: 2
+        Workers Launched: 2
+        Buffers: shared hit=6442
+        ->  Sort  (cost=13206.00..13228.33 rows=8930 width=13) (actual time=14.503..14.704 rows=6667 loops=3)
+              Output: accommodation_id, date, price
+              Sort Key: overrides.accommodation_id
+              Sort Method: quicksort  Memory: 424kB
+              Buffers: shared hit=6442
+              Worker 0:  actual time=13.119..13.313 rows=6171 loops=1
+                Sort Method: quicksort  Memory: 385kB
+                Buffers: shared hit=2007
+              Worker 1:  actual time=13.642..13.847 rows=6437 loops=1
+                Sort Method: quicksort  Memory: 394kB
+                Buffers: shared hit=2086
+              ->  Parallel Seq Scan on public.overrides  (cost=0.00..12619.99 rows=8930 width=13) (actual time=0.011..13.689 rows=6667 loops=3)
+                    Output: accommodation_id, date, price
+                    Filter: ((overrides.date >= '2025-07-01'::date) AND (overrides.date < '2025-07-08'::date))
+                    Rows Removed by Filter: 326666
+                    Buffers: shared hit=6370
+                    Worker 0:  actual time=0.009..12.361 rows=6171 loops=1
+                      Buffers: shared hit=1971
+                    Worker 1:  actual time=0.010..12.859 rows=6437 loops=1
+                      Buffers: shared hit=2050
+Planning:
+  Buffers: shared hit=129
+Planning Time: 0.272 ms
+Execution Time: 25.133 ms
diff --git a/content/posts/nested-postgres-results/index.md b/content/posts/nested-postgres-results/index.md
index e35adbf..3e07017 100644
--- a/content/posts/nested-postgres-results/index.md
+++ b/content/posts/nested-postgres-results/index.md
@@ -13,7 +13,7 @@ I often need to return several sub-rows for each top-level row of a given query
 1. Postgres composite types array offer better raw time performance that json objects array
 2. Postgres composite types array offer better data compression performance that json objects array
 3. Postgres composite types array offer better performance up to the host language
-  - Conversion between JSON output from postgres result to python objects should be slower that using composite types
+    - Conversion between JSON output from postgres result to python objects should be slower that using composite types
 4. Postgres jsonb functions offer better performances than json functions
 
 ## Data model and setup
@@ -23,7 +23,16 @@ As usual, we'll try to solve some real world problem with a certain data model.
 ## Aggregation row results
 
 The first estimates show 48ms for composite, 58ms for json array and 68ms for jsonb array. It kind of make sense, jsonb is finally costlier and only used when processing the data.
-**I should compare explain to see what blocks are impacted**
+
+To better understand the differences in performance, I want to compare the explain plans that should look very similar. They are indeed composed of the same 4 steps, and only the duration of the last step is changed by the kind of aggregation.
+
+| Method          | Parallel Seq Scan | Sort    | Gather Merge | GroupAggregate |
+| --------------- | ----------------- | ------- | ------------ | -------------- |
+| composite array | 13.7 ms           | 1.02 ms | 5.68 ms      | 4.26 ms        |
+| json array      | 13.4 ms           | 1.03 ms | 3.38 ms      | 13.6 ms        |
+| jsonb array     | 13.6 ms           | 1.05 ms | 3.51 ms      | 23.1 ms        |
+
+These results seem in line with my expectations, after updating my ideas on JSONB: this binary format is efficient for storage efficiency and querying the data, but at the cost of encoding and decoding speed. In our case, the output is raw text, so we pay the encoding in JSONB by postgres, and certainly the decoding back by the server or our client.
 
 ## Python gathering
 
diff --git a/content/posts/nested-postgres-results/json-array-agg.txt b/content/posts/nested-postgres-results/json-array-agg.txt
new file mode 100644
index 0000000..63bebe7
--- /dev/null
+++ b/content/posts/nested-postgres-results/json-array-agg.txt
@@ -0,0 +1,33 @@
+GroupAggregate  (cost=14206.02..16973.61 rows=8849 width=36) (actual time=15.958..31.415 rows=10000 loops=1)
+  Output: accommodation_id, json_agg(json_build_object('date', date, 'price', price))
+  Group Key: overrides.accommodation_id
+  Buffers: shared hit=6442
+  ->  Gather Merge  (cost=14206.02..16702.25 rows=21433 width=13) (actual time=15.939..17.800 rows=20000 loops=1)
+        Output: accommodation_id, date, price
+        Workers Planned: 2
+        Workers Launched: 2
+        Buffers: shared hit=6442
+        ->  Sort  (cost=13206.00..13228.33 rows=8930 width=13) (actual time=14.210..14.424 rows=6667 loops=3)
+              Output: accommodation_id, date, price
+              Sort Key: overrides.accommodation_id
+              Sort Method: quicksort  Memory: 423kB
+              Buffers: shared hit=6442
+              Worker 0:  actual time=13.430..13.637 rows=6367 loops=1
+                Sort Method: quicksort  Memory: 391kB
+                Buffers: shared hit=2064
+              Worker 1:  actual time=13.558..13.776 rows=6247 loops=1
+                Sort Method: quicksort  Memory: 388kB
+                Buffers: shared hit=2031
+              ->  Parallel Seq Scan on public.overrides  (cost=0.00..12619.99 rows=8930 width=13) (actual time=0.013..13.396 rows=6667 loops=3)
+                    Output: accommodation_id, date, price
+                    Filter: ((overrides.date >= '2025-07-01'::date) AND (overrides.date < '2025-07-08'::date))
+                    Rows Removed by Filter: 326666
+                    Buffers: shared hit=6370
+                    Worker 0:  actual time=0.008..12.658 rows=6367 loops=1
+                      Buffers: shared hit=2028
+                    Worker 1:  actual time=0.018..12.728 rows=6247 loops=1
+                      Buffers: shared hit=1995
+Planning:
+  Buffers: shared hit=129
+Planning Time: 0.292 ms
+Execution Time: 31.781 ms
diff --git a/content/posts/nested-postgres-results/jsonb-array-agg.txt b/content/posts/nested-postgres-results/jsonb-array-agg.txt
index 35ae6bb..5936490 100644
--- a/content/posts/nested-postgres-results/jsonb-array-agg.txt
+++ b/content/posts/nested-postgres-results/jsonb-array-agg.txt
@@ -1,33 +1,33 @@
-    GroupAggregate  (cost=14173.77..16807.65 rows=8725 width=36) (actual time=15.671..34.981 rows=10000 loops=1)
-    Output: accommodation_id, jsonb_agg(jsonb_build_object('date', date, 'price', price))
-    Group Key: overrides.accommodation_id
-    Buffers: shared hit=6442
-    ->  Gather Merge  (cost=14173.77..16545.84 rows=20367 width=13) (actual time=15.641..17.253 rows=20000 loops=1)
-            Output: accommodation_id, date, price
-            Workers Planned: 2
-            Workers Launched: 2
-            Buffers: shared hit=6442
-            ->  Sort  (cost=13173.74..13194.96 rows=8486 width=13) (actual time=13.876..14.122 rows=6667 loops=3)
-                Output: accommodation_id, date, price
-                Sort Key: overrides.accommodation_id
-                Sort Method: quicksort  Memory: 430kB
-                Buffers: shared hit=6442
-                Worker 0:  actual time=13.239..13.513 rows=6391 loops=1
-                    Sort Method: quicksort  Memory: 392kB
-                    Buffers: shared hit=2067
-                Worker 1:  actual time=13.028..13.281 rows=5997 loops=1
-                    Sort Method: quicksort  Memory: 380kB
-                    Buffers: shared hit=1948
-                ->  Parallel Seq Scan on public.overrides  (cost=0.00..12619.99 rows=8486 width=13) (actual time=0.009..13.186 rows=6667 loops=3)
-                        Output: accommodation_id, date, price
-                        Filter: ((overrides.date >= '2025-07-01'::date) AND (overrides.date < '2025-07-08'::date))
-                        Rows Removed by Filter: 326666
-                        Buffers: shared hit=6370
-                        Worker 0:  actual time=0.010..12.581 rows=6391 loops=1
-                        Buffers: shared hit=2031
-                        Worker 1:  actual time=0.008..12.365 rows=5997 loops=1
-                        Buffers: shared hit=1912
-    Planning:
-    Buffers: shared hit=129
-    Planning Time: 0.251 ms
-    Execution Time: 35.310 ms
+GroupAggregate  (cost=14206.02..16973.61 rows=8849 width=36) (actual time=16.239..41.215 rows=10000 loops=1)
+  Output: accommodation_id, jsonb_agg(jsonb_build_object('date', date, 'price', price))
+  Group Key: overrides.accommodation_id
+  Buffers: shared hit=6442
+  ->  Gather Merge  (cost=14206.02..16702.25 rows=21433 width=13) (actual time=16.215..18.136 rows=20000 loops=1)
+        Output: accommodation_id, date, price
+        Workers Planned: 2
+        Workers Launched: 2
+        Buffers: shared hit=6442
+        ->  Sort  (cost=13206.00..13228.33 rows=8930 width=13) (actual time=14.416..14.629 rows=6667 loops=3)
+              Output: accommodation_id, date, price
+              Sort Key: overrides.accommodation_id
+              Sort Method: quicksort  Memory: 424kB
+              Buffers: shared hit=6442
+              Worker 0:  actual time=13.574..13.780 rows=6267 loops=1
+                Sort Method: quicksort  Memory: 388kB
+                Buffers: shared hit=2037
+              Worker 1:  actual time=13.768..13.981 rows=6339 loops=1
+                Sort Method: quicksort  Memory: 391kB
+                Buffers: shared hit=2050
+              ->  Parallel Seq Scan on public.overrides  (cost=0.00..12619.99 rows=8930 width=13) (actual time=0.024..13.580 rows=6667 loops=3)
+                    Output: accommodation_id, date, price
+                    Filter: ((overrides.date >= '2025-07-01'::date) AND (overrides.date < '2025-07-08'::date))
+                    Rows Removed by Filter: 326666
+                    Buffers: shared hit=6370
+                    Worker 0:  actual time=0.011..12.728 rows=6267 loops=1
+                      Buffers: shared hit=2001
+                    Worker 1:  actual time=0.051..12.968 rows=6339 loops=1
+                      Buffers: shared hit=2014
+Planning:
+  Buffers: shared hit=129
+Planning Time: 0.274 ms
+Execution Time: 41.571 ms