Sim Zacks <sim@xxxxxxxxxxxxxx> 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
