Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 323EA9FB2E0 for ; Fri, 12 Jan 2007 06:56:05 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024) with ESMTP id 00407-01 for ; Fri, 12 Jan 2007 06:55:55 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.172]) by postgresql.org (Postfix) with ESMTP id 8AD869FB2A3 for ; Fri, 12 Jan 2007 06:55:55 -0400 (AST) Received: by ug-out-1314.google.com with SMTP id k3so688453ugf for ; Fri, 12 Jan 2007 02:55:53 -0800 (PST) DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding:sender; b=QhSozrirqocnaOeMYfwM/K1QMOy6ktESXXmMFdlbrfsjveCElafYYQKwHvNsBdQaV0j2GPkoZPEA3R1j9CvZLKLLlsHYoLuNj78ohOmAlDTPjA3z5ZJGg6s2Ze4s7y8Nd4gsEdCHA1uzfKY4sJ3z+8CQzGMUO1xmfeqc7BclPTk= Received: by 10.78.183.15 with SMTP id g15mr146103huf.1168599351960; Fri, 12 Jan 2007 02:55:51 -0800 (PST) Received: from ?192.168.0.39? ( [62.232.55.118]) by mx.google.com with ESMTP id 30sm1101854hue.2007.01.12.02.55.48; Fri, 12 Jan 2007 02:55:51 -0800 (PST) Message-ID: <45A7692C.2080106@enterprisedb.com> Date: Fri, 12 Jan 2007 10:55:40 +0000 From: Heikki Linnakangas User-Agent: Icedove 1.5.0.8 (X11/20061116) MIME-Version: 1.0 To: Tobias Brox CC: Peter Childs , pgsql-performance@postgresql.org Subject: Re: Planner statistics, correlations References: <20070112081631.GA10636@oppetid.no> <20070112090500.GB16243@oppetid.no> <45A765DE.9020003@enterprisedb.com> <20070112104422.GD9703@oppetid.no> In-Reply-To: <20070112104422.GD9703@oppetid.no> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200701/205 X-Sequence-Number: 22806 Tobias Brox wrote: > [Heikki Linnakangas - Fri at 10:41:34AM +0000] >> I thought about partitioning the table by state, putting rows with >> state=4 into one partition, and all others to another partition. > > That sounds like a good idea - but wouldn't that be costly when changing state? In PostgreSQL, UPDATE internally inserts a new row and marks the old one as deleted, so there shouldn't be much of a performance difference. I'm not very familiar with our partitioning support, so I'm not sure if there's any problems with an update moving a row from one partition to another. I think you'll have to create an INSTEAD OF UPDATE rule to do a DELETE on one partition and an INSERT on the other partition. Depending on your application, that might be a problem; UPDATE is different from DELETE+INSERT from transaction isolation point of view. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com