Re: scale parallel_tuple_cost by tuple width - Mailing list pgsql-hackers

Andrew Dunstan <andrew@dunslane.net> writes:
> I followed your suggested methodology to measure how Gather IPC
> cost actually scales with tuple width.

I ran your test script on two of my development machines and got:

Linux/x86_64:

Width    Parallel(ms)   Serial(ms)      Speedup  Gather rows
-----    ------------   ----------      -------  -----------
8             510.976     1219.453        2.39x       235706
16            532.123     1271.692        2.39x       235603
32            588.826     1356.428        2.30x       242062
64            674.485     1570.758        2.33x       239561
128           817.417     1887.202        2.31x       243158
256          1066.836     2548.100        2.39x       242304
384          1293.900     3038.905        2.35x       243941
512          1515.822     3573.144        2.36x       239064
768          1998.638     4725.448        2.36x       247558
1024         9865.460    22779.795        2.31x     10000000

macOS/M4-Pro:

Width    Parallel(ms)   Serial(ms)      Speedup  Gather rows
-----    ------------   ----------      -------  -----------
8             299.464      769.130        2.57x       242549
16            310.361      787.629        2.54x       243643
32            344.541      839.589        2.44x       242419
64            413.330      967.512        2.34x       238771
128           519.794     1185.757        2.28x       241440
256          1479.766     1823.559        1.23x       238615
384          2022.882     2326.823        1.15x       240617
512          2423.938     2778.995        1.15x       244752
768          3511.425     3934.384        1.12x       235814
1024         9905.073    12214.577        1.23x     10000000

It's not entirely clear to me how you reduced these numbers
to a ptc formula, but we should do that and see how the
results compare to your machine.

> The original patch used PARALLEL_TUPLE_COST_FIXED_FRAC = 0.10,
> which substantially underestimates the width-independent component.
> A higher fixed fraction would dampen the width adjustment, which
> also partly addresses Tom's concern about sensitivity to width
> estimate errors: with ~45% of the cost being fixed, even a 2x
> error in width only translates to a ~1.5x error in total cost.

That does make me feel better, assuming that we come out with
similar results on several machines.

> The script used to get the timings is attached.

If anyone else wants to try this with a platform having non-GNU
grep, you'll need these changes:

$ diff -pud ptc_calibrate.sh~ ptc_calibrate.sh
--- ptc_calibrate.sh~   2026-04-01 10:02:53.000000000 -0400
+++ ptc_calibrate.sh    2026-04-01 13:31:06.873772739 -0400
@@ -32,7 +32,8 @@ psql_cmd() {
 
 psql_cmd_timing() {
     "$PGBIN/psql" -h /tmp -p $PORT -d "$DB" -o /dev/null -qAt \
-        -c '\timing on' "$@" 2>&1 | grep -oP 'Time: \K[\d.]+' | tail -1
+        -c '\timing on' "$@" 2>&1 | \
+       sed -n 's/.*Time: \([0-9.][0-9.]*\).*/\1/p' | tail -1
 }
 
 # Create the database if needed
@@ -114,7 +115,7 @@ for W in $WIDTHS; do
 
     # Get Gather row count from EXPLAIN
     gather_rows=$(psql_cmd -c "$SET_CMDS EXPLAIN (COSTS ON) $Q;" \
-        | grep -oP 'Gather.*rows=\K\d+' | head -1)
+        | sed -n 's/.*Gather.*rows=\([0-9][0-9]*\).*/\1/p' | head -1)
     gather_rows=${gather_rows:-"?"}
 
     # Warm up (2 runs each)


            regards, tom lane