Currently, PG_DETOAST_DATUM_SLICE when run on a compressed TOAST entry will first decompress the whole object, then extract the relevant slice.
When the desired slice is at or near the front of the object, this is obviously non-optimal.
The attached patch adds in a code path to do a partial decompression of the TOAST entry, when the requested slice is at the start of the object.
For an example of the improvement possible, this trivial example:
create table slicingtest (
id serial primary key,
a text
);
insert into slicingtest (a) select repeat('xyz123', 10000) as a from generate_series(1,10000);
\timing
select sum(length(substr(a, 0, 20))) from slicingtest;
On master, in the current state on my wee laptop, I get
Time: 1426.737 ms (00:01.427)
With the patch, on my wee laptop, I get
Time: 46.886 ms
As usual, doing less work is faster.
Interesting note to motivate a follow-on patch: the substr() function does attempt to slice, but the left() function does not. So, if this patch is accepted, next patch will be to left() to add slicing behaviour.
If nobody lights me on fire, I'll submit to commitfest shortly.
P.