Re: texteq/byteaeq: avoid detoast [REVIEW]

(2011/01/17 14:51), Itagaki Takahiro wrote:
> On Mon, Jan 17, 2011 at 04:05, Andy Colson<andy(at)squeakycode(dot)net> wrote:
>> This is a review of:
>> https://commitfest.postgresql.org/action/patch_view?id=468
>>
>> Purpose:
>> ========
>> Equal and not-equal _may_ be quickly determined if their lengths are
>> different. This _may_ be a huge speed up if we don't have to detoast.
>
> We can skip detoast to compare lengths of two text/bytea values
> with the patch, but we still need detoast to compare the contents
> of the values.
>
> If we always generate same toasted byte sequences from the same raw
> values, we don't need to detoast at all to compare the contents.
> Is it possible or not?
>
Are you talking about an idea to apply toast id as an alternative key?

I did similar idea to represent security label on user tables for row
level security in the v8.4/9.0 based implementation. Because a small
number of security labels are shared by massive tuples, it is waste of
space if we have all the text data being toasted individually, not only
performance loss in toast/detoast.

In this case, I represented security label (text) using security-id (oid)
which is a primary key pointing out a certain text data in catalog table.
It well reduced storage consumption and achieved good performance in
comparison operation.

One challenge was to reclaim orphan texts. In this case, we needed to
lock out a user table referencing the toast values, then we delete all
the orphan entries.

Thanks,
--
KaiGai Kohei <kaigai(at)ak(dot)jp(dot)nec(dot)com>