Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 91939B5F77E for ; Mon, 30 May 2011 21:08:40 -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 33906-03 for ; Tue, 31 May 2011 00:08:33 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-pz0-f46.google.com (mail-pz0-f46.google.com [209.85.210.46]) by mail.postgresql.org (Postfix) with ESMTP id 9A131B5F42E for ; Mon, 30 May 2011 21:08:33 -0300 (ADT) Received: by pzk9 with SMTP id 9so1774765pzk.19 for ; Mon, 30 May 2011 17:08:33 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:from:content-type:content-transfer-encoding :subject:date:message-id:to:mime-version:x-mailer; bh=zDPM9A/cMSbKBM2UIuj1SF4KFLJ8ACE/WtK69SRbdMo=; b=PgMkdfXeqbVzoC5uhWh33O7yadnQedLWroEn8JLPvPxe/hzIUXH8a/syoqPA2MM0eh CW9PekOqASySfs+qjArCznbVDgtKZ3uLJ2RusVh1W9EFBeNrocwzZ1yOxfKfT2EQueEm EigNsOIxzKp0GnWIOMS9vZCgnxM4lazL13s18= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=from:content-type:content-transfer-encoding:subject:date:message-id :to:mime-version:x-mailer; b=PyT/Yx+eNGvje81bfMTWuEG94Pqc4VOt9s+oZ0mr29OZhl/vjw5GkIdoGCRHAEb57Q sLSNjjw1JbM4S3PiBtRkdWQ0Fa8nFKpghQgKveXVpWBOxpOB1w9AuIytQud2mn/k8Rx7 Q6Z3Hr4ItE5kGO8ycvRUk5teBA0fP1Dil8bJk= Received: by 10.68.54.199 with SMTP id l7mr2283185pbp.1.1306800513422; Mon, 30 May 2011 17:08:33 -0700 (PDT) Received: from [10.2.56.110] (203-206-187-220.perm.iinet.net.au [203.206.187.220]) by mx.google.com with ESMTPS id p5sm3227131pbd.44.2011.05.30.17.08.31 (version=TLSv1/SSLv3 cipher=OTHER); Mon, 30 May 2011 17:08:32 -0700 (PDT) From: Jarrod Chesney Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: quoted-printable Subject: Delete performance Date: Tue, 31 May 2011 10:08:28 +1000 Message-Id: <6940CBA1-BB1C-4030-B425-2DE9135CEAE3@gmail.com> To: pgsql-performance@postgresql.org Mime-Version: 1.0 (Apple Message framework v1084) X-Mailer: Apple Mail (2.1084) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.611 tagged_above=-10 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, RCVD_IN_NJABL_RELAY=2.499, RFC_ABUSE_POST=0.001, T_TO_NO_BRKTS_FREEMAIL=0.01 X-Spam-Level: X-Archive-Number: 201105/413 X-Sequence-Number: 43922 Hi All My database uses joined table inheritance and my server version = is 9.0 Version string PostgreSQL 9.0rc1 on x86_64-pc-linux-gnu, compiled by = GCC x86_64-pc-linux-gnu-gcc (Gentoo 4.4.4-r1 p1.1, pie-0.4.5) 4.4.4, = 64-bit=09 I have about 120,000 records in the table that everything else inherits = from, if i truncate-cascaded this table it happens almost instantly. If = i run 30,000 prepared "DELETE FROM xxx WHERE "ID" =3D ?" commands it = takes close to 10 minutes. My foreign keys to the base table are all set with "ON DELETE CASCADE". = I've looked though all the feilds that relate to the "ID" in the base = table and created btree indexes for them. Can anyone outline what I need to verify/do to ensure i'm getting the = best performance for my deletes? Regards, Jarrod Chesney=20=