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