Re: [GENERAL] COPY speedup
On Dec 13, 2007 4:31 PM, Reg Me Please <[EMAIL PROTECTED]> wrote: > Il Thursday 13 December 2007 19:56:02 Tom Lane ha scritto: > > Reg Me Please <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] COPY speedup
Il Thursday 13 December 2007 19:56:02 Tom Lane ha scritto: > Reg Me Please <[EMAIL PROTECTED]> 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! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] COPY speedup
Reg Me Please <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] 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! ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
