diff options
| author | Martin Kagamino Lehoux <martin@lehoux.net> | 2025-09-09 10:37:00 +0200 |
|---|---|---|
| committer | Martin Kagamino Lehoux <martin@lehoux.net> | 2025-10-01 07:51:11 +0200 |
| commit | 415f7e954b021cad720c27ea73cadb3fb400e570 (patch) | |
| tree | 99dd84cc27bcdf8fffb7adaf8d26ccda4ff337fb /content/posts/nested-postgres-results/index.md | |
| parent | 7485cdcfa7d5ffe9bb928dffefd070b13f4de518 (diff) | |
Add comparison of sql pipelines
Diffstat (limited to 'content/posts/nested-postgres-results/index.md')
| -rw-r--r-- | content/posts/nested-postgres-results/index.md | 13 |
1 files changed, 11 insertions, 2 deletions
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 |