Re: bytea encode performance issues

Sim Zacks <sim(at)compulab(dot)co(dot)il> writes:
> Results below:
>> ... but given that, I wonder whether the cost isn't from fetching
>> the toasted messageblk data, and nothing directly to do with either
>> the encode() call or the ~~ test. It would be interesting to compare
>> the results of

Okay, so subtracting the totals we've got:

2.7 sec to scan the table proper

248.7 sec to fetch the toasted datums (well, this test also includes
an equality comparison, but since the text lengths are generally
going to be different, that really should be negligible)

55.2 sec to do the encode() calls

186.4 sec to do the LIKE comparisons

So getting rid of the encode() would help a bit, but it's hardly the
main source of your problem.

We've seen complaints about toast fetch time before. I don't think
there's any really simple solution. You could experiment with disabling
compression (SET STORAGE external) but I'd bet on that being a net loss
unless the data is only poorly compressible.

If the table is not updated very often, it's possible that doing a
CLUSTER every so often would help. I'm not 100% sure but I think that
would result in the toast table being rewritten in the same order as the
newly-built main table, which ought to cut down on the cost of fetching.

Also, what database encoding are you using? I note from the CVS logs
that some post-8.2 work was done to make LIKE faster in multibyte
encodings. (Though if you were doing the LIKE directly in bytea, that
wouldn't matter ... what was the reason for the encode() call again?)

regards, tom lane