Hi, On Mon, Oct 13, 2025 at 5:42 PM Nazir Bilal Yavuz <[email protected]> wrote: > > Hi, > > On Mon, 13 Oct 2025 at 11:42, Xuneng Zhou <[email protected]> wrote: > > > > Here is patch v3. The comments have been added, and the extra scope > > ({}) has been removed as suggested. > > Thanks, the code looks good to me! > > The benchmark results are nice. I have a quick question about the > setup, if you are benchmarking with the default io_method (worker), > then the results might show the combined benefit of async I/O and > streaming I/O, rather than just streaming I/O alone. If that is the > case, I would suggest also running a benchmark with io_method=sync to > isolate the performance impact of streaming I/O. You might get > interesting results.
The previous benchmark was conducted using the default io_method=worker. The following results are from a new benchmark run with io_method=sync. The performance gains from streaming reads are still present, though smaller than those observed when asynchronous I/O and streaming I/O are combined — which is expected. Thanks for the suggestion! Fragmented Indexes (Cold Cache) Index Type Size Baseline Patched Speedup Primary Key Medium 43.80 ms 38.61 ms 1.13× Primary Key Large 238.24 ms 202.47 ms 1.17× Primary Key XLarge 962.90 ms 793.57 ms 1.21× Timestamp Medium 33.34 ms 29.98 ms 1.11× Timestamp Large 190.41 ms 161.34 ms 1.18× Timestamp XLarge 794.52 ms 647.82 ms 1.22× Float (score) Medium 14.46 ms 13.51 ms 1.06× Float (score) Large 87.38 ms 77.22 ms 1.13× Float (score) XLarge 278.47 ms 233.22 ms 1.19× Composite (3 col) Medium 44.49 ms 40.10 ms 1.10× Composite (3 col) Large 244.86 ms 211.01 ms 1.16× Composite (3 col) XLarge 1073.32 ms 872.42 ms 1.23× ________________________________ Fragmented Indexes (Warm Cache) Index Type Size Baseline Patched Speedup Primary Key Medium 7.91 ms 7.88 ms 1.00× Primary Key Large 35.58 ms 36.41 ms 0.97× Primary Key XLarge 126.29 ms 126.95 ms 0.99× Timestamp Medium 5.14 ms 6.82 ms 0.75× Timestamp Large 22.96 ms 29.55 ms 0.77× Timestamp XLarge 104.26 ms 106.01 ms 0.98× Float (score) Medium 3.76 ms 4.18 ms 0.90× Float (score) Large 13.65 ms 13.01 ms 1.04× Float (score) XLarge 40.58 ms 41.28 ms 0.98× Composite (3 col) Medium 8.23 ms 8.25 ms 0.99× Composite (3 col) Large 37.10 ms 37.59 ms 0.98× Composite (3 col) XLarge 139.89 ms 138.21 ms 1.01× Best, Xuneng
{\rtf1\ansi\ansicpg1252\cocoartf2822
\cocoatextscaling0\cocoaplatform0{\fonttbl\f0\fnil\fcharset0 HelveticaNeue;}
{\colortbl;\red255\green255\blue255;}
{\*\expandedcolortbl;;}
\paperw11900\paperh16840\margl1440\margr1440\vieww24480\viewh13660\viewkind0
\deftab560
\pard\pardeftab560\slleading20\partightenfactor0
\f0\fs26 \cf0 \
=== MEDIUM PRIMARY KEY (COLD CACHE) ===\
BASELINE:\
median: 43.80 ms, mean: 43.60 ms \'b1 0.36 (RSD: 0.8%), min: 43.05 ms, p95: 43.80 ms, n=5\
buffers: hit=12, read=3788, written=0\
\
PATCHED:\
median: 38.61 ms, mean: 38.66 ms \'b1 0.37 (RSD: 1.0%), min: 38.09 ms, p95: 38.98 ms, n=5\
buffers: hit=12, read=3755, written=0\
\
SPEEDUP (median): 1.13x (10.0% faster)\
SPEEDUP (mean): 1.12x\
\
=== MEDIUM PRIMARY KEY (WARM CACHE) ===\
BASELINE:\
median: 7.91 ms, mean: 7.35 ms \'b1 1.13 (RSD: 15.4%), min: 5.96 ms, p95: 8.17 ms, n=5\
buffers: hit=3805, read=0, written=0\
\
PATCHED:\
median: 7.88 ms, mean: 7.81 ms \'b1 0.28 (RSD: 3.6%), min: 7.38 ms, p95: 7.88 ms, n=5\
buffers: hit=3772, read=0, written=0\
\
SPEEDUP (median): 1.00x (0.0% slower)\
SPEEDUP (mean): 0.94x\
\
=== MEDIUM TIMESTAMP INDEX (COLD CACHE) ===\
BASELINE:\
median: 33.34 ms, mean: 33.30 ms \'b1 0.19 (RSD: 0.6%), min: 33.03 ms, p95: 33.36 ms, n=5\
buffers: hit=17, read=2750, written=0\
\
PATCHED:\
median: 29.98 ms, mean: 29.65 ms \'b1 0.55 (RSD: 1.9%), min: 28.67 ms, p95: 30.06 ms, n=5\
buffers: hit=17, read=2753, written=0\
\
SPEEDUP (median): 1.11x (10.0% faster)\
SPEEDUP (mean): 1.12x\
\
=== MEDIUM TIMESTAMP INDEX (WARM CACHE) ===\
BASELINE:\
median: 5.14 ms, mean: 5.81 ms \'b1 1.05 (RSD: 18.1%), min: 4.95 ms, p95: 6.36 ms, n=5\
buffers: hit=2767, read=0, written=0\
\
PATCHED:\
median: 6.82 ms, mean: 6.22 ms \'b1 1.70 (RSD: 27.3%), min: 2.88 ms, p95: 7.08 ms, n=5\
buffers: hit=2770, read=0, written=0\
\
SPEEDUP (median): 0.75x (30.0% slower)\
SPEEDUP (mean): 0.93x\
\
=== MEDIUM SCORE INDEX (COLD CACHE) ===\
BASELINE:\
median: 14.46 ms, mean: 14.46 ms \'b1 0.11 (RSD: 0.8%), min: 14.26 ms, p95: 14.52 ms, n=5\
buffers: hit=12, read=894, written=0\
\
PATCHED:\
median: 13.51 ms, mean: 13.50 ms \'b1 0.07 (RSD: 0.5%), min: 13.41 ms, p95: 13.53 ms, n=5\
buffers: hit=12, read=895, written=0\
\
SPEEDUP (median): 1.06x (0.0% slower)\
SPEEDUP (mean): 1.07x\
\
=== MEDIUM SCORE INDEX (WARM CACHE) ===\
BASELINE:\
median: 3.76 ms, mean: 3.64 ms \'b1 0.47 (RSD: 13.0%), min: 3.03 ms, p95: 4.08 ms, n=5\
buffers: hit=911, read=0, written=0\
\
PATCHED:\
median: 4.18 ms, mean: 4.05 ms \'b1 0.47 (RSD: 11.7%), min: 3.14 ms, p95: 4.31 ms, n=5\
buffers: hit=912, read=0, written=0\
\
SPEEDUP (median): 0.90x (10.0% slower)\
SPEEDUP (mean): 0.89x\
\
=== MEDIUM COMPOSITE INDEX (COLD CACHE) ===\
BASELINE:\
median: 44.49 ms, mean: 44.34 ms \'b1 0.38 (RSD: 0.9%), min: 43.79 ms, p95: 44.57 ms, n=5\
buffers: hit=20, read=3858, written=0\
\
PATCHED:\
median: 40.10 ms, mean: 40.13 ms \'b1 0.08 (RSD: 0.2%), min: 40.00 ms, p95: 40.20 ms, n=5\
buffers: hit=20, read=3861, written=0\
\
SPEEDUP (median): 1.10x (0.0% slower)\
SPEEDUP (mean): 1.10x\
\
=== MEDIUM COMPOSITE INDEX (WARM CACHE) ===\
BASELINE:\
median: 8.23 ms, mean: 7.62 ms \'b1 1.13 (RSD: 14.8%), min: 6.21 ms, p95: 8.44 ms, n=5\
buffers: hit=3883, read=0, written=0\
\
PATCHED:\
median: 8.25 ms, mean: 7.98 ms \'b1 0.80 (RSD: 10.0%), min: 6.46 ms, p95: 8.44 ms, n=5\
buffers: hit=3886, read=0, written=0\
\
SPEEDUP (median): 0.99x (0.0% slower)\
SPEEDUP (mean): 0.95x\
\
=== LARGE PRIMARY KEY (COLD CACHE) ===\
BASELINE:\
median: 238.24 ms, mean: 237.88 ms \'b1 2.66 (RSD: 1.1%), min: 233.37 ms, p95: 239.41 ms, n=5\
buffers: hit=12, read=36521, written=0\
\
PATCHED:\
median: 202.47 ms, mean: 203.77 ms \'b1 3.13 (RSD: 1.5%), min: 200.77 ms, p95: 203.35 ms, n=5\
buffers: hit=12, read=36051, written=0\
\
SPEEDUP (median): 1.17x (10.0% faster)\
SPEEDUP (mean): 1.16x\
\
=== LARGE PRIMARY KEY (WARM CACHE) ===\
BASELINE:\
median: 35.58 ms, mean: 35.63 ms \'b1 1.93 (RSD: 5.4%), min: 32.84 ms, p95: 36.24 ms, n=5\
buffers: hit=36538, read=0, written=0\
\
PATCHED:\
median: 36.41 ms, mean: 33.76 ms \'b1 5.94 (RSD: 17.6%), min: 21.92 ms, p95: 36.71 ms, n=5\
buffers: hit=36068, read=0, written=0\
\
SPEEDUP (median): 0.97x (0.0% slower)\
SPEEDUP (mean): 1.05x\
\
=== LARGE TIMESTAMP INDEX (COLD CACHE) ===\
BASELINE:\
median: 190.41 ms, mean: 190.55 ms \'b1 3.56 (RSD: 1.9%), min: 186.49 ms, p95: 191.41 ms, n=5\
buffers: hit=17, read=27427, written=0\
\
PATCHED:\
median: 161.34 ms, mean: 161.85 ms \'b1 1.29 (RSD: 0.8%), min: 160.62 ms, p95: 161.74 ms, n=5\
buffers: hit=17, read=27430, written=0\
\
SPEEDUP (median): 1.18x (10.0% faster)\
SPEEDUP (mean): 1.17x\
\
=== LARGE TIMESTAMP INDEX (WARM CACHE) ===\
BASELINE:\
median: 22.96 ms, mean: 23.19 ms \'b1 6.20 (RSD: 26.7%), min: 16.17 ms, p95: 29.52 ms, n=5\
buffers: hit=27444, read=0, written=0\
\
PATCHED:\
median: 29.55 ms, mean: 24.70 ms \'b1 6.80 (RSD: 27.5%), min: 16.24 ms, p95: 29.94 ms, n=5\
buffers: hit=27447, read=0, written=0\
\
SPEEDUP (median): 0.77x (20.0% slower)\
SPEEDUP (mean): 0.93x\
\
=== LARGE SCORE INDEX (COLD CACHE) ===\
BASELINE:\
median: 87.38 ms, mean: 87.14 ms \'b1 0.76 (RSD: 0.9%), min: 86.23 ms, p95: 87.60 ms, n=5\
buffers: hit=12, read=8522, written=0\
\
PATCHED:\
median: 77.22 ms, mean: 77.90 ms \'b1 0.98 (RSD: 1.3%), min: 77.00 ms, p95: 79.09 ms, n=5\
buffers: hit=12, read=8525, written=0\
\
SPEEDUP (median): 1.13x (10.0% faster)\
SPEEDUP (mean): 1.11x\
\
=== LARGE SCORE INDEX (WARM CACHE) ===\
BASELINE:\
median: 13.65 ms, mean: 13.28 ms \'b1 1.40 (RSD: 10.6%), min: 10.56 ms, p95: 14.25 ms, n=5\
buffers: hit=8539, read=0, written=0\
\
PATCHED:\
median: 13.01 ms, mean: 12.56 ms \'b1 1.47 (RSD: 11.7%), min: 10.02 ms, p95: 13.30 ms, n=5\
buffers: hit=8542, read=0, written=0\
\
SPEEDUP (median): 1.04x (0.0% slower)\
SPEEDUP (mean): 1.05x\
\
=== LARGE COMPOSITE INDEX (COLD CACHE) ===\
BASELINE:\
median: 244.86 ms, mean: 246.50 ms \'b1 2.75 (RSD: 1.1%), min: 243.80 ms, p95: 247.82 ms, n=5\
buffers: hit=20, read=38511, written=0\
\
PATCHED:\
median: 211.01 ms, mean: 210.71 ms \'b1 1.25 (RSD: 0.6%), min: 208.94 ms, p95: 211.42 ms, n=5\
buffers: hit=20, read=38514, written=0\
\
SPEEDUP (median): 1.16x (10.0% faster)\
SPEEDUP (mean): 1.16x\
\
=== LARGE COMPOSITE INDEX (WARM CACHE) ===\
BASELINE:\
median: 37.10 ms, mean: 37.04 ms \'b1 1.08 (RSD: 2.9%), min: 35.77 ms, p95: 38.02 ms, n=5\
buffers: hit=38536, read=0, written=0\
\
PATCHED:\
median: 37.59 ms, mean: 35.08 ms \'b1 5.16 (RSD: 14.7%), min: 24.79 ms, p95: 37.70 ms, n=5\
buffers: hit=38539, read=0, written=0\
\
SPEEDUP (median): 0.98x (0.0% slower)\
SPEEDUP (mean): 1.05x\
\pard\pardeftab560\slleading20\pardirnatural\partightenfactor0
\cf0 \
\
\pard\pardeftab560\slleading20\partightenfactor0
\cf0 === XLARGE PRIMARY KEY (COLD CACHE) ===\
BASELINE:\
median: 962.90 ms, mean: 962.90 ms \'b1 5.06 (RSD: 0.5%), min: 957.84 ms, p95: 957.84 ms, n=2\
buffers: hit=12, read=171146, written=0\
\
PATCHED:\
median: 793.57 ms, mean: 793.57 ms \'b1 0.07 (RSD: 0.0%), min: 793.51 ms, p95: 793.51 ms, n=2\
buffers: hit=12, read=172056, written=0\
\
SPEEDUP (median): 1.21x (10.0% faster)\
SPEEDUP (mean): 1.21x\
\
=== XLARGE PRIMARY KEY (WARM CACHE) ===\
BASELINE:\
median: 126.29 ms, mean: 126.29 ms \'b1 1.47 (RSD: 1.2%), min: 124.82 ms, p95: 124.82 ms, n=2\
buffers: hit=171163, read=0, written=0\
\
PATCHED:\
median: 126.95 ms, mean: 126.95 ms \'b1 1.03 (RSD: 0.8%), min: 125.92 ms, p95: 125.92 ms, n=2\
buffers: hit=172073, read=0, written=0\
\
SPEEDUP (median): 0.99x (0.0% slower)\
SPEEDUP (mean): 0.99x\
\
=== XLARGE TIMESTAMP INDEX (COLD CACHE) ===\
BASELINE:\
median: 794.52 ms, mean: 794.52 ms \'b1 0.51 (RSD: 0.1%), min: 794.00 ms, p95: 794.00 ms, n=2\
buffers: hit=17, read=137104, written=0\
\
PATCHED:\
median: 647.82 ms, mean: 647.82 ms \'b1 4.03 (RSD: 0.6%), min: 643.79 ms, p95: 643.79 ms, n=2\
buffers: hit=17, read=137107, written=0\
\
SPEEDUP (median): 1.22x (10.0% faster)\
SPEEDUP (mean): 1.22x\
\
=== XLARGE TIMESTAMP INDEX (WARM CACHE) ===\
BASELINE:\
median: 104.26 ms, mean: 104.26 ms \'b1 1.03 (RSD: 1.0%), min: 103.23 ms, p95: 103.23 ms, n=2\
buffers: hit=137121, read=0, written=0\
\
PATCHED:\
median: 106.01 ms, mean: 106.01 ms \'b1 2.02 (RSD: 1.9%), min: 103.99 ms, p95: 103.99 ms, n=2\
buffers: hit=137124, read=0, written=0\
\
SPEEDUP (median): 0.98x (0.0% slower)\
SPEEDUP (mean): 0.98x\
\
=== XLARGE SCORE INDEX (COLD CACHE) ===\
BASELINE:\
median: 278.47 ms, mean: 278.47 ms \'b1 2.20 (RSD: 0.8%), min: 276.27 ms, p95: 276.27 ms, n=2\
buffers: hit=12, read=42361, written=0\
\
PATCHED:\
median: 233.22 ms, mean: 233.22 ms \'b1 1.60 (RSD: 0.7%), min: 231.62 ms, p95: 231.62 ms, n=2\
buffers: hit=12, read=42363, written=0\
\
SPEEDUP (median): 1.19x (10.0% faster)\
SPEEDUP (mean): 1.19x\
\
=== XLARGE SCORE INDEX (WARM CACHE) ===\
BASELINE:\
median: 40.58 ms, mean: 40.58 ms \'b1 0.79 (RSD: 2.0%), min: 39.79 ms, p95: 39.79 ms, n=2\
buffers: hit=42378, read=0, written=0\
\
PATCHED:\
median: 41.28 ms, mean: 41.28 ms \'b1 0.66 (RSD: 1.6%), min: 40.62 ms, p95: 40.62 ms, n=2\
buffers: hit=42380, read=0, written=0\
\
SPEEDUP (median): 0.98x (0.0% slower)\
SPEEDUP (mean): 0.98x\
\
=== XLARGE COMPOSITE INDEX (COLD CACHE) ===\
BASELINE:\
median: 1073.32 ms, mean: 1073.32 ms \'b1 1.08 (RSD: 0.1%), min: 1072.24 ms, p95: 1072.24 ms, n=2\
buffers: hit=20, read=192527, written=0\
\
PATCHED:\
median: 872.42 ms, mean: 872.42 ms \'b1 2.07 (RSD: 0.2%), min: 870.35 ms, p95: 870.35 ms, n=2\
buffers: hit=20, read=192530, written=0\
\
SPEEDUP (median): 1.23x (10.0% faster)\
SPEEDUP (mean): 1.23x\
\
=== XLARGE COMPOSITE INDEX (WARM CACHE) ===\
BASELINE:\
median: 139.89 ms, mean: 139.89 ms \'b1 0.60 (RSD: 0.4%), min: 139.29 ms, p95: 139.29 ms, n=2\
buffers: hit=192552, read=0, written=0\
\
PATCHED:\
median: 138.21 ms, mean: 138.21 ms \'b1 1.44 (RSD: 1.0%), min: 136.77 ms, p95: 136.77 ms, n=2\
buffers: hit=192555, read=0, written=0\
\
SPEEDUP (median): 1.01x (0.0% slower)\
SPEEDUP (mean): 1.01x}
v4-0001-pgstattuple-Use-streaming-read-API-in-pgstatindex.patch
Description: Binary data
