Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 86FF21337B8C for ; Sat, 4 Dec 2010 20:05:13 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 58978-01 for ; Sun, 5 Dec 2010 00:05:07 +0000 (UTC) X-Greylist: delayed 00:25:02.916598 by SQLgrey-1.7.6 Received: from mail.erwied.eu (mail.erwied.eu [85.214.80.157]) by mail.postgresql.org (Postfix) with ESMTP id DD42C1337B77 for ; Sat, 4 Dec 2010 20:05:05 -0400 (AST) Received: from dslb-088-078-103-143.pools.arcor-ip.net ([88.78.103.143] helo=erwied.dns2go.com ident=mail) by mail.erwied.eu with esmtpsa (Exim 4.72#1) id 1PP1ht-0007kA-EY ; Sun, 05 Dec 2010 00:39:57 +0100 Received: from joe-lifebook.dhcp.erwied.de ([172.17.10.70] helo=localhost) by erwied.dns2go.com with esmtpsa (Exim 4.72#1) id 1PP1hH-0006aA-Ro ; Sun, 05 Dec 2010 00:39:22 +0100 Date: Sun, 5 Dec 2010 00:38:39 +0100 From: Jochen Erwied Organization: Creative Chaos Computing X-Priority: 3 (Normal) Message-ID: <1628298022.20101205003839@erwied.eu> To: Mladen Gogala CC: Tom Lane , John Papandriopoulos , "pgsql-performance@postgresql.org" , Subject: Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT In-Reply-To: <4CFACC81.3050901@vmsinfo.com> References: <14012.1291443654@sss.pgh.pa.us> <23792.1291480923@sss.pgh.pa.us> <13159.1291502400@sss.pgh.pa.us> <4CFACC81.3050901@vmsinfo.com> MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable X-Antivirus: avast! (VPS 101204-1, 04.12.2010), Outbound message X-Antivirus-Status: Clean X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201012/47 X-Sequence-Number: 41499 Sunday, December 5, 2010, 12:19:29 AM you wrote: > Hmmm, what happens if I need 10 years of data, in monthly partitions? It= =20 > would be 120 partitions. Can you please elaborate on that limitation?=20 > Any plans on lifting that restriction? I'm running a partitioning scheme using 256 tables with a maximum of 16 million rows (namely IPv4-addresses) and a current total of about 2.5 billion rows, there are no deletes though, but lots of updates. Using triggers or rules on the main table in my case showed to be not very effective, so I reverted to updating the inherited tables directly. This way you still can use a SELECT on the main table letting the optimizer do it's work, but do not run into the problem of oversized shared memory usage when doing DELETEs or UPDATEs IMHO if you are using large partitioning schemes, handle the logic of which table to update or delete in your application. In most cases extending the underlying application will be much less work and more flexible than trying to write a dynamic rule/trigger to do the same job. --=20 Jochen Erwied | home: jochen@erwied.eu +49-208-38800-18, FAX: -19 Sauerbruchstr. 17 | work: joe@mbs-software.de +49-2151-7294-24, FAX: -50 D-45470 Muelheim | mobile: jochen.erwied@vodafone.de +49-173-5404164