Small improvements to substring()

Started by Thomas Munro9 months ago6 messageshackers
Jump to latest
#1Thomas Munro
thomas.munro@gmail.com

Hi,

As recently discussed on pgsql-bugs[1]/messages/by-id/19406-9867fddddd724fca@postgresql.org, substring() does unnecessary
work that can easily be deleted. Here's a patch to try that.

[1]: /messages/by-id/19406-9867fddddd724fca@postgresql.org

Attachments:

t53311_1
v1-0001-Improve-substring-implementation.patchtext/x-patch; charset=UTF-8; name=v1-0001-Improve-substring-implementation.patchDownload+39-125
#2Junwang Zhao
zhjwpku@gmail.com
In reply to: Thomas Munro (#1)
Re: Small improvements to substring()

Hi Thomas,

On Sun, Feb 15, 2026 at 5:41 AM Thomas Munro <thomas.munro@gmail.com> wrote:

Hi,

As recently discussed on pgsql-bugs[1], substring() does unnecessary
work that can easily be deleted. Here's a patch to try that.

[1] /messages/by-id/19406-9867fddddd724fca@postgresql.org

A simple benchmark indicates an approximately 20% performance improvement.
I have not identified any corner cases where the patch would introduce
regressions, so +1.

I test it on an old intel chip mac, test query used:

EXPLAIN ANALYZE
SELECT substring(repeat('中', i * 2), 1, i)
FROM generate_series(1, 10000) AS i;

--
Regards
Junwang Zhao

#3Chao Li
li.evan.chao@gmail.com
In reply to: Thomas Munro (#1)
Re: Small improvements to substring()

On Feb 15, 2026, at 05:45, Thomas Munro <thomas.munro@gmail.com> wrote:

Hi,

As recently discussed on pgsql-bugs[1], substring() does unnecessary
work that can easily be deleted. Here's a patch to try that.

[1] /messages/by-id/19406-9867fddddd724fca@postgresql.org
<v1-0001-Improve-substring-implementation.patch>

Thanks for the patch. This is a great cleanup. The single-pass logic is more efficient, the special cases for empty strings have been removed, and the code is easier to follow now. After reviewing and playing with it, I don’t get any comment to raise.

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/

#4Thomas Munro
thomas.munro@gmail.com
In reply to: Chao Li (#3)
Re: Small improvements to substring()

Thanks for looking, Chao and Junwang. It's a straightforward win, but
I'll wait a week or two for the dust to settle on the recent bugfix
and for any other reviews or objections. That'll give master a bit of
extra time with code matching the release branches, for the unlikely
case that developers working on master discover something interesting
about that.

#5Zsolt Parragi
zsolt.parragi@percona.com
In reply to: Thomas Munro (#4)
Re: Small improvements to substring()

Hello!

The patch looks good, but I think there's an edge case where it causes
a performance regression with a negative start and long strings,
because of the removal of this condition:

- /*
- * Ending at position 1, exclusive, obviously yields an empty
- * string. A zero or negative value can happen if the start was
- * negative or one. SQL99 says to return a zero-length string.
- */
- if (E <= 1)
- return cstring_to_text("");
-

See the following example:

CREATE TEMP TABLE toast_large (c text);
INSERT INTO toast_large VALUES (repeat(U&'\2026', 1000000));

\timing on
SELECT length(substring(c, -5, 3)) FROM toast_large;
\timing off

Without the patch it's 0.4ms
With the patch this executes in 2ms
With the patch and the condition added back it's 0.4ms again

(all release build times on my pc)

#6Thomas Munro
thomas.munro@gmail.com
In reply to: Zsolt Parragi (#5)
Re: Small improvements to substring()

On Tue, Feb 17, 2026 at 10:05 AM Zsolt Parragi
<zsolt.parragi@percona.com> wrote:

The patch looks good, but I think there's an edge case where it causes
a performance regression with a negative start and long strings,
because of the removal of this condition:

- /*
- * Ending at position 1, exclusive, obviously yields an empty
- * string. A zero or negative value can happen if the start was
- * negative or one. SQL99 says to return a zero-length string.
- */
- if (E <= 1)
- return cstring_to_text("");
-

Thanks Zsolt. Huh. Is it wasting time actually detoasting when it
doesn't need to? I had peeked perhaps too briefly at that and figured
it would skip the work for slicelength == 0 (see comment "/* Can
save a lot of work at this point! */")... Maybe we need that check
after all, but the duplication is annoying. Perhaps we should figure
out how to hoist all the length computations up to the top so they're
not duplicated in the eml == 1 and eml > 1 branches. Previously I'd
written this as a proposal for the bugfix and was deliberately
touching only the eml > 1 branch, but I guess we can aim a bit higher
now and try to improve that aspect of the function too...