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
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
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
