Grzegorz Blinowski <g(dot)blinowski(at)gmail(dot)com> wrote:
> 2) changing long attribute storage to EXTERNAL gave 30% better
> search time (but only on the first search - i.e. before data is
> cached)
That suggests that all of the following are true:
(1) The long value was previously being compressed and stored
in-line.
(2) It's now being stored uncompressed, out-of-line in the TOAST
table.
(3) Following the TOAST pointers on cached tuples isn't
significantly more or less expensive than decompressing the data.
(4) The smaller base tuple caused fewer page reads from disk, even
with the out-of-line storage for the large value.
The first three aren't surprising; that last one is. Unless there
is significant bloat of the table, I'm having trouble seeing why
that first run is cheaper this way. Make sure your vacuum policy is
aggressive enough; otherwise you will probably see a slow but steady
deterioration in performance..
-Kevin