Обсуждение: COPY speedup

Hi all.
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?

Is there a way to "automate" this by using the information_schema?

Many thanks in advance.

--
Reg me, please!

Reg Me Please <regmeplease@gmail.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

Il Thursday 13 December 2007 19:56:02 Tom Lane ha scritto:
> Reg Me Please <regmeplease@gmail.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?

--
Reg me, please!

On Dec 13, 2007 4:31 PM, Reg Me Please <regmeplease@gmail.com> wrote:
> Il Thursday 13 December 2007 19:56:02 Tom Lane ha scritto:
> > Reg Me Please <regmeplease@gmail.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