Re: bytea encode performance issues

Поиск
Список
Период
Сортировка
От Tom Lane
Тема Re: bytea encode performance issues
Дата
Msg-id 20251.1217977030@sss.pgh.pa.us
обсуждение исходный текст
Ответ на Re: bytea encode performance issues  (Sim Zacks <sim@compulab.co.il>)
Ответы Re: bytea encode performance issues  (Sim Zacks <sim@compulab.co.il>)
Список pgsql-general
Sim Zacks <sim@compulab.co.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

В списке pgsql-general по дате отправления:

Предыдущее
От: "x asasaxax"
Дата:
Сообщение: Update tsvector trigger
Следующее
От: Tom Lane
Дата:
Сообщение: Re: What happen to the VARATT_SIZEP macro in version 8.3?