Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id CB38A1337B47 for ; Sat, 4 Dec 2010 02:21:03 -0400 (AST) 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 54986-03 for ; Sat, 4 Dec 2010 06:20:56 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id 7D5D21337990 for ; Sat, 4 Dec 2010 02:20:56 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id oB46Ktar014013; Sat, 4 Dec 2010 01:20:55 -0500 (EST) To: John Papandriopoulos cc: pgsql-performance@postgresql.org Subject: Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT In-reply-to: References: Comments: In-reply-to John Papandriopoulos message dated "Fri, 03 Dec 2010 13:41:36 -0800" Date: Sat, 04 Dec 2010 01:20:54 -0500 Message-ID: <14012.1291443654@sss.pgh.pa.us> From: Tom Lane 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/30 X-Sequence-Number: 41482 John Papandriopoulos writes: > I've found that a k-ary table inheritance tree works quite well to > reduce the O(n) CHECK constraint overhead [1] in the query planner > when enabling partition constraint exclusion. Um ... you mean you're creating intermediate child tables for no reason except to reduce the number of direct descendants of any one table? That's an utter waste of time, because the first thing the planner will do with an inheritance tree is flatten it. Just create *one* parent table and make all the leaf tables direct children of it. > My machine swaps wildly when PostgreSQL plans an UPDATE or DELETE. This is a strong hint that you've got way too many child tables. regards, tom lane