Re: Further pg_upgrade analysis for many tables

On 2012-11-08 12:30:11 -0500, Peter Eisentraut wrote:
> On 11/7/12 9:17 PM, Bruce Momjian wrote:
> > As a followup to Magnus's report that pg_upgrade was slow for many
> > tables, I did some more testing with many tables, e.g.:
> >
> > CREATE TABLE test991 (x SERIAL);
> >
> > I ran it for 0, 1k, 2k, ... 16k tables, and got these results:
> >
> > tables pg_dump restore pg_upgrade(increase)
> > 0 0.30 0.24 11.73(-)
> > 1000 6.46 6.55 28.79(2.45x)
> > 2000 29.82 20.96 69.75(2.42x)
> > 4000 95.70 115.88 289.82(4.16x)
> > 8000 405.38 505.93 1168.60(4.03x)
> > 16000 1702.23 2197.56 5022.82(4.30x)
>
> I can reproduce these numbers, more or less. (Additionally, it ran out
> of shared memory with the default setting when dumping the 8000 tables.)
>
> But this issue seems to be entirely the fault of sequences being
> present. When I replace the serial column with an int, everything
> finishes within seconds and scales seemingly linearly.

I don't know the pg_dump code at all but I would guess that without the
serial there are no dependencies, so the whole dependency sorting
business doesn't need to do very much...

Greetings,

Andres Freund