Compressed TOAST Slicing
Paul Ramsey <pramsey@cleverelephant.ca>
From: Paul Ramsey <pramsey@cleverelephant.ca>
To: pgsql-hackers@lists.postgresql.org
Date: 2018-11-01T20:55:16Z
Lists: pgsql-hackers
Commits
Same data as JSON:
GET /api/v1/messages/:b64id/commits
the thread's linked commits as JSON, with link sources.
API reference →
-
Add support for partial TOAST decompression
- 4d0e994eed83 12.0 landed
-
Remove remaining hard-wired OID references in the initial catalog data.
- 3aa0395d4ed3 12.0 cited
-
Rephrase references to "time qualification".
- ebcc7bf949ba 12.0 cited
Attachments
- compressed-datum-slicing-20190101a.patch (application/octet-stream) patch
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.