From 6573a54c50738e455b2b0d4863498b916046a466 Mon Sep 17 00:00:00 2001 From: Martin Kagamino Lehoux Date: Mon, 6 Oct 2025 09:28:43 +0200 Subject: Simpler perf charts --- content/posts/postgres-aggregation-speed/index.md | 124 +--------------------- data/chart_presets.yaml | 17 +++ layouts/shortcodes/chart.html | 17 +++ layouts/shortcodes/perf-chart.html | 89 ++++++++++++++++ 4 files changed, 128 insertions(+), 119 deletions(-) create mode 100644 data/chart_presets.yaml create mode 100644 layouts/shortcodes/perf-chart.html diff --git a/content/posts/postgres-aggregation-speed/index.md b/content/posts/postgres-aggregation-speed/index.md index 3507d4f..36d8fac 100644 --- a/content/posts/postgres-aggregation-speed/index.md +++ b/content/posts/postgres-aggregation-speed/index.md @@ -31,47 +31,13 @@ In this case, there are list aggregation functions to generate sub lists at the After performing this article's investigation, I will implement the learnings to a central query in my day job product: a search query. Because it requires some price filtering in Python, the data loading part from SQL can be quite important. The results below show the time spent in each part of the pipeline. Overall, the changes give about a 5% boost to the query, while reducing maintenance cost. Not bad for a query I spent weeks optimising! -{{< chart type="bar" data=`{ - "labels": ["Old", "New"], - "datasets": [{ - "label": "Wait (ms)", - "data": [1095, 1100], - "backgroundColor": "rgba(16, 185, 129, 0.6)", - "borderColor": "rgba(16, 185, 129, 1)", - "borderWidth": 1, - "barThickness": 8 - }, { - "label": "Psycopg parsing (ms)", - "data": [45, 180], - "backgroundColor": "rgba(245, 158, 11, 0.6)", - "borderColor": "rgba(245, 158, 11, 1)", - "borderWidth": 1, - "barThickness": 8 - }, { - "label": "Custom parsing (ms)", - "data": [350, 140], - "backgroundColor": "rgba(91, 30, 114, 0.6)", - "borderColor": "rgba(91, 30, 114, 1)", - "borderWidth": 1, - "barThickness": 8 - }] -}` options=`{ - "indexAxis": "y", - "scales": { - "x": { "stacked": true, "title": { "display": true, "text": "Time (ms)" }, - "y": { } - } - }, - "plugins": { - "title": { "display": true, "text": "Case study" } - } -}` >}} +{{< perf-chart title="Case study" labels="Old,New" datasets="Wait (ms):emerald:1095,1100|Psycopg parsing (ms):amber:45,180|Custom parsing (ms):purple:350,140" >}} ## Data model and setup I use a simple data model, and I can tweak the size of the dataset so that the benchmark is interesting. -The model is an `Accommodation`, that has a default price for rental, and then many `Override` that would allow to specify different prices along the year. There are 10 000 accommodations and 1 000 000 overrides. +The model is an `Accommodation`, that has a default price for rental, and then many `Override` that would allow to specify different prices along the year. There are 10,000 accommodations and 1,000,000 overrides. ![Data model](data-model.svg) @@ -180,34 +146,7 @@ They look pretty similar. I run these five queries with `EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)`, and I can note their total time, alongside the different nodes duration in the plan. -{{< chart type="bar" data=`{ - "labels": ["composite", "json array", "jsonb array", "json object", "jsonb object"], - "datasets": [{ - "label": "Seq Scan (ms)", - "data": [39, 37, 35, 40, 40], - "backgroundColor": "rgba(16, 185, 129, 0.6)", - "borderColor": "rgba(16, 185, 129, 1)", - "borderWidth": 1, - "barThickness": 8 - }, { - "label": "HashAggregate (ms)", - "data": [40, 86, 112, 120, 180], - "backgroundColor": "rgba(245, 158, 11, 0.6)", - "borderColor": "rgba(245, 158, 11, 1)", - "borderWidth": 1, - "barThickness": 8 - }] -}` options=`{ - "indexAxis": "y", - "scales": { - "x": { "stacked": true, "title": { "display": true, "text": "Time (ms)" }, - "y": { } - } - }, - "plugins": { - "title": { "display": true, "text": "PostgreSQL Query Performance" } - } -}` >}} +{{< perf-chart title="PostgreSQL Query Performance" labels="composite,json array,jsonb array,json object,jsonb object" datasets="Seq Scan (ms):emerald:39,37,35,40,40|HashAggregate (ms):amber:40,86,112,120,180" >}} - `Seq Scan` is stable for all queries, which makes sense because the same data is required. - `HashAggregate` are different because of the choice of data processing @@ -289,33 +228,7 @@ class TestJSONOut: ) ``` -{{< chart type="bar" data=`{ - "labels": ["composite binary", "json array text", "json array binary", "jsonb array binary", "jsonb array text", "composite text"], - "datasets": [{ - "label": "PostgreSQL (ms)", - "data": [80, 120, 120, 150, 150, 80], - "backgroundColor": "rgba(59, 130, 246, 0.6)", - "borderColor": "rgba(59, 130, 246, 1)", - "borderWidth": 1, - "barThickness": 8 - }, { - "label": "Python (ms)", - "data": [140, 110, 110, 110, 120, 230], - "backgroundColor": "rgba(245, 158, 11, 0.6)", - "borderColor": "rgba(245, 158, 11, 1)", - "borderWidth": 1, - "barThickness": 8 - }] -}` options=`{ - "indexAxis": "y", - "scales": { - "x": { "stacked": true, "title": { "display": true, "text": "Time (ms)" } }, - "y": { } - }, - "plugins": { - "title": { "display": true, "text": "Python Processing Performance" } - } -}` >}} +{{< perf-chart title="Python Processing Performance" labels="composite binary,json array text,json array binary,jsonb array binary,jsonb array text,composite text" datasets="PostgreSQL (ms):blue:80,120,120,150,150,80|Python (ms):amber:140,110,110,110,120,230" >}} - JSON parsing seems stable across all runs - Composite binary seems a little slower, but not by much @@ -342,34 +255,7 @@ I have to fetch the data and format it as JSON. I'm not pushing it to the point This time, I use JSON objects, as this is the shape of the data transmitted. It should give a boost to all JSON queries, while composite still requires processing. -{{< chart type="bar" data=`{ - "labels": ["json object binary", "json object text", "jsonb object binary", "composite text", "jsonb object text", "composite binary"], - "datasets": [{ - "label": "PostgreSQL (ms)", - "data": [160, 160, 220, 80, 220, 80], - "backgroundColor": "rgba(59, 130, 246, 0.6)", - "borderColor": "rgba(59, 130, 246, 1)", - "borderWidth": 1, - "barThickness": 8 - }, { - "label": "Python (ms)", - "data": [60, 70, 50, 200, 60, 200], - "backgroundColor": "rgba(245, 158, 11, 0.6)", - "borderColor": "rgba(245, 158, 11, 1)", - "borderWidth": 1, - "barThickness": 8 - }] -}` options=`{ - "indexAxis": "y", - "scales": { - "x": { "stacked": true, "title": { "display": true, "text": "Time (ms)" }, - "y": { } - } - }, - "plugins": { - "title": { "display": true, "text": "JSON API Performance" } - } -}` >}} +{{< perf-chart title="JSON API Performance" labels="json object binary,json object text,jsonb object binary,composite text,jsonb object text,composite binary" datasets="PostgreSQL (ms):blue:160,160,220,80,220,80|Python (ms):amber:60,70,50,200,60,200" >}} - All `json` processing look the same - `composite` in much slower in Python, regardless of binary or text diff --git a/data/chart_presets.yaml b/data/chart_presets.yaml new file mode 100644 index 0000000..27a46ba --- /dev/null +++ b/data/chart_presets.yaml @@ -0,0 +1,17 @@ +# Chart styling defaults +defaults: + borderWidth: 1 + barThickness: 8 + backgroundOpacity: 0.6 + borderOpacity: 1.0 + +# Base colors (RGB values without alpha) +colors: + emerald: "16, 185, 129" + amber: "245, 158, 11" + purple: "147, 51, 234" + blue: "59, 130, 246" + red: "239, 68, 68" + indigo: "99, 102, 241" + pink: "236, 72, 153" + teal: "20, 184, 166" diff --git a/layouts/shortcodes/chart.html b/layouts/shortcodes/chart.html index 69689cd..2198b63 100644 --- a/layouts/shortcodes/chart.html +++ b/layouts/shortcodes/chart.html @@ -1,8 +1,25 @@ {{- $id := printf "chart-%d" (now.UnixNano) -}} {{- $type := .Get "type" | default "bar" -}} +{{- $preset := .Get "preset" -}} +{{- $title := .Get "title" -}} {{- $data := .Get "data" -}} {{- $options := .Get "options" | default "{}" -}} +{{/* Load chart presets */}} +{{- $presets := site.Data.chart_presets -}} + +{{/* Handle preset-based charts */}} +{{- if $preset -}} + {{- $presetData := index $presets.charts $preset -}} + {{- if $presetData -}} + {{- $data = $presetData.data | jsonify -}} + {{- if and $title (not (index $presetData.options.plugins.title "text")) -}} + {{- $presetData = merge $presetData (dict "options" (merge $presetData.options (dict "plugins" (merge $presetData.options.plugins (dict "title" (dict "text" $title)))))) -}} + {{- end -}} + {{- $options = $presetData.options | jsonify -}} + {{- end -}} +{{- end -}} +
diff --git a/layouts/shortcodes/perf-chart.html b/layouts/shortcodes/perf-chart.html new file mode 100644 index 0000000..8c1734a --- /dev/null +++ b/layouts/shortcodes/perf-chart.html @@ -0,0 +1,89 @@ +{{/* Performance chart shortcode for consistent chart styling */}} +{{- $title := .Get "title" -}} +{{- $labelsRaw := .Get "labels" -}} +{{- $datasetsRaw := .Get "datasets" -}} +{{- $xAxisLabel := .Get "xAxisLabel" | default "Time (ms)" -}} + +{{/* Load chart configuration */}} +{{- $config := site.Data.chart_presets -}} +{{- $defaults := $config.defaults -}} +{{- $colors := $config.colors -}} + +{{/* Parse labels - split by comma */}} +{{- $labels := split $labelsRaw "," -}} + +{{/* Parse datasets parameter: "Label:color:values" */}} +{{- $parsedDatasets := slice -}} +{{- if $datasetsRaw -}} + {{- $datasetList := split $datasetsRaw "|" -}} + {{- range $datasetList -}} + {{- $parts := split . ":" -}} + {{- if ge (len $parts) 3 -}} + {{- $label := index $parts 0 -}} + {{- $preset := index $parts 1 -}} + {{- $valuesStr := index $parts 2 -}} + {{- $values := split $valuesStr "," -}} + {{- $colorRgb := index $colors $colorName -}} + {{- if $colorRgb -}} + {{- $backgroundColor := printf "rgba(%s, %.1f)" $colorRgb $defaults.backgroundOpacity -}} + {{- $borderColor := printf "rgba(%s, %.1f)" $colorRgb $defaults.borderOpacity -}} + {{- $dataset := dict + "label" $label + "data" $values + "backgroundColor" $backgroundColor + "borderColor" $borderColor + "borderWidth" $defaults.borderWidth + "barThickness" $defaults.barThickness -}} + {{- $parsedDatasets = $parsedDatasets | append $dataset -}} + {{- end -}} + {{- end -}} + {{- end -}} +{{- end -}} + +{{/* Generate unique ID */}} +{{- $id := printf "chart-%d" (now.UnixNano) -}} + +
+ +
+ + + + + -- cgit v1.3