Re: Dump/Reload pg_statistic to cut time from pg_upgrade?

On Sun, Sep 1, 2013 at 10:35:57AM -0700, Jeff Davis wrote:
> [ late response, but might still be useful to someone ]
>
> You can work around the problem with a little effort if you call
> array_in directly. It takes the type output (cstring), element type
> (oid), and element typmod (integer).
>
> To dump the pg_statistics table, you have to output all of the columns
> plus the type ID and the typmod, and then load it back in by doing
> something like:
>
> insert into pg_statistic
> select starelid, ...,
> array_in(stavalues1, the_element_type, -1), ...
> from my_statistics_dump;
>
> The element typmod is always -1 for pg_statistic. To get the element
> type, you can join against pg_attribute. The only problem is, you don't
> actually want the attribute type, you want the type used for the
> statistics, which is normally the same but could be different. I don't
> think the statypid is stored in the catalog, so you'd have to inventory
> the types that you use and figure out a mapping of the type to it's
> statistics type looking at the typanalyze routines.
>
> So, it's possible to do, but not worth the effort unless you are quite
> concerned about the analyze time post-upgrade.
>
> It would be nice if we had a better way to backup, transfer, and upgrade
> statistics. However, allowing statistics to be upgraded could be a
> challenge if the statistics format changes between releases.

FYI, the data stored in those statistics tables frequently change in
major releases, so a simple dump/reload of those statistics might give
you the _wrong_ statistics. This is the big reason I have not done more
work in migrating those. The incremental statistics build seems to have
worked for most people, i.e. statistics target = 1, 10, default.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +