The cases I've seen in production typically involve "outgrowing" optimizer parameter settings: (e.g work_mem, effective_cache_size) as the application dataset gets bigger over time.
An argument in favour of the DBMS maintaining a running estimate of such things.
That is an interesting idea - I'm not quite sure how it could apply to server config settings (e.g work_mem) for which it would be dangerous to allow the server to increase on the fly, but it sure would be handy to have some sort of query execution "memory" so that alerts like:
"STATEMENT: SELECT blah : PARAMETERS blah: using temp file(s), last execution used memory"
could be generated (this could be quite complex I guess, requiring some sort of long lived statement plan cache).
