Re: Make deparsing of column defaults faster

From: Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com>
To: Jeff Janes <jeff(dot)janes(at)gmail(dot)com>
Cc: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>, Justin Pryzby <pryzby(at)telsasoft(dot)com>
Subject: Re: Make deparsing of column defaults faster
Date: 2018-07-05 14:45:07
Views: Raw Message | Whole Thread | Download mbox
Lists: pgsql-hackers

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
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Attachment Content-Type Size
0001-Cache-pg_get_expr-deparse-context-between-calls.patch text/plain 2.9 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Fujii Masao 2018-07-05 14:45:45 Re: Speed up the removal of WAL files
Previous Message Andrew Gierth 2018-07-05 14:25:24 Re: Should contrib modules install .h files?