Re: Make deparsing of column defaults faster

On 29.06.18 05:15, Jeff Janes wrote:
> Since pg_dump calls pg_get_expr once over and over again on the same
> table consecutively, perhaps we could cache the column alias assignments
> in a single-entry cache, so if it is called on the same table as last
> time it just re-uses the aliases from last time.  I am not planning on
> working on that, I don't know where such a cache could be stored such
> that is freed and invalidated at the appropriate times.

I looked into that.  deparse_context_for() is actually not that
expensive on its own, well below one second, but it gets somewhat
expensive when you call it 1600 times for one table.  So to address that
case, we can cache the deparse context between calls in the fn_extra
field of pg_get_expr.  The attached patch does that.  This makes the
pg_dump -s times pretty much constant even with 1600 columns with
defaults.  psql \d should benefit similarly.  I haven't seen any other
cases where we'd expect hundreds of related objects to deparse.  (Do
people have hundreds of policies per table?)

(I suppose you could create scenarios with very many such tables to make
the overhead visible again.)

How realistic is this use case?  Is it worth it?

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services