Re: Let an ordering index scan hand its ORDER BY value to the target list

Hello again,

I spent some time running this code on metal instances in EC2 to see how
it performs. The microbenchmarks so far time single queries. I wanted
to see what the series does to a whole workload, including workloads
where it has nothing to do, so I ran HammerDB against v8. As I hinted
at earlier, the server was an EC2 i7i.metal-24xl (96 vCPU, 755 GB, local
NVMe, shared_buffers=192GB on huge pages), with three c8i.4xlarge
driving traffic to it.

To see the impact of different additions I've proposed in this thread I
tested three different builds:
base (a14041d437f)
0006 (v8 0001-0006), and
0007 (v8 0001-0007).

I randomized the run order within each block. I created weighted mixes
by taking TPROC-H at SF 30 and adding two plpgsql functions declared COST
5000, plus four indexes on them. Each of 96 VUs picks a query shape the
series improves with probability w, or a neutral one on the same tables
otherwise.

Three blocks of 180 s:

base q/s 0006 0007
w = 0.8 893 +7.9% [+7.4, +8.4] +196% [+191, +201]
w = 0.2 2140 +4.3% [+3.0, +5.6] +70% [+68, +72]
w = 0 4118 +0.4% [-0.4, +1.3] +1.5% [+0.7, +2.2]

The loop is closed, so the q/s gain tracks how slow the positive queries
are on base and won't carry over to another mix.

Per query on 0007, at w = 0.2 (w = 0.8 is within two points):

kNN on fgeo(), distance returned -64% (0001)
the same below a join -53% (0003)
fnorm() from an IOS stock doesn't choose -55% (0004, 0005)
fnorm() on the nullable side of LEFT JOIN -85% (0007; 0006 -1%)

FWIW, this patch series has nothing to add in cases where stock already
picks an index-only scan that returns the expression.

At w = 0 the neutral queries are within 3%. At w = 0.8 one of them is
7-10% slower on both builds and another 10% slower on 0007. Run alone
they take the same time in all three builds (5.56, 5.58 and 5.51 ms;
0.049, 0.047 and 0.047 ms). At w = 0.8, 0007 runs three times as many
queries alongside them.

Backend memory summed over the 96 backends is 417-447 MB at w = 0 in
every build. At w > 0 it is 12-17% lower on the patched builds, sampled
at the end of each run.

No expression indexes, TPROC-C at 1000 warehouses. With
log_nested_statements, auto_explain logged 21 distinct plans inside the
transaction functions. They are identical in all three builds.

48 VUs, 3 blocks 0006 +1.6% [-4.5, +8.1]
0007 -0.4% [-9.5, +9.7]
192 VUs, 10 blocks 0006 -4.9% [-11.4, +2.2]
0007 -3.7% [-10.4, +3.4]

At 192 VUs, 8 of the 30 runs hit a storm of waits on the relation
extension lock (about 300 to 2300 logged waits each), and those runs
lost up to 27% NOPM. base had 1 of 10 and 0006 and 0007 had 4 and 3. A
one-sided Fisher test gives p = 0.15 for that split. Over the pairs
where neither run had a storm, 0006 is -1.2% [-5.1, +2.9] (6 pairs) and
0007 is -0.2% [-2.6, +2.2] (7). The plans are the same, and the
executor changes touch only index scans with ORDER BY operators, which
TPROC-C doesn't use, so I can't see how the series would cause the
storms. Thirty runs don't rule it out either. Backend memory summed
over the 195 backends was 1238 (stock), 1238 (0006) and 1242MB (0007) so
only ~4MB overhead.

TPROC-H at SF 30, DOP 8. All 22 plans are identical.

base 0006 0007
power, 5 runs 4.35 s +0.1% -0.6% (CI within +/-5%)
throughput, 6 streams 4.70 s +0.2% +0.0% (2 runs, +/-7%)
planning, 22 queries 18.47 ms +1.2% +0.8%
planner memory 6609 kB +0.2% +0.2%

Q5 and Q10 jump between run times with the same plan in every build (Q5
takes 2.2 or 2.75 s), so their medians move by up to 22% depending on
which times a build drew. The other queries are within 4%, except Q6 on
0006 (+6.5%) and Q15 (-5% and -7%).

I did find one real cost. One of the positive templates is an inner
hash join over an index-only scan that stock already picks, returning
fnorm(s_phone). The plan shape doesn't change, but with the series that
query is 2-8% slower in the mixes and 8% slower alone (30.6 -> 33.2 ms
over 300k rows). 0006 made use_physical_tlist() refuse any path that
emits extra values, so the index-only scan now projects every row: in
one EXPLAIN ANALYZE the node goes from 15.3 ms to 17.9-19.2 ms. base
returns the index's physical tlist, which holds the fnorm() column
already. Keeping the physical tlist when every value the path emits is
in its indextlist should fix it. I haven't tried that yet.

The scripts, raw results and plans are in
cf7392-bench-hammerdb.tar.gz.nocfbot. Its README lists the runs I
dropped and why. To me this seems like something that started as
a simple idea, but then grew into something that has value for a wide
set of query shapes with minimal overhead.

thanks for looking.

best.

-greg