Re: Speed up lpad() and rpad() for one-byte padding strings

On Thu, Sep 24, 2026 at 1:21 AM John Naylor <johncnaylorls(at)gmail(dot)com> wrote:
>
> On Wed, Sep 23, 2026 at 9:10 PM Sehrope Sarkuni <sehrope(at)jackdb(dot)com> wrote:
> > Timings on an AMD Ryzen 7 5700G, release build (-O3, no asserts),
> > pgbench -c 1 with one statement per transaction, alternating
> > before/after rounds, median latency in ms of 5 rounds (3 at 100M):
> >
> > SELECT octet_length(rpad('x', N, ' '))
> > or
> > SELECT octet_length(lpad('x', N, '0'))
> >
> > N rpad before after lpad before after
> > 1 0.056 0.056 0.057 0.056
> > 10 0.056 0.056 0.057 0.056
> > 100 0.056 0.056 0.057 0.057
>
> 100 is already far beyond the usual N values I've seen in real life,
> and there is no measured difference here, so I don't see what problem
> this is solving.

That's probably true for the usual formatting uses.

But large N does show up for test data. We already have this in
src/test/regress/sql/join_hash.sql:

--- Make a relation with several enormous tuples.
create table wide as select generate_series(1, 3) as id, rpad('',
320000, 'x') as t;

There are similar uses in benchmarks and reproducers in the wild too.

So I don't think large N is particularly artificial. We also have lots
of places in the tree doing essentially the same thing with
'foo' || repeat('bar', large_N) to generate test data.

Regards,
-- Sehrope Sarkuni
Founder & CEO | JackDB, Inc. | https://www.jackdb.com/