I looked into the out-of-memory problem reported by Jakub Ouhrabka here:
http://archives.postgresql.org/pgsql-general/2010-11/msg00353.php
It's pretty simple to reproduce, even in HEAD; what you need is an index
expression that computes a bulky intermediate result. His example is
md5(array_to_string(f1, ''::text))
where f1 is a bytea array occupying typically 15kB per row. Even
though the final result of md5() is only 32 bytes, evaluation of this
expression will eat about 15kB for the detoasted value of f1, roughly
double that for the results of the per-element output function calls
done inside array_to_string, and another 30k for the final result string
of array_to_string. And *none of that gets freed* until
compute_index_stats() is all done. In my testing, with the default
stats target of 100, this gets repeated for 30k sample rows, requiring
something in excess of 2GB in transient space. Jakub was using stats
target 500 so it'd be closer to 10GB for him.
AFAICS the only practical fix for this is to have the inner loop of
compute_index_stats() copy each index expression value out of the
per-tuple memory context and into the per-index "Analyze Index" context.
That would allow it to reset the per-tuple memory context after each
FormIndexDatum call and thus clean up whatever intermediate result trash
the evaluation left behind. The extra copying is a bit annoying, since
it would add cycles while accomplishing nothing useful for index
expressions with no intermediate results, but I'm thinking this is a
must-fix.
Comments?
regards, tom lane