Re: COPY speedup

On Dec 13, 2007 4:31 PM, Reg Me Please <regmeplease(at)gmail(dot)com> wrote:
> Il Thursday 13 December 2007 19:56:02 Tom Lane ha scritto:
> > Reg Me Please <regmeplease(at)gmail(dot)com> writes:
> > > In order to speed up the COPY ... FROM ... command, I've
> > > disabled everything (primary key, not null, references, default and
> > > indexes) in the table definition before doing the actual COPY.
> > > Later I can restore them with ALTER TABLE ... and CREATE INDEX ...
> > >
> > > My question is: is all this necessary, or could I save some of them
> > > (maybe just the DEFAULT) with no speed cost?
> >
> > Indexes and foreign key references are the only things that benefit
> > from this treatment. DEFAULTs are irrelevant to a COPY, and simple
> > constraints (NOT NULL and CHECK) are not any faster to verify later
> > --- which makes dropping them slower, since you'll need an additional
> > table scan to verify them when they're re-added.
> >
> > regards, tom lane
>
> I'd suppose that foreign keys are to be "disabled" in order to speed things
> up. Right?

pg_restore has a --disable-triggers option which you can use to do
this in some cases. otherwise you can make a simple function wrapper
to do this with some dynamic sql which disables the triggers for
you...

merlin