Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 6C4B3B5FE57 for ; Mon, 6 Jun 2011 17:35:13 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 23417-04 for ; Mon, 6 Jun 2011 20:35:06 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-iw0-f174.google.com (mail-iw0-f174.google.com [209.85.214.174]) by mail.postgresql.org (Postfix) with ESMTP id D8CB6B5F917 for ; Mon, 6 Jun 2011 17:35:04 -0300 (ADT) Received: by iwn34 with SMTP id 34so3427017iwn.19 for ; Mon, 06 Jun 2011 13:35:04 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:date:message-id:subject:from:to :content-type; bh=G4RYgE6+oLV3gqON3qrS4kEujKg2/8rjaqyaJv7HCMo=; b=hyBn9GX6/LK/wixAUaqK+u7myuaDafXW01U2ZkfN35fdn/vn8KdC89qNxFEJbXJpr1 KYTjb3b4LplexCkEmainh6dCMcVP73MEISNqRf8KnLXTlN9cc0CzTAQMYjaz3+UI2/VK ja6lcuGFr60oIdOOaptDx4LIa7jAlBuTtruDA= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:date:message-id:subject:from:to:content-type; b=jQ8m0vMYWF17b0Tcoh3zAfuNsMHD/4iPxpab0l+1W17z5wOyUkbyQFBEMOn3lePqMA TEbVHoRnrBo4YYH6OFQX+ws0FXAf0gAu8XUlLMaO8D45F4lH9Y2vJvjcyEAV8bNYQQsx 49iA8KB+u9R1iJmnT44GHZBzxCxpt8CHAyVBw= MIME-Version: 1.0 Received: by 10.231.187.232 with SMTP id cx40mr8970130ibb.73.1307392504402; Mon, 06 Jun 2011 13:35:04 -0700 (PDT) Received: by 10.231.199.74 with HTTP; Mon, 6 Jun 2011 13:35:04 -0700 (PDT) Date: Mon, 6 Jun 2011 15:35:04 -0500 Message-ID: Subject: poor performance when recreating constraints on large tables From: Mike Broers To: pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary=00504502bad2ce932304a51109af X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.887 tagged_above=-10 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001, RFC_ABUSE_POST=0.001, T_TO_NO_BRKTS_FREEMAIL=0.01 X-Spam-Level: X-Archive-Number: 201106/34 X-Sequence-Number: 43967 --00504502bad2ce932304a51109af Content-Type: text/plain; charset=ISO-8859-1 I originally posted this on admin, but it was suggested to post it to performance so here goes - I am in the process of implementing cascade on delete constraints retroactively on rather large tables so I can cleanly remove deprecated data. The problem is recreating some foreign key constraints on tables of 55 million rows+ was taking much longer than the maintenance window I had, and now I am looking for tricks to speed up the process, hopefully there is something obvious i am overlooking. here is the sql I am running, sorry im trying to obfuscate object names a little - BEGIN; ALTER TABLE ONLY t1 DROP CONSTRAINT fk_t1_t2_id; ALTER TABLE ONLY t1 ADD CONSTRAINT fk_t1_t2_id FOREIGN KEY(id) REFERENCES t2(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; COMMIT; t1 has 55 million rows t2 has 72 million rows the id columns are integer types postgres version 8.3.8 there are nightly vacuum/analyze commands, and auto vacuum is enabled. I have tried set constraints deferred, immediate, the id column on table 2 is indexed, its the primary key. Nothing really seems to impact the time it takes to recreate the constraint. There may be memory settings to tweak, I was able to get it to run on a faster test server with local storage in about 10 minutes, but it was running for over an hour in our production environment.. We took down the application and I verified it wasnt waiting for an exclusive lock on the table or anything, it was running the alter table command for that duration. Let me know if there is anything else I can supply that will help the review, thanks! One additional question - is there any way to check how long postgres is estimating an operation will take to complete while it is running? Thanks again, Mike --00504502bad2ce932304a51109af Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable
<= span class=3D"Apple-style-span" style=3D"border-collapse: collapse; font-fa= mily: arial, sans-serif; font-size: 13px; ">I originally posted this on adm= in, but it was suggested to post it to performance so here goes -=A0=

I am in = the process of implementing cascade on delete constraints retroactively on = rather large tables so I can cleanly remove deprecated data. =A0The problem= is recreating some foreign key constraints on tables of 55 million rows+ w= as taking much longer than the maintenance window I had, and now I am looki= ng for tricks to speed up the process, hopefully there is something obvious= i am overlooking.

here is the sql I am running, sorry im trying to obfusc= ate object names a little -=A0
BEGIN;
ALTER TABLE ONLY t1 DROP CONSTRAINT fk_t1_t2_id;
ALTER TABLE ONLY t1 ADD= CONSTRAINT fk_t1_t2_id FOREIGN KEY(id) REFERENCES t2(id)=A0
ON DELE= TE CASCADE=A0
COMMIT;

t1 has 55 million rows
t2 has 72 million rows
the id columns are integer types
postgres version 8.3.8
there are nightly vacuum/analyze commands, and auto vacuum is enable= d.

I have tried set constraints deferred, immediate, the i= d column on table 2 is indexed, its the primary key. =A0Nothing really seem= s to impact the time it takes to recreate the constraint. =A0There may be m= emory settings to tweak, I was able to get it to run on a faster test serve= r with local storage in about 10 minutes, but it was running for over an ho= ur in our production environment.. We took down the application and I verif= ied it wasnt waiting for an exclusive lock on the table or anything, it was= running the alter table command for that duration. =A0

Let me know if there is anything else I can supply that= will help the review, thanks!

One additional ques= tion - is there any way to check how long postgres is estimating an operati= on will take to complete while it is running?

Thanks again,
Mike
--00504502bad2ce932304a51109af--