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 →
  1. Add support for partial TOAST decompression

  2. Remove remaining hard-wired OID references in the initial catalog data.

  3. Rephrase references to "time qualification".

Attachments

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.